Introduction

The 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 add a new SSL certificate on the DB2 server in the HADR environment. The detailed steps also include how to remove the old SSL certificate, recreate and extract the new SSL certificate, and how to reset the HADR environment after adding the new SSL certificate.

Assumptions

Here we assume that we have a Db2 v11 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
Existing SSL Certificate label on Primary : Primaryselfsigned
Existing SSL Certificate label on Standby : Standbyselfsigned

We also assume that we have an existing SSL certificate with the label Primaryselfsigned and Standbyselfsigned on primary and standby server respectively with the Key Size 1024 and Signature Algorithm SHA1WithRSASignature.

If we need to add another certificate with Key Size 2048 and Signature Algorithm SHA256WithRSASignature, then we need to disable the HADR first then drop the existing SSL certificate and recreate the new one and then enable DB2 HADR again.

Step 1: Remove the old SSL certificate and the key database

On the primary and standby server, before proceeding for re-creation of the new SSL certificate, we need to drop the existing SSL certificate and the key database.

  • On Primary server, remove the old SSL certificate.

You need to remove the old SSL certificate with the label Primaryselfsigned.

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

Source database password: xxxxxx

  • Verify the certificate is dropped.

To verify the SSL certificate with the label Primaryselfsigned is dropped, try to display the certificate and make sure that it’s not displaying.

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

CTGSK3029W The database does not contain a certificate with label Primaryselfsigned.

  • Remove the old key database.

Remove the old key database Primarydb.kdb , it will also remove the related files.

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

Note: Verify the files are removed from the keydb location i.e. ‘/home/db2inst1/’, also remove the old certificate’s extracted files i.e. primary.arm and standby.arm.

Step 2: Create a new key database

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 keydb location i.e., ‘/home/db2inst1’
$ ls -ltr | grep -i key
-rw-------. 1 db2inst1 db2iadm1 88 Feb 4 12:37 Primarydb.crl
-rw-------. 1 db2inst1 db2iadm1 88 Feb 4 12:37 Primarydb.kdb
-rw-------. 1 db2inst1 db2iadm1 88 Feb 4 12:37 Primarydb.rdb
-rw-------. 1 db2inst1 db2iadm1 193 Feb 4 12:37 Primarydb.sth

Step 3: Adding the new SSL certificate to the key database

Here we’ll add a new SSL certificate with the size 2048 and Signature Algorithm SHA256WithRSA as shown below:

$ /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" -size "2048" -sig_alg "SHA256WithRSA"

Note: Make sure the Primarydb.kdb file is updated with the size and the timestamp.

Adding new SSL certificate

Step 4: Extract the certificate

Extract the certificate you have just created so that you can distribute it to computers running clients that will be establishing SSL connections to your Db2 server.

Note : Remove the old Primary.arm if it exists and extract the certificate with the new file Primary.arm as show below:

$ /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 file was created for extracting a certificate “Primary.arm”.

$ ls -ltr | grep -i my
-rw-rw-r--. 1 db2inst1 db2iadm1 1354 Feb 4 12:43 Primary.arm

Step 5: Display the certificate

After creating and extracting the new SSL certificate, you’ll need to display it and verify the parameters, such as key size, and signature algorithm.

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’ll see the certificate below:

Label : Primaryselfsigned
Key Size : 2048
Version : X509 V3
Serial : 65d7db7c564bd66f
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 : 3 February 2019 12:41:52 GMT
Not After : 4 February 2020 12:41:52 GMT
Public Key
30 82 01 22 30 0D 06 09 2A 86 48 86 F7 0D 01 01
01 05 00 03 82 01 0F 00 30 82 01 0A 02 82 01 01
00 CB AA 51 76 AB BC 2B 24 1A 2A 1E E7 9A B6 FE
4D 4B CE A6 2F AF 59 83 58 D2 6C 0A 76 8B 82 14
3D D1 BF 93 FD 40 7F AD A6 56 98 19 06 73 CA DD
B3 9F D9 B3 EE 1C 43 38 FA C4 1C 6D E8 B1 9D 94
A3 EB 7A 0A A2 E2 72 47 37 C0 6D 5F 2F 6F CF 6F
88 20 E8 20 BF E7 A1 74 9D D8 9A 22 9E E6 FC 67
70 51 1F DF FF F6 8F 03 3C 5A D3 47 0D F4 37 AB
78 E4 7A B3 CE A5 53 14 08 B9 09 57 A9 1A 66 A8
8C BC 70 6D 09 F7 63 F0 73 7B 32 2B 63 B6 79 BF
64 95 FA 2B DA C9 F2 77 38 76 2A 71 92 DE 1B A0
E1 91 50 0E E8 19 C9 68 06 FC 50 84 1B 31 5C E4
25 FE 60 58 17 85 7F C5 94 71 64 FF F7 F2 56 A2
8B 6E 2A 5D 20 D6 E1 32 EC 7D 18 44 2A D7 A7 8D
24 DC 94 C2 14 BD 21 78 3F 59 3D 13 F7 A8 53 37
28 F0 20 BA 64 D2 79 EA 65 E1 3E 9C 6A BB BC C4
EA 00 6C 58 D8 1D D6 E8 66 64 B3 A7 79 41 59 23
95 02 03 01 00 01
Public Key Type : RSA (1.2.840.113549.1.1.1)
Fingerprint : SHA1 :
FD CB 5D 61 47 2C 0D 21 3A AE 23 44 7D 74 66 20
40 9A 6F 3B
Fingerprint : MD5 :
78 D8 9A 99 BD 70 DB C9 D0 A8 D6 DC 54 7D BE DF
Fingerprint : SHA256 :
B1 F6 80 BF E6 E6 94 8C 84 90 50 D8 5C 14 3D 63
00 F2 A7 D1 DA 90 90 05 59 02 25 FD 2E C3 B4 88
Extensions
SubjectKeyIdentifier
keyIdentifier:
93 8D 2E 10 D3 F0 70 85 E5 AB 11 18 8D E6 FF BA
BB E6 03 03
AuthorityKeyIdentifier
keyIdentifier:
93 8D 2E 10 D3 F0 70 85 E5 AB 11 18 8D E6 FF BA
BB E6 03 03
authorityIdentifier:
authorityCertSerialNumber:
Signature Algorithm : SHA256WithRSASignature (1.2.840.113549.1.1.11)
Value
15 0A 41 9E 0D 2F D4 C5 20 F8 81 A2 3A EC A8 2B
3C 49 90 EC BA 52 D9 11 04 BF 99 EA 5B B3 EB 8A
52 A9 E7 50 2B 0B B3 79 CE 32 5F 82 1B 3B EB 7D
BF 58 20 69 BD 4A 27 21 DF 5C 4F 3D 15 99 35 6F
28 BA CE CE A8 4F B5 63 9C 3C B7 16 5C 5A 34 82
64 F5 E1 4B D6 57 E9 A9 E3 56 05 8C 8B 18 E4 B3
1E 75 3A 6F 78 6A 35 67 88 11 5D E4 08 04 04 5C
02 4F 37 03 BA DA 8D A3 85 AB 96 15 19 80 C3 5F
38 B0 93 40 72 40 93 31 2A E1 F2 FC 44 4E 5E 45
61 76 FB 9D 7A EA 92 6E 77 19 5C 9F EA 47 62 36
78 F8 99 80 F5 44 12 44 7A D4 5C 4E 87 43 B6 01
1F D4 9B 2A A7 6A DF 18 A1 C0 FC 90 5B A4 BD 78
3F F3 F7 D6 CE 20 05 50 72 C7 A9 4A 98 89 D3 A6
BB FA 5A 97 D4 72 48 41 11 3C 06 11 73 E6 78 31
25 C1 38 1D DF 9C 77 58 16 10 4A 7F 76 6C 6A 2A
38 42 1B EF 1E 4E 45 FC 7E 26 2D 58 A7 ED EF C5
Trust Status : Enabled

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

On the standby server login as db2 instance owner and repeat from step 1 to step 5 as demonstrated above i.e., remove the old SSL and key database, create a new key database, add the certificate, extract and display the certificate.

  • Remove the old SSL certificate and the key database.
    $ /opt/ibm/db2/V11.1/gskit/bin/gsk8capicmd_64 -cert -delete -db “/home/db2inst1/Standbydb.kdb” -
    pw “xxxxxx” -label “ Standbyselfsigned “
    
  • Verify the certificate is dropped

To verify the SSL certificate with the label Standbyselfsigned is dropped, try to display the certificate and make sure that it’s not displaying the old certificate.

$ /opt/ibm/db2/V11.1/gskit/bin/gsk8capicmd_64 -cert -details -db “/home/db2inst1/Standbydb.kdb” -
pw “xxxxxx” -label “Standbyselfsigned “

CTGSK3029W The database does not contain a certificate with label Standbyselfsigned.

  • Remove the old key database

Remove the old key database Standbydb.kdb, and it will also remove the related files.

$ /opt/ibm/db2/V11.1/gskit/bin/gsk8capicmd_64 -keydb -delete -db "/home/db2inst1/Standbydb.kdb"
-pw "xxxxxx"

Note: Verify the files are removed from the key database location i.e., ‘/home/db2inst1/’, and also remove the old certificate’s extracted files i.e., primary.arm and standby.arm.

  • Create a new key database.
    $ /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" -size “2048” -sig_alg “SHA256_WITH_RSA”
    
  • 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/Standbydb.kdb" -pw
    "xxxxxx" -label " Standbyselfsigned "
    

Step 7: Adding 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.

  • On primary execute the command below:
    $ scp /home/db2inst1/Primary.arm db2inst1@appduv22d1:/home/db2inst1
    
  • On standby execute the command below:
    $ 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 command below:

$ /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 4 14:07 Standbydb.kdb
  • Add the standby instance’s certificate into the primarydb’s key database.

On primary execute the command below:

$ /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 4 14:09 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: Setting 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

SSL primary

On standby:

$ db2 get dbm cfg | grep SSL

SSL standby

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

DB2 standby

On primary:

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

DB2 primary

Conclusion

You’ve added a new SSL certificate in the existing DB2 HADR environment. You now have a better understanding of how to recreate a new SSL certificate and keydb–and how to extract and display the digital SSL certificate. In addition, you’re now able to set up the Db2 server for SSL support and reset the HADR.