Configuring SSL for client-server instance communication

Databases can contain sensitive information; these days, the main concern is related to the security of data stored in tables as well as those sent over the network. One method of securing network communication between server and client is SSL, which is actually an abbreviation for Secure Socket Layer. We do not delve further into too much theory. Mainly, SSL addresses the following important security considerations: authentication, confidentiality, and integrity. Mainly SSL encryption and other network communication or also named data in transit encryption methods protects against unauthorized packet interception and analysis performed by an interposed person between a client and a server, also known as eavesdropping.

The DB2 instance has built-in support for SSL. DB2 relies on Global Security Kit for implementing SSL. GSKit is included in the IBM DB2 ESE software installation kit or is downloadable for free from IBM's website. Next, we'll show how to implement a secure connection between a DB2 server and a DB2 client.

Getting ready

For the next recipe, we will use nodedb21 (db2inst1 instance) as server and nodedb22 (db2clnt1 instance) as client, where we have installed DB2 Client in previous recipes. You need to ensure that you have GSKit libraries in LD_LIBRARY_PATH. In our case, the libraries that are located in /home/db2inst1/sqllib/lib64 are pointing to the /opt/ibm/db2/V9.7/lib64 location.

How to do it...

1. The first step is to add the gsk8capicmd_64 executable in our PATH.

   Include the following in .bash_profile:

   PATH=$PATH:$HOME/bin:$HOME/sqllib/gskit/bin

   Execute source .bash_profile to reinitialize the user environment.

2. To create a key database on the server, execute the following (for more information about gsk8capicmd_64, execute gsk8capicmd_64 –help):

   [db2inst1@nodedb21 ~]$ gsk8capicmd_64 -keydb -create -db "/home/db2inst1/keystoredb2inst1.kdb" -pw "db2cookbook" -stash
   [db2inst1@nodedb21 ~]$
   

3. Create a self-signature and self-sign the key database on the server:

   [db2inst1@nodedb21 ~]$ gsk8capicmd_64 -cert -create -db "/home/db2inst1/keystoredb2inst1.kdb" -pw "db2cookbook" -label "db2cookbooksignature" -dn "CN=www.packtpub.com,O=Packt Publishing,OU=Packt Publishing"
   [db2inst1@nodedb21 ~]$
   

4. Extract the signature for signing the client key database:

   [db2inst1@nodedb21 ~]$ gsk8capicmd_64 -cert -extract -db "/home/db2inst1/keystoredb2inst1.kdb" -label "db2cookbooksignature" -target "/home/db2inst1/db2cookbook.arm" -format ascii -fips -pw "db2cookbook"
   [db2inst1@nodedb21 ~]$
   

5. Next, create the client key database:

   [db2inst1@nodedb21 ~]$ gsk8capicmd_64 -keydb -create -db "/home/db2inst1/keystoreclientdb2inst1.kdb" -pw "db2ckbk" –stash
   [db2inst1@nodedb21 ~]$
   

6. Import the self-signed certificate into the client key database:

   [db2inst1@nodedb21 ~]$ gsk8capicmd_64 -cert -add -db "/home/db2inst1/keystoreclientdb2inst.kdb" -pw "db2ckbk" -label "db2cookbooksignature" -file "/home/db2inst1/db2cookbook.arm" -format ascii –fips
   [db2inst1@nodedb21 ~]$
   

7. To enable SSL as communication protocol on nodedb21, execute the following:

   [db2inst1@nodedb21 ~]$ db2set DB2COMM=tcpip,ssl –i
   [db2inst1@nodedb21 ~]$
   

8. Enable SSL as communication protocol also on the client side:

   [db2clnt1@nodedb21 ~]$ db2set DB2COMM=tcpip,ssl –i 
   [db2clnt1@nodedb21 ~]$
   

9. Next, on nodedb21, set SSL-related parameters on the server instance; then, stop and start the instance:

   [db2inst1@nodedb21 ~]$ db2 "update dbm cfg using ssl_svr_keydb /home/db2inst/keystoredb2inst1.kdb"
   DB20000I  The UPDATE DATABASE MANAGER CONFIGURATION command completed
   successfully.
   [db2inst1@nodedb21 ~]$ db2 "update dbm cfg using ssl_svr_stash /home/db2inst/keystoredb2inst1.sth"
   DB20000I  The UPDATE DATABASE MANAGER CONFIGURATION command completed
   successfully.
   [db2inst1@nodedb21 ~]$ db2 "update dbm cfg using ssl_svr_label db2cookbooksignature"
   DB20000I  The UPDATE DATABASE MANAGER CONFIGURATION command completed
   successfully.
   [db2inst1@nodedb21 ~]$ db2 "update dbm cfg using ssl_svcename 50004"
   DB20000I  The UPDATE DATABASE MANAGER CONFIGURATION command completed
   successfully.
   [db2inst1@nodedb21 ~]$ db2stop
   06/09/2011 19:08:39     0   0   SQL1064N  DB2STOP processing was successful.
   SQL1064N  DB2STOP processing was successful.
   [db2inst1@nodedb21 ~]$ db2start
   06/09/2011 19:08:45     0   0   SQL1063N  DB2START processing was successful.
   SQL1063N  DB2START processing was successful.
   

   Note

   Description of SSL-related parameters used on the server side:

   • SSL_SVR_KEYDB specifies a fully qualified filepath of the key file to be used for SSL setup at server side

   • SSL_SVR_STASH—specifies a fully qualified filepath of the stash file to be used for SSL setup at server side

   • SSL_SVR_LABEL—specifies a label of the personal certificate of the server in the key database

   • SSL_SVCENAME—specifies the name of the port that a database server uses to await communications from remote client nodes using SSL protocol

   • Be careful to set the correct paths, otherwise SSL won't work.

10. Copy /home/db2inst1/keystoreinstclient.kdb and /home/db2clnt1/keystoreinstclient.sth to nodedb22.

11. On nodedb22, set SSL DB2 client instance-related parameters:

    [db2clnt1@nodedb22 ~]$ db2 "update dbm cfg using SSL_CLNT_KEYDB /home/db2clnt1/keystoreclientdb2inst.kdb"
    DB20000I  The UPDATE DATABASE MANAGER CONFIGURATION command completed successfully.
    [db2clnt1@nodedb22 ~]$  db2 "update dbm cfg using SSL_CLNT_STASH /home/db2clnt1/keystoreclientdb2inst.sth"
    DB20000I  The UPDATE DATABASE MANAGER CONFIGURATION command completed successfully.
    

    Note

    Description of SSL-related parameters on the client side:

    SSL_CLNT_KEYDB specifies the fully qualified filepath of the key file to be used for SSL connection at the client side

    SSL_CLNT_STASH specifies the fully qualified filepath of the stash file to be used for SSL connections at the client side

12. Next, copy GSKit libraries to the client's DB2HOME/lib64 directory:

    [root@nodedb22 ~]# cp /opt/ibm/db2/V9.7_part/lib64/libgsk8* /opt/ibm/db2/V9.7/lib64/
    [root@nodedb22 ~]#
    

How it works...

SSL establishes the connection between client and server using a mechanism called handshake. There is a lot of information on the Internet about SSL and its working. Briefly, these are the steps for SSL handshake:

1. The client requests an SSL connection, listing its SSL version and supported cipher suites.

2. The server responds with a selected cipher suite.

3. The server sends its digital certificate to the client.

4. The client verifies the validity of the server's certificate (server authentication).

5. Client and server securely negotiate a session key.

6. Client and server securely exchange information using the key selected previously.

There's more...

In this recipe, we used a self signed certificate, which is fine for testing or internal use. For production environments, you should use trusted certificates signed by a third-party certification authority.

Other methods for encrypting data in transit can be implemented by using DATA_ENCRYPT and DATA_ENCRYPT_CMP as authentication methods. Also using port forwarding with SSH tunnels is a good option.

See also

Chapter 10, DB2 Security