SECURE Remote Database Administration

Michael J. Hillanbrand II and Edward Haskins, Dulcian, Inc.

Overview

There are many reasons for organizations to be interested in Remote Administration of their Oracle Databases. The ability to respond to a nascent crisis in the middle of the night would probably rank number one. A close second is enabling telecommuting, and third, we would add outsourcing the DBA function entirely.

From our perspective, we can provide expanded service to more clients at a cheaper price than is possible to service on-site, providing they agree to Remote Administration. The benefit for us is less travel and an ability to work in a "totally customized", and hence more productive, environment.

Whatever the reasons for considering Remote Database Administration, until recently the only relatively secure option was a direct dial connection. This option utilizes a range of products from PC Anywhere and Carbon Copy on the low end, allowing only one simultaneous connection and requiring a dedicated modem, to a bank of modems and the use of smart card encryption technology as a high-end solution.

With the renewed interest in using the Internet to get "real" work done, and concurrent interest in encryption mechanisms, there are tools now available which provide a "secure" remote connection to the corporate Intranet, via the connection to the Internet. We do however stipulate that no solution will ever be perfectly secure. If a person can devise a lock, another person can unlock it, or circumvent the locking mechanism entirely.

This paper provides details about VPN (Virtual Private Networking), and Remote Data Base Administration, taking full advantage of VPN as a part of the infrastructure.

Introduction to VPN

Virtual Private Networking technology is designed to address issues surrounding the current business trends toward increased telecommuting. These trends include:

• Widely distributed global operations
• Highly interdependent partner operations where workers must be able to connect to central resources and communicate with each other
• System administrators who can monitor their networks at any time of day or night from anywhere in the World that an Internet connection can be established.

A Virtual Private Network can be described as the ability to "tunnel" through the Internet in a manner that provides the same security and other features formerly only available on private networks. It allows a user working at home or on the road to connect to a remote corporate server using the bandwidth provided by the public network. VPN also allows a corporation to connect with branch offices, or with other companies, while maintaining a secure connection.

From the user’s perspective, the nature of the physical network being tunneled through is irrelevant because it appears as if the information is being sent over a dedicated private network. From a more technical perspective, a VPN tunnel encapsulates data within IP packets to transport information that does not otherwise conform to Internet addressing standards. The result is that remote users become virtual nodes on the network into which they have tunneled.

Basic VPN Requirements

Typically, when deploying a remote networking solution, an enterprise has the goal of facilitating controlled access to corporate resources and information. The solution must allow freedom for authorized remote clients to easily connect to corporate LAN resources and for remote offices to connect to each other to share resources and information (LAN-to-LAN connections). Finally, the solution must ensure the privacy and integrity of data as it traverses the public Internet. The same concerns apply in the case of sensitive data traversing a corporate internetwork. Therefore, at a minimum, a VPN solution should provide all of the following:

• User Authentication. The solution must verify a user’s identity and restrict VPN access to authorized users. In addition, the solution must provide audit and accounting records to show who accessed what information and when.
• Address Management. The solution must assign a client’s address on the private network, and must ensure that private addresses are kept private.
• Data Encryption. Data carried on the public network must be rendered unreadable to unauthorized clients on the network.
• Key Management. The solution must generate and refresh encryption keys for the client and server.
• Multiprotocol Support. The solution must be able to handle common protocols used in the public network. These include Internet Protocol (IP), Internet Packet Exchange (IPX), and so on.

An Internet VPN solution based on the Point-to-Point Tunneling Protocol (PPTP) or Layer 2 Tunneling Protocol (L2TP) meets all of these basic requirements and takes advantage of the broad availability of the worldwide Internet. Other solutions, including the new IP Security Protocol (IPSec), meet only some of these requirements, but remain useful for specific situations.

Tunneling Basics

Tunneling is a method of using an internetwork infrastructure to transfer data from one network over another network. The data to be transferred can be the packets of another protocol. Instead of sending a packet as it is produced by the originating node, the tunneling protocol encapsulates the packet in an additional header. The additional header provides routing information so that the encapsulated packets can traverse the intermediate internetwork. The encapsulated packets are then routed between tunnel endpoints over the internetwork. The logical path through which the encapsulated packets travel through the internetwork is called a tunnel. Once the encapsulated packets reach their destination on the internetwork, the packets are un-encapsulated and forwarded to their final destination. The three predominant tunneling protocols in use today are:

• Point-to-Point Tunneling Protocol (PPTP). PPTP allows IP, IPX, or NetBEUI traffic to be encrypted and encapsulated in an IP header to be sent across a corporate IP internetwork or a public IP internetwork such as the Internet.
• Layer 2 Tunneling Protocol (L2TP). L2TP allows, IP, IPX, or NetBEUI traffic to be encrypted and sent over any medium that supports point-to-point datagram delivery, such as IP, X.25, Frame Relay, or asynchronous transfer mode (ATM).
• IP Security (IPSec) Tunnel Mode. IPSec Tunnel Mode allows IP payloads to be encrypted and encapsulated in an IP header to be sent across a corporate IP internetwork or a public IP internetwork such as the Internet.

How Does PPTP Work?

Imagine PPTP as a Dial-Up Networking (DUN) connection inside a DUN connection, or a pipe within a pipe. Your first connection, or pipe, is your Point-to-Point (PPP) connection to your ISP; your second connection is your PPTP connection, which tunnels through your first connection. Because the PPTP connection is a tunnel, you can route whatever types you want, including IPX and NetBEUI, through the tunnel over the Internet. Your ISP sees the traffic as IP packets; but when the packets reach your PPTP-configured RAS server, they leave the tunnel and enter your corporate network.

On the Server Side

To get your RAS server ready to accept incoming PPTP traffic, install PPTP. You can do this through the Network applet in the Control Panel using the following steps:

1. In the PPTP Configuration dialog box, define the number of VPN devices for inbound PPTP connections you want to support. RAS can support up to 256 simultaneous connections.
2. Configure encryption for your PPTP connection. Keep in mind that your sensitive corporate data will travel through the Internet, which is a public network. You will want to select Require Microsoft encrypted authentication and Require data encryption. Additional security features will be discussed later. For the most part, your RAS server is now ready to receive PPTP connections from remote clients via the Internet.

On the Client Side

Any Windows 95, 98 or NT client can be used to connect to the RAS/PPTP server. We will use the setup of a Windows NT Workstation for our discussion. Install PPTP in the same way that you did for the server and follow these additional steps:

1. Create a new entry in the DUN phone book to define the PPTP connection. Create the entry using your VPN port rather than a modem, and enter the RAS/PPTP server’s IP address or fully qualified domain name if it is registered in DNS.
2. On the server tab, select all of the protocols that you will need access to on the private network.
3. On the security tab, select the Allow only Microsoft encrypted authentication and Require data encryption options. Another option is Use current username and password. You can use this option if you expect your username and password on this workstation to be the same as they are in the domain you are dialing into.

If you have configured everything correctly, you can now dial up your PPTP connection and connect to your private network.

What about UNIX?

Up to this point, there has been only mention of implementing a VPN for a Windows NT based internetwork. There are plenty of enterprises running UNIX only and mixed UNIX / Windows environments. We have found one such company that provides a UNIX based (as well as a Windows) solution. Data Fellows (www.datafellows.com) has created F-Secure VPN+, an enterprise class, secure, remote access solution.

F-Secure VPN+ is based on the Internet standard IPSec and Internet Key Exchange (IKE). IPSec provides IP-level authentication. F-Secure supports the following encryption algorithms:

• 3DES (168 bit)
• DES (56 bit)
• Blowfish (128 bit)
• CAST128 (128 bit)

F-Secure VPN+ products can route, filter, encrypt and authenticate IPSec and plain-text communication. The F-Secure VPN+ family consists of the following products:

• F-Secure VPN+ Client Supports both Windows and Solaris workstations that send encrypted data to an F-Secure VPN+ Enterprise Gateway.
• F-Secure VPN+ Server Also supports servers running either Windows or Solaris and encrypts all data from server to workstations.
• F-Secure VPN+ Gateway Encrypts all data traffic from LAN through WAN using IPSec to other LAN’s.
• F-Secure VPN+ Enterprise Gateway The Enterprise version allows simultaneous connections from both VPN+ Clients and VPN+ Gateway devices.

Data Fellows also supplies a product called F-Secure SSH Server. SSH Server is a UNIX tool that allows for secure login connections, file transfer and TCP/IP connections over the Internet. System administrators can use tools provided in the server package to replace existing rsh, rlogin, rcp, rdist and telnet protocols. This will allow the administrator to perform all remote tasks securely over the Internet. It is strongly recommended by Data Fellows that all the standard remote tools such as rsh, rlogin, etc. be disabled to ensure the server is fully secured.

Notes About Security and Performance

Now, before you tell your CIO that you plan to route your company’s sensitive remote-access data over the Internet, make sure that you can answer some obvious questions. Here are some of the basics concerning security and performance:

• Microsoft RAS server supports both 40-bit and 128-bit RSA RC4 data encryption, derived from what is called a "shared secret," namely, your password. The client encrypts its data using your password, and the server does the same with its copy of your password from the security database. Because both systems (client and server) know what your password is, it never has to travel across the Internet unencrypted. This function solves the major security problem of key distribution.
• Obviously, performance over the public Internet won’t be as fast as your dedicated dial-up circuits. First, you share a finite amount of bandwidth with several million other users. Keep in mind that the Internet is getting slower as time goes on. Second, running a tunneling protocol has unavoidable overhead, although Microsoft has designed PPTP to minimize overhead.

Remote Database Administration

Once your VPN or other connective solution is in place, you could simply work the same way some of us always have, namely from crisis to crisis. A better alternative is to invest some time, talent and energy into your infrastructure and attain "Remote DBA Nirvana."

If your network administrator has not provided for it, a real necessity for remote database administration is an event-driven method to "page" and or E-mail the remote DBA. This functionality is a part of what is provided for the Windows user equipped with Oracle’s Enterprise Manager (OEM) product. A Windows node (95/98 or NT) would need to be continuously running OEMa to take advantage of this and the other event driven features mentioned below. Another product,, I/Watch, marketed by Quest Software provides much of the capability that the Remote DBA needs to implement to keep tabs on remote databases.

In "UNIXland" this capability has been exploited with homegrown scripts for years. There are several very good monitoring packages available as well. Platinum and BMC seem to lead the market here. These products would be considered "high-end" from a pricing perspective. Now with UNIX-like tools available on NT, the same scripts can run virtually unchanged throughout the enterprise. We like the MKS toolkit from Mortice Kern Systems,

Now is the time to go a bit farther than just reporting errors from batch jobs or the backup routine. Engage the assistance of your UNIX or NT administrator and any other scripting guru you can drag into the project. As much as possible, the goal is to automate those tasks that an individual might perform that are not already automated. Whether the scripts are produced for UNIX, or NT, or even another scripting language like TCL (OEM) is irrelevant.

Each new event or job scripted saves the DBA time and the company/client money, if not directly in clocked hours, then in the time saved when errors are caught early and corrected.

Automating the jobs normally performed manually

There are many jobs that needed to be performed manually in the past that are now possible to automate. We will mention some of the most important ones here.

• One of the most useful items to automate is a parsing of the Alert Log to report any new errors. This routine should run about every five minutes. We ran into this about two years ago in a UNIX shop where the routine first checks to see if the Alert log had grown. If it had, it did a grep of the Alert log outputting lines containing specific error strings like "^ORA" and "Errors in file" as well as others. The resulting file is then e-mailed to the DBA, or the DBA could also be paged.
• Any DBA who does not include an automated notification for potential growth problems (next extent will blow table, tablespace, index etc.) is either too busy, too new, or…pick your own excuse.
• Another useful task to automate is the set of tuning statistics you use. The result of this should be stored in a database to enable later analysis and evaluation. Taking this task a step further, it is even possible to produce a new recommended initSID.ora file. Imagine - A self-tuning database engine! It is still advisable to keep an eye on it. Do not allow any new values to be added until the next day. The job sends me E-mail, telling me what it changed and schedules its own reboot, at which time it will swap out the old initSID.ora file (renaming it to initSID.datestamp) and move in the new file.
• It should be obvious to everyone that the most important item to automate is the backup of the database. Whatever backup solution is selected, remember that it is just as vital to provide any success and failure information as it is to perform the backup.
• If you perform a task on a regular basis, automate the task and have the result reported to you. Even renaming the alert log on a regular basis is a worthwhile task to automate. (mv alertSID.log alertSID.timestamp)

Conclusions

With an infrastructure investment like the one described in this paper, your DBA should have the capability and capacity to handle up to twenty-five medium-sized databases both remotely and, just as importantly, securely. The remote DBA must be BETTER than the localized counterpart to get management cooperation. This approach to the DBA infrastructure allows for the average DBA to be more productive, and, in building the infrastructure, the DBA becomes better than average.

Mike's Law: "If you can code yourself out of a job, you will never lack for a job."

The final piece to the puzzle is adding a less than three hundred dollar widget to your television and telephone giving you the ability to attend conferences and meetings from your home-office (A-hem, please get dressed first!) Even if the company provides your home PC equipment, the savings they realize by not needing to provide you with workspace more than covers any phone and Internet related charges.

Bibliography

Frequently Asked Questions about Microsoft VPN Security, www.microsoft.com, December 1998.

Iseminger, David; Inside RRAS: Remote Access Solutions for Windows NT, Wiley Computer Publishing, 1998

Minasi, Mark; Deciphering PPTP: A Poor Person’s Firewall, Windows NT Magazine, December 1996, Page 45.

Marlene Theriault, THE ORACLE ENTERPRISE MANAGER - HIP OR HYPE?, The SEER (Delaware Valley Oracle User Group Newsletter, June 1997, available upon request from DEVOUG or author.

Marlene Theriault, DB Phone Home -- An Overview of the Oracle Enterprise Manager, The SEER, March 1999

Datafellows - www.datafellows.com

Quest Software - www.quests.com

Mortice Kern Systems - www.mks.com

About the Authors

Michael Hillanbrand is a DBA and Principal with Dulcian, Inc. He has been a Vice President of the Delaware Valley Oracle Users Group for the last five years. He has over twenty years experience in IT and more than ten years in systems and Oracle. Mike is the architect of Dulcian's DBAGuardä suite of Database Administration Services. Past presentations have been offered at DEVOUG, NYOUG and IOUG-A. He can be contacted at mjhii@dulcian.com or through Dulcian’s Website at www.dulcian.com.

Ed Haskins is the Network Manager of Dulcian, Inc. and an active member of the New Jersey NT Users Group. Ed is also a Microsoft Certified Professional and a Compaq Accredited Systems Engineer. He can be contacted at ehaskins@dulcian.com or through Dulcian’s Website at www.dulcian.com.