Secure DB2 communications using OpenSSH tunneling
Secure communication between DB2 server and client using ssh tunneling
As the popularity of Internet increases with every passing hour, so does the need to store this huge volume of data and protect it as it floats around in this mesh. This large volume of data is stored in various available databases, which provide the appropriate level of security and consistency to ensure that your data is safe and away from unwanted users. But as it flows through the network, it may become vulnerable to access by unwanted persons.
DB2 is used by many companies to store such data. Although DB2 provides a sufficient level of security to safeguard the data over the network, it requires administrators to configure the DB2-specific authentication modules. (See Related topics for more information on configuring DB2 databases.) In addition to the configuration overhead, the security modules in DB2 9 supports encryption for only a specific set of data over the wire, as indicated in the "DATA_ENCRYPT" section of "Authentication methods for your server" in the DB2 9 for Linux, UNIX, and Windows documentation (see Related topics).
Secure Shell (SSH), as the name suggests, is a tool that provides a high degree of security and reliability to data as it is transferred across the network. SSH supports multiple encryption types, with most of its implementation supporting stronger encryption types like AES. Also, in SSH, the degree of security needed can be configured by choosing the appropriate method of encryption desired by the user. Moreover, the tool is available on most of the distributed platforms, including IBM AIX®, Microsoft® Windows®, Linux®, and so on. In this article, follow the detailed steps for using SSH for secure communication between DB2 clients and DB2 server, without the explicit need to configure DB2 authentication modules.
What is an SSH tunnel?
SSH, apart from being a mode of communication to remote computers, also provides other features that most users do not use very often. One such feature is port forwarding through SSH tunneling. SSH gives you the option of mapping a local open port to any port of a remote machine. Once mapped, all the traffic destined for the local port is forwarded to the remote machine on the mapped port within a secured tunnel of SSH. Tunneling can be utilized to securely communicate for almost any kind of service.
This article demonstrates how you can exploit the security provided by SSH to create a secure tunnel between your DB2 9 client and DB2 9 server for communication, thus adding more security to your data when it is on network. For the demonstration, this article makes use of the general OpenSSH server package shipped with Red Hat Enterprise Linux and PuTTY for Windows, a free implementation of SSH.
The following definitions are used in the demonstration:
Listing 1. Definitions used in article demonstration
DB2 Version 9 Server hostname : astrix.in.ibm.com , OS : Red Hat Enterprise Linux 4 SSH Server (OpenSSH 3.9) hostname : astrix.in.ibm.com , OS : Red Hat Enterprise Linux 4 DB2 Version 9 client hostname : windee.in.ibm.com , OS : Microsoft Windows XP (SP2) Professional SSH Client (PuTTY release 0.58) hostname : windee.in.ibm.com , OS : Microsoft Windows XP (SP2) Professional
Configuring DB2 9 client
This section describes the steps needed to configure DB2 9 client to use the port-forwarding mechanism of SSH.
- On your Windows client machine, open DB2 9 Client Configuration Assistant. (You can also use
db2cafrom a command line.)
- Select Selected > Add database using wizard... > Manually configure a connection to a database > TCP/IP.
- Fill hostname as
localhostand port number of your choice ("12345," in this example), as shown in Figure 1.
Figure 1. DB2 9 client configuration
- Specify the operating system of DB2 9 server (Linux, in this example case) and DB2
instance name appropriately, as shown in Figure 2.
Figure 2. Configuring Server node
- Add database name.
Note: The port number you specify must be free on local machine, and there
should not be any service running on it. You can verify this from the output of
See Related topics for other configuration options for DB2 9 server and client.
Configuring PuTTY client for tunneling
This section describes the steps needed to configure SSH client (PuTTY, in this example) so that it can forward the traffic of localhost to the destined port on remote machine.
- Create a new session, choosing SSH as the mode of communication.
- In the category tree select SSH > tunnels.
- Fill in the
entries, as shown in Figure 3, and add the desired port.
Figure 3. Configuring PuTTY
- Make sure you go back to sessions and save the configuration.
- Open the SSH session and log in to the machine (astrix.in.ibm.com, in this example
case) using the SSH user id and password for the Linux machine to test your SSH
tunneling configuration. On successful login, the output of the
netstatcommand will show a local port opened and listening for the port you specified (refer to Figure 5).
Users preferring other clients, such as OpenSSH, or users on other IX-based clients can use the following command line to achieve the same effect as the PuTTY configuration:
ssh -L 12345:localhost:50000 astrix.in.ibm.com
Testing the setup
Now you are ready to test the setup and use DB2 9 client and server over tunneled SSH. Figure 4 shows the flow of data when you communicate between DB2 9 client and server.
Figure 4. Test setup
- Log in to astrix using the previously configured PuTTY session.
- Open the DB2 9 Control Center. (You should see the database name that you added previously.)
- Open the database and run some SQL statements against one of the available tables.
The first line in Figure 5 is the output of
netstat -an, which shows that when you open your previously saved
PuTTY session to connect to machine astrix, PuTTY opens up a local socket listening
on port 12345. Other lines show the number and state of sockets opened by DB2 client
on its interaction with the DB2 server. As a result of this setup, DB2 client tries
to connect to DB2 server on localhost:12345, which in turn is securely forwarded to
DB2 server. (For example, astrix:50000, by PuTTY's SSH tunnel, configured by you.)
Figure 5. netstat output
Since all data is now tunneled through SSH, it is secured by the current encryption mechanism in use by SSH.
In this article, you learned how you can utilize the tunneling capability of OpenSSH to set up a secure tunnel between your DB2 9 client and servers. Using OpenSSH to communicate between DB2 server and client gives you the option of choosing the best-suited encryption type, as per requirement from a wide range of supported authentication mechanisms by SSH.
- OpenSSH: Get a free download of OpenSSH for AIX.
- PuTTY: Download PuTTY free.
- Build your next development project with IBM trial software, available for download directly from developerWorks.
- IBM DB2 Database for
Linux, UNIX, and Windows Information Center: Find information you need to use the DB2
family of products and features as well as related WebSphere® Information Integration products and features.
- Authentication methods: Find information about the various types of authentication.
- Encryption considerations: Consult the Information Management Software for z/OS Solutions Information Center for information on the cost of encryption for DB2.
- "Database Configuration Guide: Configuring DB2 databases (Novell, Inc., 2004): Configure your DB2 database for use with the Novell exteNd Application Server as a SilverMaster or deployment database.
- "DB2 connectivity cheat sheet" (developerWorks, January 2003): Get your clients connected to DB2 servers, either on distributed systems or OS/390, using DB2 commands.
- "Tunneling with ssh" (developerWorks, October 2006): Set up of a simple SSH client connecting to an AIX- or Linux-based SSH server that allows you to set up, configure, and operate a flexible means of tunneling data and services over the SSH service.
- "DB2 UDB security, Part 6: Configure Kerberos for authentication on DB2 UDB for Linux, UNIX, and Windows" (developerWorks, March 2006): Set up a single Kerberos realm environment for DB2 Universal Database for Linux, UNIX, and Windows (DB2 UDB), and configure DB2 to use Kerberos authentication.
- "Server clinic: Connect securely with ssh" (developerWorks, July 2003): Harness the power of ssh. Use ssh to work on your servers from remote sites, and learn the tricks to keep progress rolling smoothly.