DB2 technical tip: Set up Secure Sockets Layer (SSL) for DB2 on Windows

Step-by-step instructions

Using Secure Sockets Layer (SSL) with IBM® DB2® means your data can be sent securely over the network. In this technical tip, learn how to set up this protocol for DB2 on the Windows® platform.

Share:

Praveen R. Sogalad (psogalad@in.ibm.com), System Software Engineer, IBM

Praveen SogaladPraveen Sogalad works Common Application Development for JCC QA team in the IBM India Software Labs. Prior to this role, he was as an application developer for the DB2 Samples Development team in the IBM India Software Labs



12 June 2008

Introduction

Secure Sockets Layer (or SSL) is a protocol that lets services communicate over a network without compromising security. It creates a secure connection between a client and a server. Any amount of data can then be sent securely over that connection. You should consider using SSL if, for example, you process credit cards through an online application, process sensitive data such as personal identification information, or need to comply with privacy standards.

The IBM DB2 driver for JDBC and SQLJ (type 4 connectivity) contains Secure Socket Layer (SSL) support for DB2 for z/OS®. Beginning with DB2 Version 8 Fix Pack 16, the DB2 for Linux®, UNIX®, and Windows data server also contains SSL support. Note, you can use SSL support to communicate between DB2 data servers and the IBM DB2 Driver for JDBC and SQLJ type 4 connectivity only. The SSL connection will always be in Federal Information Process Standards (FIPS) mode.

In order to have SSL support for DB2 data servers, the IBM Global Security Kit (GSKit) version 7c must to be installed on the server.

For DB2 data servers to recognize SSL support, the DB2COMM registry variable has to be set to SSL, and the SSL configuration file SSLconfig.ini must be created in the instance directory . This file will store the SSL parameters that are used to load and start SSL. Table 1 shows the SSL parameters you can set:

Table 1. SSL Parameters
Name of SSL parametersNullable ?Description
DB2_SSL_KEYSTORE_FILE NOFully Qualified file name of KeyStore that stores the Server Certificate
DB2_SSL_KEYSTORE_PWYESPassword of KeyStore that stores the Server Certificate
DB2_SSL_KEYSTORE_LABELYESLabel of Server Certificate
DB2_SSL_LISTENER NOService name/Port number for SSL Listener

SSL setup steps

Step 1: Install the Global Security Kit

For Window 64 bit platforms, the Global Security Kit (GSKit) is not bundled with DB2. It must be installed separately. The latest version of the Global Security Kit (GSKit) tool package can be downloaded from the IBM software download site.

The download site also contains:

  • The Global Security Kit Install Guide
  • Secure Socket Layer Introduction and iKeyman User's Guid

Step 2: Configure your system environment variables

  1. Right click on My Computer and select Properties –> Advanced –> Environment variables.
  2. Set these values:
    • JAVA_HOME= $JAVA_HOME; C:\Program Files\IBM\SQLLIB\java\jdk\jre;
      Note: The above JDK path should be the path for JRE that comes when you install DB2.
    • PATH = C:\Program Files\IBM\gsk7\lib;$PATH;
    • CLASSPATH= C:\Program Files\IBM\gsk7\classes\cfwk.zip;C:\Program Files\IBM\gsk7\classes\gsk7cls.jar;$CLASSPATH;
    • LIB = C:\Program Files\IBM\gsk7\lib;C:\PROGRA~1\IBM\SQLLIB\LIB;$LIB

Step 3: Create the server keystore and certificate

Perform these steps:

  1. Open a DB2 command window. You can do that with the db2cmd command.
  2. Invoke IKEYMan to start the process to generate a new CMS key:
    C:\Program Files\IBM\gsk7\bin> gsk7ikm.exe
    This command brings up the IBM Key Management (IKEYMAN) GUI window. Perform the remaining steps from IKEYMan GUI window.
  3. Create a new Key Database file, key.kdb of type CMS. Provide a password (remember this for later).
  4. Extract the certificate from the selected key to a file using the GUI.
  5. Create a new self-signed certificate, define the key label (can be anything, such as SSLLabel ) and provide the organization name. Select OK. The remaining items are optional.
  6. The new certificate will appear in the menu. Select it and choose View/Edit from the buttons on the right pane. Make sure Set the Certificate as the Default is selected
  7. Select the certificate (by Label SSLLabel) and click Extract Certificate. The default name cert.arm can be used
  8. Select OK. Confirm Update existing file and close IKeyMan

Step 4: Configure the DB2 environment

  1. Set the DB2COMM environment variable with the following command:
    db2set DB2COMM=SSL
  2. Create the file C:\Program Files\IBM\SQLLIB\DB2\SSLconfig.ini, or edit if it already exists. Modify path, port and password as required. Include the following:
    • DB2_SSL_KEYSTORE_FILE= C:\Program Files\IBM\gsk7\bin\key.kdb
    • DB2_SSL_LISTENER=30171 (This value should be the one which doesn’t exist in your C:\WINDOWS\system32\drivers\etc\services file.)
    • DB2_SSL_KEYSTORE_PW=xxxxxx (This password should be the one which you give while creating the key.kdb)
    • DB2_SSL_KEYSTORE_LABEL=SSLLabel

Step 5: Start and stop DB2

After the steps above, stop and start DB2 using db2stop and db2start from a command window.

Note: You should see the following messages after db2stop and db2start.

C:\Program Files\IBM\gsk7\bin>db2stop
10/30/2007 16:16:42     0   0   SQL1064N DB2STOP processing was successful.
SQL1064N DB2STOP processing was successful.

C:\Program Files\IBM\gsk7\bin>db2start
10/30/2007 16:16:48     0   0   SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.

Note: If you see the message shown below after db2start, then there may be some problem with either the system environment variables setting or with your SYSConfig.ini file.

C:\Program Files\IBM\gsk7\bin>db2start
10/30/2007 16:19:15     0   0   SQL5043N Support for one or more 
communications protocols failed to start successfully. However, 
core database manager functionality started successfully.
SQL1063N DB2START processing was successful.

Note: Check in the db2diag.log file for the following messages

  • CHANGE: CFG DB2SET: DB2COMM: From: "TCPIP" To: "SSL"
  • MESSAGE: number of SSL connection managers were started.
  • MESSAGE: DIA3000I "SSL" protocol support was successfully started.
  • MESSAGE: SSL is setup properly.

If you find all the above messages in the db2diag.log file, then SSL has been set up successfully on your machine, and you can use this machine as your server. For some DB2 versions (such as DB2 V8 fp16) you will not see the above messages. In this case you just need to confirm SSL has been set up correctly by running the test case SSLtest.java shown in Listing 1.

Step 6: Configure the client

  1. FTP the certificate (cert.arm) to the client machine.
  2. Run the keytool import command. Provide the password used above. Here is an example of the dialog from running this command:
    Listing 1. Configuring the client with keytool import
    C:\Program Files\IBM\gsk7\bin> keytool -import -file cert.arm -keystore keystore
    Enter keystore password
    Re-enter new password:
    Owner: CN=sidewinder.torolab.ibm.com, O=IBM, C=US
    Issuer: CN=sidewinder.torolab.ibm.com, O=IBM, C=US
    Serial number: 4630c460
    Valid from: Wed Apr 25 11:25:20 EDT 2007 until: Fri Apr 25 11:25:20 EDT 2008
    Certificate fingerprints:
    MD5:  DD:B8:DA:78:F8:8F:6E:2A:CE:D3:47:3A:E9:AC:67:D5
    SHA1: EE:D2:62:3E:84:0D:67:5C:A6:EB:23:39:04:EC:03:4C:1F:54:2F:84
    Signature algorithm name: MD5withRSA
    Version: 3
    Trust this certificate? [no]:  yes
    Certificate was added to keystore

Step 7: Execute a test case

Listing 2 is a Java program that you can use to test SSL.

Listing 2. Executing the test case
public class SSLTest
{
public static void main (String[] args)
{
  String ServerName = "<change>change to your server name</change>";
  int PortNumber = <change>change to SSL Port Defined in SSLconfig.ini</change>;
  String DatabaseName = "<change>change to your db name</change>";
  
    java.util.Properties properties = new java.util.Properties();
    
  properties.put("user", "enter user name");
  properties.put("password", "enter password");
  properties.put("sslConnection", "true");
  
    String url = "jdbc:db2://" + ServerName + ":"+ PortNumber + "/" +
                DatabaseName+ ":traceFile=foobar.txt;traceLevel="+ 0xFFFFFFFF+ ";";
                
				  java.sql.Connection con = null;  
   try
   {
      Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
   }
   catch ( Exception e )
   {
      System.out.println("Error: failed to load Db2 jcc driver.");
   }
   
   try
   {
      System.out.println("url: " + url);
      con = java.sql.DriverManager.getConnection(url, properties);
      java.sql.Statement s2 = con.createStatement();
   
       try
       {
            s2.executeUpdate("drop table t1");
       }
       catch(Exception e)
       {
            System.out.println("drop is failing");
       }
       
       try
       {
            s2.executeUpdate ("create table t1 (c1 int)");
       }
       catch(Exception e)
       {
            System.out.println("create is failing");
       }
       
       String str = "insert into t1 values (100)";
       s2.executeUpdate(str);
       
       java.sql.PreparedStatement ps = con.prepareStatement ("select * from t1");
       java.sql.ResultSet rs = ps.executeQuery ();
       
       while(rs.next())
       {
            System.out.println(rs.getString(1));
       }
       
      con.close();
   }
   catch (Exception e)
   {
      e.printStackTrace();
   }
 }
}

Run the test SSLTest.java:

javac SSLTest.java

Compile and execute SSLTest as follows:
java -Djavax.net.ssl.trustStore=<keystore location> -Djavax. net.ssl.trustStorePassword=<password>SSLTest

Note: Modify keystore location and password as required. For example:
C:\Program Files\IBM\gsk7\bin>java -Djavax.net.ssl.trustStore=keystore -Djavax.net.ssl.trustStorePassword=test123 SSLTest

The output of the SSLTest should be similar to this:
url: jdbc:db2://psogalad:30171/sample 100


Conclusion

SSL is a valuable protocol for protecting your data as it moves through a network. Almost any Internet service can be protected with SSL. Using the configuration instructions and test case verification program above, you can get started with securing your own DB2 data.

Resources

Learn

Get products and technologies

  • Download the Global Security Kit to obtain the DB2 security plugins required for SSL support.
  • Download a free trial version of DB2 Enterprise 9.
  • Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edtion and provides a solid base to build and deploy applications.
  • Download IBM product evaluation versions and get your hands on application development tools and middleware products from DB2, Lotus®, Rational®, Tivoli®, and WebSphere®.

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
ArticleID=313705
ArticleTitle=DB2 technical tip: Set up Secure Sockets Layer (SSL) for DB2 on Windows
publish-date=06122008