Technical tip: Secure DB2 communications using OpenSSH tunneling

Secure communication between DB2 server and client using ssh tunneling

Follow the steps outlined in this article to use SSH for secure communication between IBM® DB2® clients and servers.

Sandeep Ramesh Patil (sandeep.patil@in.ibm.com), Senior Staff Software Engineer, IBM

Photo of Sandeep PatilSandeep Ramesh Patil is a Senior Staff Software Engineer for the IBM India Software Labs. He has worked for IBM for the past six years, focusing on distributed technology including DCE, SARPC, and security products such as the IBM Network Authentication Services (IBM Kerberos). He is currently developing new features and implementing security-related RFC for the IBM Network Authentication Service, along with its product support. Sandeep holds a BS degree in computer science and engineering from the University of Pune, India. You can contact him at sandeep.patil@in.ibm.com.



Manish Katiyar, System Software Engineer, IBM

Photo of Manish KatiyarManish Katiyar is a System Software Engineer for the IBM India Software Labs. He has worked for IBM for the past three years, focusing on SARPC, and has experience on Data Warehousing tools (Ab-Initio). Manish holds a Bachelor of Technology degree in Chemical Engineering from the Indian Institute of Technology Kharagpur, India. You can contact him at manish.katiyar@in.ibm.com.



26 April 2007

Introduction

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 Resources 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 Resources).

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.

  1. On your Windows client machine, open DB2 9 Client Configuration Assistant. (You can also use db2ca from a command line.)
  2. Select Selected > Add database using wizard... > Manually configure a connection to a database > TCP/IP.
  3. Fill hostname as localhost and port number of your choice ("12345," in this example), as shown in Figure 1.
    Figure 1. DB2 9 client configuration
    client configuration
  4. 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
    node configuration
  5. 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 netstat.

See Resources 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.

  1. Create a new session, choosing SSH as the mode of communication.
  2. In the category tree select SSH > tunnels.
  3. Fill in the entries, as shown in Figure 3, and add the desired port.
    Figure 3. Configuring PuTTY
    putty configuration
  4. Make sure you go back to sessions and save the configuration.
  5. 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 netstat command 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
setup
  1. Log in to astrix using the previously configured PuTTY session.
  2. Open the DB2 9 Control Center. (You should see the database name that you added previously.)
  3. 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
netstat

Since all data is now tunneled through SSH, it is secured by the current encryption mechanism in use by SSH.


Summary

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.

Resources

Learn

Get products and technologies

  • 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.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


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

All information submitted is secure.

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.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, Open source
ArticleID=215939
ArticleTitle=Technical tip: Secure DB2 communications using OpenSSH tunneling
publish-date=04262007