75 ways to demystify DB2 #63: Techtip :Steps to configure SSL connection from DB2 Client CLP

Body

Hi there!

Recently I had an opportunity to implement SSL connection from DB2 client's CLP. DB2 Server was already setup with SSL. Just wanted to share the steps I followed to implement SSL connectivity successfully from the client side:

1> Set LD_LIBRARY_PATH, PATH and LIBPATH to point to GSKit libraries (present at $INSTHOME/sqllib/lib64/gskit)  and tools needed to add SSL certificate (present at $INSTHOME/sqllib/gskit/bin)

[db2v10@ts-jkgera new]$ echo $LD_LIBRARY_PATH
/home/db2v10/sqllib/lib64/gskit:/home/db2v10/sqllib/gskit/bin:/home/db2v10/sqllib/lib64:/home/db2v10/sqllib/lib32

[db2v10@ts-jkgera new]$ echo $PATH
/home/db2v10/sqllib/lib64/gskit:/home/db2v10/sqllib/gskit/bin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/db2v10/sqllib/bin:/home/db2v10/sqllib/adm:/home/db2v10/sqllib/misc:/home/db2v10/sqllib/db2tss/bin:/home/db2v10/bin

[db2v10@ts-jkgera new]$ echo $LIBPATH
/home/db2v10/sqllib/lib64/gskit:/home/db2v10/sqllib/gskit/bin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/db2v10/sqllib/bin:/home/db2v10/sqllib/adm:/home/db2v10/sqllib/misc:/home/db2v10/sqllib/db2tss/bin:/home/db2v10/bin

2>Use the GSKCapiCmd tool to create a key database

[db2v10@ts-jkgera new]$ ls -la
total 24
drwxrwxr-x 2 db2v10 db2v10 4096 Sep 24 22:12 .
drwxrwxr-x 3 db2v10 db2v10 4096 Sep 24 22:07 ..
-rw-rw-r-- 1 db2v10 db2v10 1814 Sep 24 22:10 ehaasp-430-mastermanager.cert

Here ehaasp-430-mastermanager.cert is my SSL certificate, which I got after setting SSL on DB2 Server.

[db2v10@ts-jkgera new]$ gsk8capicmd_64 -keydb -create -db "keyclient.kdb" -pw "ibm654321" -stash

This command will create below files:

[db2v10@ts-jkgera new]$ ls -lttr
total 40
-rw-rw-r-- 1 db2v10 db2v10 1814 Sep 24 22:10 ehaasp-430-mastermanager.cert
-rw------- 1 db2v10 db2v10  129 Sep 24 22:13 keyclient.sth
-rw------- 1 db2v10 db2v10   88 Sep 24 22:13 keyclient.rdb
-rw------- 1 db2v10 db2v10   88 Sep 24 22:13 keyclient.kdb
-rw------- 1 db2v10 db2v10   88 Sep 24 22:13 keyclient.crl

3> Add the signer certificate  ehaasp-430-mastermanager.cert into the client key database keyclient.kdb

[db2v10@ts-jkgera new]$ gsk8capicmd_64 -cert -add -db "keyclient.kdb" -pw "ibm654321" -label "SSLLableClt" -file ehaasp-430-mastermanager.cert -format ascii -fips
[db2v10@ts-jkgera new]$

4> Update ssl_clnt_keydb and ssl_clnt_stash configuration parameters to specify the client key-database and the stash file

[db2v10@ts-jkgera new]$ db2 update dbm cfg using SSL_CLNT_KEYDB /home/db2v10/swati/new/keyclient.kdb
DB20000I  The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.

[db2v10@ts-jkgera new]$ db2 update dbm cfg using SSL_CLNT_STASH /home/db2v10/swati/new/keyclient.sth
DB20000I  The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.

5> Catalog the remote node as SSL followed by cataloging remote database

[db2v10@ts-jkgera new]$ db2 catalog TCPIP NODE BIGSQL  REMOTE  <IP_address_of_server>  SERVER 51000  SECURITY SSL
DB20000I  The CATALOG TCPIP NODE command completed successfully.
DB21056W  Directory changes may not be effective until the directory cache is
refreshed.

[db2v10@ts-jkgera new]$ db2 catalog DATABASE BIGSQL  AS BIGSQL  AT NODE BIGSQL
DB20000I  The CATALOG DATABASE command completed successfully.
DB21056W  Directory changes may not be effective until the directory cache is
refreshed.

[db2v10@ts-jkgera new]$ db2 terminate
DB20000I  The TERMINATE command completed successfully.

6> Connect to the remote database

[db2v10@ts-jkgera new]$ db2 connect to BIGSQL user **** using ****

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.6.3
 SQL authorization ID   = *****

 Local database alias   = *****

---

And this is Yay! moment, you created SSL connection successfully to remote server!

These documents are good references in this task:

Configuring Secure Sockets Layer (SSL) support in non-Java DB2 clients

Secure Sockets Layer (SSL) support in DB2 for Linux, UNIX, and Windows

Please let me know if you have any questions.

Thanks,

-Swati Thorve