Using SSL/TLS with an Amazon RDS for Db2 DB instance

Download the JDBC driver. For more information, see DB2 JDBC Driver Versions and Downloadsin the IBM Support documentation.

Create a shell script file with the following content. This script adds all certificates from the bundle to a Java KeyStore.

#!/bin/bash
PEM_FILE=$1
PASSWORD=$2
KEYSTORE=$3
# number of certs in the PEM file
CERTS=$(grep 'END CERTIFICATE' $PEM_FILE| wc -l)
for N in $(seq 0 $(($CERTS - 1))); do
    ALIAS="${PEM_FILE%.*}-$N"
    cat $PEM_FILE |
    awk "n==$N { print }; /END CERTIFICATE/ { n++ }" |
    keytool -noprompt -import -trustcacerts -alias $ALIAS -keystore $KEYSTORE -storepass $PASSWORD
done

To run the shell script and import the PEM file with the certificate bundle into a Java KeyStore, run the following command. Replace shell_file_name.sh with the name of your shell script file and password with the password for your Java KeyStore.

 ./shell_file_name.sh global-bundle.pem password truststore.jks

To connect to your Db2 server, run the following command. Replace the following placeholders in the example with your RDS for Db2 DB instance information.

export trustStorePassword=MyPassword
java -cp ~/dsdriver/jdbc_sqlj_driver/linuxamd64/db2jcc4.jar \
com.ibm.db2.jcc.DB2Jcc -url \
"jdbc:db2://ip_address:port/database_name:\
sslConnection=true;sslTrustStoreLocation=\
~/truststore.jks;\
sslTrustStorePassword=${trustStorePassword};\
sslVersion=TLSv1.2;\
encryptionAlgorithm=2;\
securityMechanism=7;" \
-user master_username -password master_password

Install the node-ibm_db driver. For more information, see Installing the node-ibm_db driver on Linux and UNIX systems in the IBM Db2 documentation.

Create a JavaScript file based on the following content. Replace the following placeholders in the example with your RDS for Db2 DB instance information.

var ibmdb = require("ibm_db");
const hostname = "ip_address";
const username = "master_username";
const password = "master_password";
const database = "database_name";
const port = "port";
const certPath = "/root/qa-bundle.pem";
ibmdb.open("DRIVER={DB2};DATABASE=" + database + ";HOSTNAME=" + hostname + ";UID=" + username + ";PWD=" + password + ";PORT=" + port + ";PROTOCOL=TCPIP;SECURITY=SSL;SSLServerCertificate=" + certPath + ";", function (err, conn){
 if (err) return console.log(err);
 conn.close(function () {
 console.log('done');
 });
});        

To run the JavaScript file, run the following command.

node ssl-test.js

Create a Python file with the following content. Replace the following placeholders in the example with your RDS for Db2 DB instance information.

import click
import ibm_db
import sys

port = port;
master_user_id = "master_username" # Master id used to create your DB instance
master_password = "master_password" # Master password used to create your DB instance
db_name = "database_name" # If not given "db-name'
vpc_customer_private_ip = "ip_address" # Hosts end points - Customer private IP Addressicert_path = "/root/ssl/global-bundle.pem" # cert path

@click.command()        
@click.option("--path", help="certificate path")
def db2_connect(path):

    try:
        conn = ibm_db.connect(f"DATABASE={db_name};HOSTNAME={vpc_customer_private_ip};PORT={port};
            PROTOCOL=TCPIP;UID={master_user_id};PWD={master_password};SECURITY=ssl;SSLServerCertificate={path};", "", "")
        try:
            ibm_db.exec_immediate(conn, 'create table tablename (a int);')
            print("Query executed successfully")
        except Exception as e:
            print(e)
        finally:
            ibm_db.close(conn)
            sys.exit(1)
    except Exception as ex:
        print("Trying to connect...")

if __name__ == "__main__":
    db2_connect()

Create the following shell script, which runs the Python file you created. Replace python_file_name.py with the name of your Python script file.

#!/bin/bash
PEM_FILE=$1
# number of certs in the PEM file
CERTS=$(grep 'END CERTIFICATE' $PEM_FILE| wc -l)

for N in $(seq 0 $(($CERTS - 1))); do
    ALIAS="${PEM_FILE%.*}-$N"
    cert=`cat $PEM_FILE | awk "n==$N { print }; /END CERTIFICATE/ { n++ }"`
    cat $PEM_FILE | awk "n==$N { print }; /END CERTIFICATE/ { n++ }" > $ALIAS.pem
    python3 <python_file_name.py> --path $ALIAS.pem
    output=`echo $?`
    if [ $output == 1 ]; then
        break
    fi
done
            

To import the PEM file with the certificate bundle and run the shell script, run the following command. Replace shell_file_name.sh with the name of your shell script file.

./shell_file_name.sh global-bundle.pem

To connect to your Db2 instance using Db2 CLP, you need GSKit, which you can download at IBM Fix Central. To use Db2 CLP, you also need the IBM Db2 client, which you can download from Download initial Version 11.5 clients and drivers in IBM Support.

Create a keystore.

gsk8capicmd_64 -keydb -create -db "directory/keystore-filename" -pw "changeThisPassword" -type pkcs12 -stash

Import the certificate bundles to the keystore.

gsk8capicmd_64 -cert -import -file global-bundle.pem -target directory/keystore-filename> -target_stashed

Update the Db2 instance configuration.

db2 update dbm cfg using SSL_CLNT_KEYDB keystore-filename SSL_CLNT_STASH keystore stash file immediate

Catalog the node and database.

db2 catalog tcpip node ssluse1 REMOTE endpoint SERVER ssl_svcename security ssl

db2 catalog database testdb as ssltest at node ssluse1

Connect to the database.

db2 connect to ssltest user username using password