Python IBM_DB using SSL connection

Question

I'm using Python on Centos 7 and I have installed GSK8Kit with DB2 11.3 client.

So I set:

IBM_DB_HOME=/path/to/my/db2client/sqllib - ODBC and clidriver

Also I set:

LD_LIBRARY_PATH = $IBM_DB_HOME/lib:$LD_LIBRARY_PATH

Then I installed ibm_db:

pip install ibm_db

I added my db2servercert.arm into mykeydb.kdb file, located /opt/IBM/db2/GSK8KitStore and I'm using the same version of GSK8Kit on client and server.

gsk8capicmd_64 -cert -add -db mykeydb.kdb -stashed -label "DB2 Server self-signed certificate" -file db2servercert.arm -format ascii -trust enable

According to this IBM docs: https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.admin.sec.doc/doc/t0053518.html

From Db2 V10.5 FP5 onwards, the SSLClientKeystoredb and SSLClientKeystash keywords are not needed in the connection string, db2cli.ini file, FileDSN, or db2dsdriver.cfg file. If you have not set or passed values for the SSLClientKeystoreddb and SSLClientKeystash keywords, the CLI/ODBC client driver will create a default key database internally during the first SSL connection. The Client driver will call GSKit API's to create a key database populated with the default root certificates.

Now I'm trying to create ibm_db connection string for db2 SSL connection using various scenarios:

1. Security=ssl and SSLServerCertificate=/path/to/my/db2servercert.arm "Database=sampledb;Protocol=tcpip;Hostname=myhost;Servicename=50001;Security=ssl;SSLServerCertificate=/path/to/my/db2servercert.arm;"
2. SECURITY=SSL and SSLClientKeystoredb=/opt/IBM/db2/GSK8KitStore/mykeydb.kdb and SSLClientKeystash=/opt/IBM/db2/GSK8KitStore/mystashfile.sth "Database=sampledb;Protocol=tcpip;Hostname=myhost;Servicename=50001;Security=ssl;SSLClientKeystoredb=/opt/IBM/db2/GSK8KitStore/mykeydb.kdb;SSLClientKeystash=/opt/IBM/db2/GSK8KitStore/mystashfile.sth;"

3. Security=ssl

   "Database=sampledb;Protocol=tcpip;Hostname=myhost;Servicename=50001;Security=ssl;"

In 1) and 2) I was able to connect without any SSL error connections, but in 3) I'm getting Socket 414 error:

[IBM][CLI Driver] SQL30081N A communication error has been detected. Communication protocol being used: "SSL". 
Communication API being used: "SOCKETS". Location where the error was detected: "". 
Communication function detecting the error: "sqlccSSLSocketSetup". Protocol specific error code(s): "414", "", "". SQLSTATE=08001

That means: https://www.ibm.com/support/knowledgecenter/en/SSAL2T_7.1.0/com.ibm.cics.tx.doc/reference/r_gskit_error_codes.html,

414 error: GSK_ERROR_BAD_CERT - Incorrectly formatted certificate received from partner.

Note: on another machine with the same config and ibm_db installed this connection string works (I'm sure I missed smth) "Database=sampledb;Protocol=tcpip;Hostname=myhost;Servicename=50001;Security=ssl;"

My questions are:

1. Which env variables or db2 client parameters I have to configure to connect only with Security=ssl property?
2. How does ibm_db work under the hood, when trying to connect to db2 remote server and where I can find this root certificate based on which it automatically generate its own keydb.kdb file as mentioned in IBM docs?

Thx for any idea ;)

Answer 1

If you're using a self-signed SSL certificate, you can't connect without using options 1 or 2.

In option 1 you're supplying the certificate's public key directly, to allow the Db2 client to validate the Db2 server. This is already using the "in memory keystore" that you're asking about in question #2.

In option 2, you would have imported the same public key into your keystore to allow the Db2 client to validate the server.

If you want to connect using only Security=SSL, your Db2 server's SSL certificate needs to come from one of the CAs already in the system keystore.

Answer 2

I believe that when the Db2-documentation writes "The Client driver will call GSKit API's to create a key database populated with the default root certificates", it means that the dynamically created kdb will contain the certs for some common commercial CAs, and (if specified) will also contain the cert specified by SSLServerCertificate.

As you are using a self-signed certificate, the CA certs will be ignored in this case.

If you are connecting to a Db2-server that runs on Linux/Unix/Windows, using IBM's drivers, and want an encrypted connection that uses the target Db2-instance public-key as part of the encryption, then you must tell the Db2-client the location of that certificate (which contains the Db2-instance public key) in one way or another.

For a linux client, thay cert will either be in a statically created kdb (via GSKit commands), or in a dynamically created kdb as specified by using the SSLServerCertificate property. For a Db2-client running on Microsoft Windows the certificate can additionally be fetched from the MS keystore if Db2-client is configured to use that.

The source code for ibm_db module is available on github. However, the client-side SSL work happens not in ibm_db module but instead happens in the (closed source) Db2-driver along with (closed source) libraries for GSKit. To see some of what's happening under the covers you can trace the CLI driver. Refer to the Db2-documentation online for details of CLI tracing.