Introduction

The IBM Db2 database system supports Secure Sockets Layer (SSL) where the client can establish the connection to the db2 server using SSL socket. CLP and .Net Data Provider client applications and applications that use the IBM Data Server Driver for JDBC and SQLJ (type 4 connections) support SSL. The step-by-step approach in this article will show you how to configure the SSL on the DB2 HADR environment. The detailed steps also include how to create and extract the SSL Certificate–and how to reset the HADR after the SSL configuration.

Assumptions

Here we assume that we have a Db2 V11.1 HADR setup with a database. Below are the details:

Primary server: appduv22d0.ibmsl.cloud.test.group

Standby server: appduv22d1.ibmsl.cloud.test.group

DB2 instance primary server: db2inst1

DB2 instance standby server: db2inst1

DB2 database: testdb1

Step 1: Environmental setup on the primary server

Here we will first set the environmental variable i.e., LD_LIBRARY_PATH on the DB2 Primary server.

To set LD_LIBRARY_PATH variable login as the db2 instance owner.

  • Login as db2inst1
  • Set the LD_LIBRARY_PATH environment variable as follows:
    $ export
    LD_LIBRARY_PATH=/home/db2inst1/sqllib/lib64:/home/db2inst1/sqllib/lib64/gskit:/home/db2inst1/sqll
    ib/lib32
    
  • Verify the path
    $ env|grep LD_LIBRARY
    LD_LIBRARY_PATH=/home/db2inst1/sqllib/lib64:/home/db2inst1/sqllib/lib64/gskit:/home/db2inst1/sqll
    ib/lib32
    
    Step 2: Create the key database and set up the digital certificate

GSKCapiCmd is a non-java command line utility to create a key database and set up your digital certificates. Invoke gsk8capicmd as per the 32 bit OR 64 bit server.

Note: On 32-bit platforms use the “gsk8capicmd” utility, and on 64-bit platforms use the “gsk8capicmd_64” utility. Below commands are based on 64-bit server.

  • Browse to the “gsk8capicmd_64” location i.e., /opt/ibm/db2/V11.1/gskit/bin
$ pwd
/opt/ibm/db2/V11.1/gskit/bin
$ ls
gsk8capicmd gsk8capicmd_64 gsk8ver gsk8ver_64
  • Create a key database

For example, the following command creates a key database called Primarydb.kdb and a stash file called Primarydb.sth

$ /opt/ibm/db2/V11.1/gskit/bin/gsk8capicmd_64 -keydb -create -db "/home/db2inst1/Primarydb.kdb" -
pw "xxxxxx" –stash

The -stash option creates a stash file at the same path as the key database, with a file extension of .sth. At instance start-up, GSKit uses the stash file to obtain the password to the key database.

  • Verify the Following Files are Created at the db2 instance home location i.e. ‘/home/db2inst1’
$ ls -ltr | grep -i key
-rw-------. 1 db2inst1 db2iadm1 88 Feb 1 10:58 Primarydb.crl
-rw-------. 1 db2inst1 db2iadm1 88 Feb 1 10:58 Primarydb.kdb
-rw-------. 1 db2inst1 db2iadm1 88 Feb 1 10:58 Primarydb.rdb
-rw-------. 1 db2inst1 db2iadm1 193 Feb 1 10:58 Primarydb.sth

Step 3. Add the certificate to the key database

The server sends this certificate to clients during the SSL handshake to provide authentication for the server. To obtain a certificate, you can either use GSKCapiCmd to create a new certificate request and submit it to a certificate authorities(CA) to be signed, or you can create a self-signed certificate for testing purposes.

For example, to create a self-signed certificate with a label of “Primaryselfsigned”, use the GSKCapiCmd command.

$ /opt/ibm/db2/V11.1/gskit/bin/gsk8capicmd_64 -cert -create -db "/home/db2inst1/Primarydb.kdb" -
pw "xxxxxx" -label "Primaryselfsigned" -dn "CN= appduv22d0.ibmsl.cloud.test.group
,O=IBM,OU=DatabaseCloud ,L=AP,ST=ON,C=INDIA"
$ ls -ltr | grep -i key
-rw-------. 1 db2inst1 db2iadm1 88 Feb 1 10:58 Primarydb.crl
-rw-------. 1 db2inst1 db2iadm1 5088 Feb 1 11:24 Primarydb.kdb
-rw-------. 1 db2inst1 db2iadm1 88 Feb 1 10:58 Primarydb.rdb
-rw-------. 1 db2inst1 db2iadm1 193 Feb 1 10:58 Primarydb.sth

Note: After adding the certificate the size of Primarydb.kdb will change.

Step 4: Extract the certificate

Extract the certificate so that you can distribute it to client computers which will be establishing SSL connections to your Db2 server.

For example, the following GSKCapiCmd command extracts the certificate to a file called mydbserver.arm:

$ /opt/ibm/db2/V11.1/gskit/bin/gsk8capicmd_64 -cert -extract -db "/home/db2inst1/Primarydb.kdb" -
pw "xxxxxx" -label "Primaryselfsigned" -target “/home/db2inst1/Primary.arm” -format ascii -fips

Note : Make sure it has created a file for extracting a certificate “Primary.arm” at the db2 instance home location “/home/db2inst1/”.

$ ls -ltr | grep -i my
-rw-rw-r--. 1 db2inst1 db2iadm1 1001 Feb 1 11:34 Primary.arm

Step 5: Display the certificate

To display the certificate, issue the following command:

$ /opt/ibm/db2/V11.1/gskit/bin/gsk8capicmd_64 -cert -details -db "/home/db2inst1/Primarydb.kdb" -
pw "xxxxxx" -label "Primaryselfsigned"

Note: You will see the certificate below:

Label : Primaryselfsigned
Key Size : 1024
Version : X509 V3
Serial : 6c545fcd390ce81f
Issuer : CN=appduv22d0.ibmsl.cloud.test.group,OU=DatabaseCloud,O=IBM,L=AP,ST=ON,C=INDIA
Subject : CN=appduv22d0.ibmsl.cloud.test.group,OU=DatabaseCloud,O=IBM,L=AP,ST=ON,C=INDIA
Not Before : 31 January 2019 11:24:48 GMT
Not After : 1 February 2020 11:24:48 GMT
Public Key
30 81 9F 30 0D 06 09 2A 86 48 86 F7 0D 01 01 01
05 00 03 81 8D 00 30 81 89 02 81 81 00 B9 24 22
B9 BE DB 8E 88 3D 41 19 3C 85 E8 94 5F BE 64 A4
EC 35 1A DA D9 5A 39 45 9D 9A CA 9E 11 E0 C8 E0
CD CF 02 70 61 8E B0 02 81 BD F7 BD 2D 8A 9D CD
28 D2 6F B6 71 F0 42 89 6B 00 9B 6B FE 8A C3 DF
8C D2 B0 DA 1C E1 0E E8 F8 B7 CD 37 C3 9C 19 5A
A0 58 E7 01 EF AE E8 9B B1 95 DF 09 4D 24 E0 B5
6C 9A 4D B8 7E C8 CD 4A 50 17 A0 E6 E6 FB 71 06
43 78 05 16 CE 90 54 0F 02 0A A1 2D 39 02 03 01
00 01
Public Key Type : RSA (1.2.840.113549.1.1.1)
Fingerprint : SHA1 :
92 9D DA EF 37 A4 C4 5A 21 74 8A D5 C0 9F 1B DB
8F 33 1D 15
Fingerprint : MD5 :
F7 CC D3 6D C6 F2 8D 01 03 86 97 3F 02 61 50 17
Fingerprint : SHA256 :
FF 96 4B D3 67 EE B8 0F EA F4 6B 54 BE 9C 08 07
94 76 0C 8C 8A 5B A2 C0 AE 60 EE 43 DD 14 A0 5C
Extensions
SubjectKeyIdentifier
keyIdentifier:
AE C9 FB BC 69 10 51 FE 9A 6A 1B 60 B8 CA 02 1E
25 27 E7 21
AuthorityKeyIdentifier
keyIdentifier:
AE C9 FB BC 69 10 51 FE 9A 6A 1B 60 B8 CA 02 1E
25 27 E7 21
authorityIdentifier:
authorityCertSerialNumber:
Signature Algorithm : SHA1WithRSASignature (1.2.840.113549.1.1.5)
Value
89 A3 D2 F7 E5 19 AC FB 98 A7 9A 7B 6C D9 C5 40
B8 81 A7 0E 8B 5F F2 CD A9 D2 92 0F 73 A4 C7 56
98 D7 8D 2C 5A D7 8D 14 D9 AC B5 B3 D2 E6 5A A6
A5 A6 DB 38 8E 1A 4D E6 F2 C4 31 7C A9 F7 1D DF
C8 29 26 6F 8F 44 1E C0 40 08 44 D7 0C 20 D2 A2
FC 48 95 36 FF 5D 16 9E 2B 6D D9 5A E1 F2 F8 11
DF 5F F3 FE 54 F2 5D DF E9 B8 D1 7D 74 8C 40 F1
F4 CD 68 25 1A AA B4 1A 6A AA 4B 6F E1 01 7A 1D
Trust Status : Enabled

Step 6: Repeat steps 1 through 5 on the standby server

Repeat the steps on each standby server i.e., environment setup, create a key db, add the ceritifcate, and extract and display the certificate.

Example:

  • Environmental setup
    $ export
    LD_LIBRARY_PATH=/home/db2inst1/sqllib/lib64:/home/db2inst1/sqllib/lib64/gskit:/home/db2inst1/sqll
    ib/lib32
    
  • Create a keydb
    $ /opt/ibm/db2/V11.1/gskit/bin/gsk8capicmd_64 -keydb -create -db "/home/db2inst1/Standbydb.kdb"
    -pw "xxxxxx" –stash
    
  • Add the certificate
    $ /opt/ibm/db2/V11.1/gskit/bin/gsk8capicmd_64 -cert -create -db "/home/db2inst1/Standbydb.kdb" -
    pw "xxxxxx" -label "Standbyselfsigned" -dn "CN= appduv22d0.ibmsl.cloud.test.group
    ,O=IBM,OU=DatabaseCloud ,L=AP,ST=ON,C=INDIA"
    
  • Extract the certificate
    $ /opt/ibm/db2/V11.1/gskit/bin/gsk8capicmd_64 -cert -extract -db "/home/db2inst1/ Standbydb.kdb" -
    pw "xxxxxx" -label " Standbyselfsigned " -target “/home/db2inst1/Standby.arm” -format ascii -fips
    
  • Display the certificate
    $ /opt/ibm/db2/V11.1/gskit/bin/gsk8capicmd_64 -cert -details -db "/home/db2inst1/Standby.kdb" -pw
    "xxxxxx" -label " Standbyselfsigned "
    
    Step 7: Add the primary and standby certificates

Now add the primary and standby certificates to the key database at each primary and standby instance.

  • Copy the certificate file from primary to standby and vice versa. FTP the file that contains the primary instance’s certificate to the standby instance. This file was extracted in a previous step into a file called primary.arm. Also, FTP the file that contains the standby instance’s certificate, standby.arm, to the primary instance. Place these files into the directory where you created your key database on each instance.

Example:

  • On primary execute the below command:
    $ scp /home/db2inst1/Primary.arm db2inst1@appduv22d1:/home/db2inst1
    
  • On standby execute the below command:
    $ scp /home/db2inst1/Standby.arm db2inst1@appduv22d0:/home/db2inst1
    
  • Add the primary instance’s certificate into the standby’s key database.
  • On standby execute the below command:
    $ /opt/ibm/db2/V11.1/gaskit/bin/gak8campicmd_64 -cert -add -db “/home/db2inst1/Standbydb.kdb” -
    pw “xxxxxx” -label “Primaryselfsigned” -file “/home/db2inst1/Primary.arm” -format ascii -fips
    

Note: After adding the certificate the size of Standbydb.kdb will change.

$ ls -ltr | grep -i key
-rw-------. 1 db2inst1 db2iadm1 10088 Feb 1 15:26 Standbydb.kdb

Add the standby instance’s certificate into the primarydb’s key database.

  • On primary execute the below command:
    $ /opt/ibm/db2/V11.1/gaskit/bin/gak8campicmd_64 -cert -add -db “/home/db2inst1/Primarydb.kdb” -
    pw “xxxxxx” -label “Standbyselfsigned” -file “/home/db2inst1/Standby.arm” -format ascii -fips
    
    Note: After adding the certificate the size of Primarydb.kdb will change.
    $ ls -ltr | grep -i key
    -rw-------. 1 db2inst1 db2iadm1 10088 Feb 1 15:30 Primarydb.kdb
    
    Note: If multiple standby databases exist, the certificate from each instance in the HADR configuration must be imported into the key database of each instance as demonstrated above.

Step 8: Set up the Db2 instances for SSL support

To set up your Db2 server for SSL support, log in as the Db2 instance owner and set the following configuration parameters:

  • Set the ssl_svr_keydb configuration parameter.

Set this configuration parameter to the fully qualified path of the key database file. This step must be done on the Db2 instance of the primary and all standby databases.

On primary:

$ db2 update dbm cfg using SSL_SVR_KEYDB '/home/db2inst1/Primarydb.kdb'

On standby:

$ db2 update dbm cfg using SSL_SVR_KEYDB '/home/db2inst1/Standbydb.kdb'
  • Set the ssl_svr_stash configuration parameter

On primary:

$ db2 update dbm cfg using SSL_SVR_STASH '/db2home/db2inst1/Primarydb.sth'

On standby:

$ db2 update dbm cfg using SSL_SVR_STASH '/db2home/db2inst1/Standbydb.sth'
  • Verify the parameters are set

On primary:

$ db2 get dbm cfg | grep SSL

Primary screenshot

On standby:

Standby screenshot

Step 9: Restart the DB2 instance

Restart the Db2 instance both on primary and standby server:

$ db2 deactivate database testdb1
$ db2 terminate
$ db2stop
$ db2start

Step 10: Enable SSL communication for each primary and standby database

On primary:

$ db2 update db cfg for testdb1 using HADR_SSL_LABEL Primaryselfsigned
$ db2 get db cgf for testdb1 | grep HADR_SSL_LABEL
HADR SSL Label Certificate (HADR_SSL_LABEL) = Primaryselfsigned

On standby

$ db2 update db cfg for testdb1 using HADR_SSL_LABEL Standbyselfsigned
$ db2 get db cgf for testdb1 | grep HADR_SSL_LABEL
HADR SSL Label Certificate (HADR_SSL_LABEL) = Standbyselfsigned

Note: If hadr_ssl_label is set for one primary or standby, then it must be set for all primary and standby databases in the configuration. If hadr_ssl_label is not set for all databases, then some HADR connections between primary and standby databases fail.

If the hadr_ssl_label is set, then both the ssl_svr_keydb and ssl_svr_stash must be set. If not, then HADR cannot be started, or some HADR connections between primary and standby databases fail.

Step 11: Restart and monitor the HADR

On standby :

$ db2 start hadr on database testdb1 as standby
$ db2pd -db testdb1 -hadr

Standby screenshot 2

On primary :

$ db2 start hadr on database testdb1 as primary
$ db2pd -db testdb1 -hadr

Primary screenshot 2

Conclusion

You’ve configured SSL in Db2 HADR environment, and now you have a better understanding of how to create a keydb and extract the digital SSL certificate. In additiom, you’re now able to set up the Db2 server for SSL support and then reset the HADR.