ferborges
Tags: AIX and UNIX, Linux, Security
Published on July 27, 2018 / Updated on December 12, 2019
Skill Level: Intermediate
In this document, you will find a full step-by-step recipe teaching you How To Setting up Encrypted Connections in DB2 CLI needed by GDPR. The Encrypted Connection learned here is the Secure Sockets Layer (SSL).
1. You should have IBM gskit installed on your system where you intend to install the certificate. By default, DB2 has a gskit installed in db2 installation path.
To verify, you can run the following
Login to db2 server using putty.
Switch to db2 instance and run db2profile
Run “which gsk8capicmd_64”
It will show you the path from where system is using the gskit.
In case you it returns something like. Then you need to adjust the db2profile
which: no gsk8capicmd_64 in (/usr/local/bin:/usr/bin:/bin:/usr/bin/X11:/usr/X11R6/bin:/usr/games:/usr/lib64/jvm/jre/bin:/usr/lib/mit/bin:/usr/lib/mit/sbin:/opt/gnome/bin)
There are some of the environment variables that you need to change in commands according to your environment.
Eg:
db2instance - Replace this with environment DB2 instance.
CN=<Server Host> – Replace it with your server host name.
OU=<Organization Unit> – Replace it with your Organization.
L=<Server Location> – Replace it with Server physical Location.
ST=<Server State> – Replace it with Server state.
C=<Server Country> – Replace it with Server Country.
label – Replace it with label of your choice.
Password – Password of your choice.
1. First verify that you have gskit available in your db2 installation path. (Note you may have standalone gskit installed on your system as well)
2. Go to db2 installation path
cd /home//sqllib/gskit/bin
ls -l
if you have above path you will see gskit commands available there.
3. If you find gskit from step 2, Modify the db2profile to add the following.
### Added to set the GSKIT PATH
PATH=/home//sqllib/gskit/bin:$PATH; export PATH
4. Run db2profile after the changes.
5. Go through step 1 to make sure which gsk8capicmd_64 returns the path.
Note – If you do not find gskit installed on your system you need to install it from IBM Fixed Central here.
Step 1. Login to DB2 Server using putty
Step 2. Create a separate folder structure to protect Signer Certificate, KeyStore and Stash file.
mkdir /home/db2instance/db2_ssl
cd /home/db2instance/db2_ssl
Step 3. Use the gsk8capicmd_64 command to create key database.
gsk8capicmd_64 -keydb -create -db "db2_ssl_keydb.kdb" -pw "" -type cms -stash
Note – You should have 4 new files created in /home//db2_ssl path.
Step 4. Create a Certificate Signing Request (CSR) File
gsk8capicmd_64 -certreq -create -db "db2_ssl_keydb.kdb" -pw "" -label "IBM_CA_signed" -dn "CN=, O=IBM, OU=, L=, ST= , C= " -file db2_ssl_ibmca_certreq.csr -size 2048 -sigalg SHA512WithRSA
You will receive a new file with extension .csr
Step 5. Run command
cat db2_ssl_ibmca_certreq.csr
You will see the request key.
1. Go to IBM CA website
2. Select Server Certificates
3. Select Create Profile
Note – You can use your existing profile if you already have one.
4. Fill in the details – Profile Information
* Label is basically for you to identify your server
5. Fill in the details – Certificate Fields
CN - Your server host name
OU – Fill in your organizational Unit
C - Country where the server is located
L, S – Server Location
Note – You can fill-in the same info that you have used in generating the request file in previous step.
6. Fill in the details – Description
7. Fill in the details – Owner and MAD Address.
dd owner to the certificate, you can add your team member who can backup you and/or project manager. You may have multiple people added as owner.
MAD reference is you server host name or alias. You can search your server in MAD DB site. There is a link beside the input box.
8. Click on Submit. It will create your profile
9. On next screen you will see a button “Request Certificate” click on it.
10. Select copy and paste option. And Paste the whole contents of your CSR file (generated from step Create Certificate KeyStores - Step 5):
11. Select your approving manager.
12. Submit the request.
13. Once you have your approval. Follow the next steps.
Step 1. Once the approval is in place, you will receive an email from service machine.
It will have download links for the IBMRoot and IBMIntermediate certificates.
Download both the certificates (IBMRoot and IBMIntermediate)
Alternately you can download them from IBMCA website -> Certificate tab -> at the bottom of the page.
Step 2. Goto IBM CA website
Select Certificates tab → select Server label from the list.
On the next screen select “CRT File” from Action dropdown and click on arrow “>” to download it.
Now you will have the 3rd file.
Step 3. Now you got the total of 3 files (2 .der files and 1 .crt file)
IBMRoot (carootcert.der), IBMIntermediate (caintermediatecert.der) and cert.crt file
Ftp all 3 files to DB2 server. You need to place them under the folder you have created. As per our eg db2_ssl.
Now you need to install the certificates.
Run the following commands
Step 1. Add the root certificate to KeyStore
gsk8capicmd_64 -cert -add -db "db2_ssl_keydb.kdb" -pw "" -label "IBMCA_Root" -file carootcert.der -format binary -fips
Step 2. Add the Intermediate certificate to KeyStore
gsk8capicmd_64 -cert -add -db "db2_ssl_keydb.kdb" -pw "" -label "IBMCA_Intermediate" -file caintermediatecert.der -format binary -fips
Step 3. Receive the signer certificate to KeyStore
gsk8capicmd_64 -cert -receive -file cert.crt -db db2_ssl_keydb.kdb -pw "" -format binary -default_cert yes
Step 4. Validate
Run the following command to list the certificates.
gsk8capicmd_64 -cert -list -db db2_ssl_keydb.kdb -stashed
You should see 3 certificates in your keystore.
Step 1. Configure the DBM configuration. (for DB2 Server)
db2 update dbm cfg using SSL_SVR_KEYDB /home//db2_ssl/db2_ssl_keydb.kdb
db2 update dbm cfg using SSL_SVR_STASH /home//db2_ssl/db2_ssl_keydb.sth
db2 update dbm cfg using SSL_SVR_LABEL
Note - Only set the Default Certificate Label. System will atomically use the reset of the certificate chain.
Step 2. Recycle the DB Instance.
db2stop
db2start
Here are the steps to configure SSL on Client side:
Step 1. Download the IBM CA Root Certificate (carootcert.der). This is a binary-encoded DER-format certificate. Save it somewhere convenient.
Step2. Create a new folder where you will store all Client SSl files:
cd /home/
mkdir SSL_CLIENT
Step 3. FTP carootcert.der to /home//SSL_CLIENT
Step 4. Use GSKit to create a key database by running the following command:
/home//sqllib/gskit/bin/gsk8capicmd_64 -keydb -create -db "/home//SSL_CLIENT/ibmca.kdb" -pw "" -stash
Step 5. For Linux and Mac users, run the following commands to ensure that all users on the system (including both your own main userid and the DB2 instance id) can read the key database files:
chmod 775 /home//SSL_CLIENT/ibmca.kdb
chmod 775 /home//SSL_CLIENT/ibmca.sth
Step 6. Update your DB2 client environment to look for this key database:
db2 update dbm cfg using SSL_CLNT_KEYDB /home//SSL_CLIENT/ibmca.kdb
db2 update dbm cfg using SSL_CLNT_STASH /home//SSL_CLIENT/ibmca.sth
db2 force application all
db2stop
db2start
Step 7. Import the IBM CA root certificate (as downloaded in step 1) into the GSKit key database:
/home///sqllib/gskit/bin/gsk8capicmd_64 -cert -add -db "/home//SSL_CLIENT/ibmca.kdb" -pw "" -label "IBMRoot" -file "/home//SSL_CLIENT/carootcert.der -format binary
Step 8. Finally, catalog the DB2 connection using SSL. Note that you need to know the secure port number for the database systems you want to connect to securely.
db2 catalog tcpip node <node_name> remote server security ssl
db2 catalog db <db_name> as <db_alias> at node <node_name> authentication SERVER_ENCRYPT
db2 terminate
Step 9. Test the connection as normal:
db2 connect to <db_alias> user <user_id>