The first time you sign into developerWorks, a profile is created for you. Select information in your profile (name, country/region, and company) is displayed to the public and will accompany any content you post. You may update your IBM account at any time.

All information submitted is secure.

The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerworks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

All information submitted is secure.

My developerWorks:

My notifications:

Sign out

Select a language:

Tunneling Database Traffic with openSSH and Linux

Marty Lurie (lurie@us.ibm.com), Systems Engineer, IBM Corporation

Marty Lurie started his computer career generating chads while attempting to write Fortran on an IBM 1130. His day job is in Systems Engineering for IBM's Data Management Division, but if pressed he will admit he mostly plays with computers. His favorite program is the one he wrote to connect his Nordic Track to his laptop (the laptop lost two pounds, and lowered its cholesterol by 20%). Marty is an IBM-certified DB2 DBA, IBM-certified Business Intelligence Solutions Professional, and an Informix-certified Professional. He can be reached at lurie@us.ibm.com.

Summary: Client server traffic on TCP/IP networks is an attractive target for hackers. Linux and openSSH provide a simple and effective means of encrypting this traffic. First this article will outline security exposures, and then effective counter measures for protecting the network packets are illustrated. Linux and ssh can also protect Windows clients with the methods presented.

Date: 11 Dec 2003
Level: Introductory

Activity: 15024 views
Comments:

Introduction

	Database traffic is a very attractive target for hostile packet sniffers since data packets can contain highly sensitive information. Protection of this traffic is no longer optional, privacy laws make it mandatory. The way to fight back is for database traffic, if not all traffic, on exposed networks to be encrypted. Linux provides a very economical and effective means of protecting your data packets from prying eyes in a TCP/IP environment.

We'll look at securing the environment where the database servers live, accessing a remote database with a client local to the database server, and then how to protect multi-tier client server access. Specifically we'll examine encrypting and tunneling a JDBC connection through a firewall from a Windows client.

In a few easy steps we'll use openSSH, from www.openSSH.org, to tunnel database traffic via port 22 using the port forwarding capability in openSSH. Port forwarding allows clients that lack any knowledge or SSH binaries to still tunnel their database requests. Yes, Linux is going to help those functionally challenged desktops again.

What is openSSH?

From their web site "OpenSSH is a FREE version of the SSH protocol suite of network connectivity...". So openSSH is "open source secure shell", the logical progression from command shells and rsh. Linux has more shells than your average beach. A few examples are ksh, bsh, bash, csh, ash, tcsh, sorry if I omitted your favorite. The rsh shell, or remote shell, provides unencrypted access to another machine. This is great if you can trust every system on the network. OpenSSH encrypts the packets between systems, so packet sniffers can't peek at what you're sending. You don't much care if your grocery list is compromised, but you'll be rather upset if they get your credit card number.

So what does a shell have to do with database packets? You could open a shell locally to the system with the database server, but this won't support client-server applications. We'll look at both of these cases in more detail.

Securing the server systems

Let me demonstrate my grasp of the obvious: before we worry about the database traffic on the network the servers themselves need to be secure. A good place to start is: "Beginner's guide to armoring Linux" at http://www.spitzner.net/linux.html. This article is a bit dated, eg it refers to ipchains instead of iptables, but it is a good place to get started. Although it is Linux focused, it is also highly relevant to other Unix OS systems. This article is yet another example of the Linux community providing a lucid explanation of a complex problem, and the steps to address the issues.

You'll also need a good firewall environment and Building Internet Firewalls, 2^nd Edition, Zwicky, Cooper, Chapman (http://www.oreilly.com/catalog/fire2/index.html) is an excellent resource.

Remote servers with both database and client: Just say "No" to telnet

In figure 1 there is a workstation accessing a database on a remote server. The user opens a shell on the host "saam" and runs client database software. The security exposure with telnet includes clear text transmission of the userid, password, and all the data packets.

So don't even think of working at the system called "poh" and using: [poh/home] $ telnet saam The userid, password, every query, and all the data returned is available to any packet sniffer snooping your network.

SSH to the rescue: the telnet replacement

SSH provides a simple solution for this environment. Instead of telnet the user runs ssh to access the remote host. For this example we'll use the Postgres database (see www.postgres.org). The user logs into the server poh and runs the following command:

[marty@poh home] $ ssh saam -1 myuserid
The authenticity of host 'saam.biz (192.168.1.65)' can't be established.
RSA key fingerprint is b2:...yada, yada, yada...a:f1.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'saam.biz,192.168.1.65' (RSA) to the list of known hosts.
myuserid@saam.biz's password:
Last login: Mon Nov 17 16:35:35 2003 from 192.168.1.103
[myuserid@saam tmp]$ psql mypsqldb

By using the above access, the entire session is encrypted and secure.

SSH port forwarding: Linux saves the world, again

For client server applications ssh as a replacement for telnet is not an option. We need a way to protect JDBC, ODBC, and native database clients. These clients connect directly to the listener port of the database server, so there is no opportunity to run ssh from the command line. Some clients offer Secure Sockets Layer (SSL) for encryption, see the section titled Other Options for Secure Traffic for more information.

So how does Linux save the world, or at least a small part of it? SSH port forwarding encrypts traffic between servers. The client software thinks it is connecting to the database on a local server port behind the local firewall, when in fact it is being tunneled to a remote server behind a distant firewall. Figure 2 shows the implementation we'll examine in detail, this time we'll use DB2 Version 8.1 as the server.

The database client application still runs on server poh. The database still resides on the remote server saam. The real beauty of this solution is that the client is totally unaware that there are three hops getting to the remote server saam.

Configuration details for port forwarding

Here are the detailed configuration steps for each system shown in Figure 2. The port forwarding command is entered on "tuxpoh", and the client is configured to connect to "tuxpoh".

SSH forwarding on "tuxpoh"

We want a port on "tuxpoh" forwarded to "saam" by way of "tuxsaam". The ssh man page provides the following syntax:

[tuxpoh/home] $ ssh -g -L 9090:saam:50001 tuxsaam

This opens port 9090 on host "tuxpoh" for forwarding. This is a local port on "tuxpoh" where the database clients will connect. The remote target is host "saam" and the port on the "saam" host is 50001. DB2 is running on "saam" and listening on port 50001 for client connections. The "tuxsaam" host ssh process will provide the ssh forwarding to "saam". Interestingly, the "poh" and "saam" systems may or may not have ssh at all! All the encryption/tunneling work is being done on "tuxpoh" and "tuxsaam".

The flags are important. The -L option specifies port forwarding, The -g option specifies that the listener port on "tuxpoh" should be available to other systems, eg. "poh" on the local network. If you want to use privileged ports the forwarding command needs to be run as root.

To find the port number that DB2 is using issue the following command on the "saam" system:

$ db2 get dbm config | grep SVC
 TCP/IP Service name             (SVCENAME) = db2c_DB2

Then look in the services file for the port number:

$ grep db2c_DB2 /etc/services
db2c_DB2        50001/tcp

If your DB2 server is on Win32 look in:

C:\WINNT\system32\drivers\etc>grep db2c_DB2 services
db2c_DB2        50001/tcp

Client configuration on system "POH"
The client is configured to access the database server on a port on the system "tuxpoh". There are many different possibilities for clients, the example here is for JDBC. There are a bunch of samples that come with DB2. Look in the sqllib/samples/java/jdbc directory. If you look in the Util.java program it has a number of options for the connection URL. After creating the tunnel per the instructions above, the following syntax will use the TutRead.java program and an openSSH tunnel to query the database on "saam".

dbc>java TutRead tuxpoh 9090 dbuserid yourpasswdhere

THIS SAMPLE SHOWS HOW TO READ DATA IN A TABLE.
  Connect to 'sample' database using JDBC type 4 driver.

----------------------------------------------------------
USE THE SQL STATEMENTS:
  SELECT
TO PERFORM A BASIC SELECT.

  Perform:
    SELECT deptnumb, deptname FROM org WHERE deptnumb < 40

  Results:
    DEPTNUMB DEPTNAME
    -------- --------------
          10 Head Office
          15 New England
          20 Mid Atlantic
          38 South Atlantic

  Disconnect from 'sample' database.

This method is applicable to other databases, for example see the Postgres documentation at: http://www.postgresql.org/docs/7.4/static/ssh-tunnels.html. It is also not limited to database traffic. There is an example of using this for mail access in LinuxJournal, August 2003, p80.

Other options for secure traffic

The openSSH tunnel is not the only option to secure database traffic. Some have issues with ssh forwarding as a security risk, see Zwicky et al, page 503. Depending on the client software, there may be secure sockets layer SSL support, but configuration can be challenging, see: http://www.postgresql.org/docs/7.4/static/ssl-tcp.html.

Another method of securing traffic is SOCKS (not to be confused with Sox, a sore subject in my city circa October, 2003). It turns out that ssh has SOCKS support built in, see the man page and the -D flag. There is good coverage of this topic on page 233 of Zwicky.

Conclusion

Privacy of information is becoming mandatory and unencrypted network packets are a huge exposure. We've looked at ways to encrypt this traffic with low cost Linux alternatives. I hope you find this a useful vehicle to secure your networks.

Report abuse

Thank you. This entry has been flagged for moderator attention.

Close [x]

Report abuse help

Report abuse

Report abuse submission failed. Please try again later.

Close [x]

developerWorks: Sign in

All information submitted is secure.

Close [x]

Choose your display name

The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.