0

DB2 11.5 running on Windows Server 2016 Standard

SQL Server 14.0.3430.2

I'm able to set up ODBC with some DB2 databases, but when the DB2 database is secured with an SSL certificate, which they call truststore, I can't figure out how to configure it.

Here is the JDBC connection info, which works:

jdbc:db2://someserver.db2.me.com:9999/MYDB:sslConnection=true;sslTrustStoreLocation=C:\Security\security_keyfile.jks;sslTrustStorePassword=mypassword;

When I try to set it up, I issue the following commands on the DB2 command line processor:

DB2 CATALOG TCPIP NODE MYNODE REMOTE someserver.db2.me.com: SERVER 9999

/***** SOME HELP *******
CATALOG [ADMIN] {TCPIP | TCPIP4 | TCPIP6} NODE node-name REMOTE hostname
[SERVER service-name] [SECURITY {SOCKS | SSL}] [REMOTE_INSTANCE instance-name]
[SYSTEM system-name] [OSTYPE os-type] [WITH "comment string"]

Note: SERVER service-name is mandatory for CATALOG {TCPIP | TCPIP4 | TCPIP6}
      NODE and not allowed for CATALOG ADMIN {TCPIP | TCPIP4 | TCPIP6} NODE.
      SECURITY SOCKS is not allowed for CATALOG [ADMIN] TCPIP6 NODE.

***** END HELP ********/

LIST NODE DIRECTORY

CATALOG DB MYDB AT NODE MYNODE

LIST DB DIRECTORY

CONNECT TO MYDB USER myuserid USING mydbpw

It returns:

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

This works with databases secured only with the user name and password, but not with the SSL certificate. Once set up, I can use ODBC manager to add a connection that SQL Server can use.

A second attempt tries this:

UNCATALOG DB MYDB
UNCATALOG NODE MYNODE
DB2 CATALOG TCPIP NODE MYNODE REMOTE someserver.db2.me.com: SERVER 9999 SECURITY SSL
CATALOG DB MYDB AT NODE MYNODE
CONNECT TO MYDB USER myuserid USING mydbpw

I get this:

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

This works with databases secured differently, but not with SSH file. Once setup, I can use ODBC manager to add a connection that SQL Server can use.

BOTTOM LINE: I don't understand the environment enough to know how to proceed, or if this is even possible. How do I configure the SSL connection?

5
  • That's SSL, not SSH. You need to tell the ODBC/CLI driver to use the SSL protocol and where to find the SSL certificate, just as you do with the JDBC driver. See here
    – mustaccio
    Commented Apr 12, 2022 at 0:01
  • Thanks to @Dan, it now says SSL. Again, bottom line, I don't know enough to know. I know I have to tell odbc the path and the password, and that it must use the certificate. I don't know how, and the link you provided does not tell me how to do that. That's my question. HOW? In fact, the link doesn't even mention local ssl certificates that require a password. The words CERTIFICATE, PATH, PASSWORD don't even appear on the page. Do you know of any links that explain how to actually do it, or can you walk me through the steps? I'd very much appreciate it.
    – mightycpa
    Commented Apr 12, 2022 at 13:52
  • Examples are just one click away
    – mustaccio
    Commented Apr 12, 2022 at 18:29
  • We shall see. I only have one key file, and my first read says that this method requires two files, or it will fail. Also, the search for sslTrustStore describes what needs to be done, but it's for java.
    – mightycpa
    Commented Apr 12, 2022 at 19:02
  • Yeah, this doesn't work. In one of the Windows ODBC driver screens, there is a tab where you can add CLI parameters, and the great thing about that is that all of the current CLI options are listed. There is no such thing as truststore on that list. This is strictly a JDBC thing. The other SSL option requires two files, the stash and the key. The DBA's haven't given me two files, only one.
    – mightycpa
    Commented Apr 19, 2022 at 14:08

1 Answer 1

-1

In addition to installing the DB2 product, you also need to install the IBM Global Security Kit (GSKit) on your system.

Since you have the certificate in the form of a .jks file, you can install Java and export the keys using Java keytool commands.

Then you will be able to import your exported keys into your own DB2 keystore file using the IBM GSKit commands. You will be able to create the stash file containing the password to the keystore file when creating it.

Here is a beautiful presenatation from TRIDEX 2021 which describes the fundamentals of using TLS encryption and certificates with DB2, and describes many useful GSKit commands to get the job done:

Light at the end of the Encrypted Tunnel

The two Database Manager Configuration parameters you can set on your DB2 installation to make it easy to connect with ODBC later are SSL_CLNT_KEYDB and SSL_CLNT_STASH. These are also discussed on page 36 in the presentation.

Documentation for how to update database manager configuration can be found here on IBM's website:

UPDATE DATABASE MANAGER CONFIGURATION command

Once you have your keystore, and provide it to the database manager, you will no longer receive the communication error. The communication error is telling you that you are trying to connect without encryption to a port which requires encryption.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.