« Back to home

JDBC connections over TLS with IBM DB2 11.1

Posted on

With GDPR around the corner, lots of organizations are suddenly developing an increased interest in encryption. In particular, I found myself asked to ensure that all of our database access was performed over encrypted channels.

No problem, I thought, DB2 is bound to support TLS connections. And indeed it does, but the setup process turns out to be a bit involved. While I was changing things, I decided to upgrade to DB2 11 and implement pooled JDBC connections.

The server side

The official documentation on setting up TLS for DB2 11 does a pretty good job of detailing the server-side setup, and I found some useful user notes, but I’ll go through the steps here, assuming a self-signed certificate and a Linux server.

First, I created a directory under ~db2inst1 to hold the keys and certificates, and changed to that directory. Next, I had to add the memorably-named gsk8capicmd_64 to my path. Then I created a key store:

# su db2inst1
$ cd ~
$ mkdir pki
$ cd pki
$ export PATH="$PATH:/opt/ibm/db2/V11.1/gskit/bin"
$ gsk8capicmd_64 -keydb -create -db server.kdb -pw 'gandalf' -stash

The command creates a key store in a .kdb file. The -stash parameter instructs the program to stash the password in an sth file. (Don’t use gandalf as a password, obviously.)

Next, I created the key and certificate. Unlike a web TLS certificate, you don’t need to specify the DNS name of the server as the common name, so you can use a proper hierarchical X.509 name as per RFC 1422:

gsk8capicmd_64 -cert -create -db server.kdb -pw 'gandalf' -label 'db2server' -dn 'CN=dbserver/OU=My Department/O=Big Blue' -size 2048 -sigalg SHA256_WITH_RSA

At the time of writing, RSA and SHA256 is still considered a secure combination with 2048 bit keys. The label is arbitrary, and also doesn’t need to match the server’s DNS name; it’s used as a unique key for the certificate within the key store.

Now that the key and certificate are in the key store database, the next step is to extract the certificate, because we’ll need it later:

$ gsk8capicmd_64 -cert -extract -db server.kdb -pw 'gandalf' -label 'db2server' -target server.arm -format ascii -fips

The extracted server.arm file is what you need on the client. You don’t need a client key store for DB2 11.1. That’s true whether you’re using the DB2 client or a JDBC driver. Nor do you need to install GSSKit or any other add-on to get TLS support.

First, though, we need to configure the DB2 server to use the key store we just created. There are three configuration variables to change – one to set the key store location; one to set the stash file location; and one to tell DB2 the label we chose earlier to indicate our certificate. After connecting to the database:

$ db2 update dbm cfg using SSL_SVR_KEYDB /home/db2inst1/pki/server.kdb
DB20000I  The UPDATE DATABASE MANAGER CONFIGURATION command completed successfully.
SQL1362W  …
$ db2 update dbm cfg using SSL_SVR_STASH /home/db2inst1/pki/server.sth
DB20000I  The UPDATE DATABASE MANAGER CONFIGURATION command completed successfully.
SQL1362W  …
$ db2 update dbm cfg using SSL_SVR_LABEL db2server
DB20000I  The UPDATE DATABASE MANAGER CONFIGURATION command completed successfully.
SQL1362W  …

I’ve elided the SQL1362W warning messages, because you can ignore them.

Next, you need to open up a port for your DB2 TLS connections, and define it in /etc/services. Since the existing entry is 50000 db2c_db2inst1, it seemed like 50001 db2cs_db2inst1 was the obvious value.

Opening the firewall port will depend on what Linux distribution and firewall you’re using, but I’m assuming you can handle it. You only need the port open for TCP.

With the port open, the next step is to tell DB2 to use the new service we’ve defined, and to flip the switch to enable SSL.

$ db2 update dbm cfg using SSL_SVCENAME db2cs_db2inst1
DB20000I  The UPDATE DATABASE MANAGER CONFIGURATION command completed successfully.
SQL1362W  …
$ db2set -i db2inst1 DB2COMM=TCPIP,SSL

If you set DB2COMM to SSL, you’ll only be able to make encrypted connections. I decided that for now I’d allow both, in case the encrypted connections didn’t work or I needed to compare behavior in the two cases.

Finally, a db2stop and db2start should enable all the new configuration. If you got it all right, DB2 will now be listening on the new port:

# lsof -i :50001
COMMAND   PID     USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
db2sysc 23269 db2inst1    9u  IPv4 174819      0t0  TCP *:db2cs_db2inst1 (LISTEN)

The last thing you might want to do is check the encrypted connection is negotiated correctly. You can do that with OpenSSL:

% openssl s_client -showcerts -connect dbserver.example.com:50001 < /dev/null
CONNECTED(00000003)
depth=0 CN = dbserver/OU=My Department/O=Big Blue
verify error:num=18:self signed certificate
verify return:1
depth=0 CN = dbserver/OU=My Department/O=Big Blue
verify return:1
---

OpenSSL will then dump the entire certificate chain in great detail, and you can confirm that the X.509 name matches what you specified when you created the certificate.

At this point, the server is set up correctly, and it’s on to the client.

The client side

As a developer, my main concern is JDBC connections. All my data pumping integration tasks use JDBC, as do most of the tools I use. The only time I’m likely to use the DB2 command line client itself is for database maintenance tasks.

Unfortunately, the documentation on how to set up random JDBC connections to DB2 V11.1 to use TLS seems to be slim-to-nonexistent. After various failed attempts using instructions found online, I decided to write some Java code. That way I could set connection parameters in code and easily dump full error traces, rather than having to rely on unhelpful GUI client messages.

I made my code set up a connection pool, fetch a connection from it, and perform a test query. Here’s what it looks like:

  private static void testDB2SSL (final String login, final String password) {
    try {
      DB2ConnectionPoolDataSource dbds = new DB2ConnectionPoolDataSource();
      dbds.setDriverType(4);
      dbds.setDatabaseName("WAREHOUSEDB");
      dbds.setDescription("My data warehouse");
      dbds.setUser(login);
      dbds.setPassword(password);
      dbds.setServerName("dbserver.example.com");
      dbds.setPortNumber(50001);
      dbds.setSslCertLocation("/home/myid/server.arm");
      dbds.setSslConnection(true);
      System.out.println("Connecting…");
      try (Connection conn = dbds.getPooledConnection().getConnection()) {
        System.out.println("Creating statement…");
        try (Statement stmt = conn.createStatement()) {
          System.out.println("Executing query…");
          try (java.sql.ResultSet rs = stmt
              .executeQuery("SELECT FIRSTNAME, LASTNAME FROM SAMPLE.PEOPLE FETCH FIRST 10 ROWS ONLY")) {
            System.out.println("Examining results…");
            while (rs.next()) {
              String fn = rs.getString(1);
              String ln = rs.getString(2);
              System.out.println(fn + " " + ln);
            }
          }
        }
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

A few things to note here. First, I use setDriverType to make sure DB2 loads the Type IV driver, which is pure Java. This means I can bundle the necessary jar files into my application before deployment, and not need to worry about a DB2 client install on whatever machine the application is going to run on.

The setServerName value is where you specify the DNS name of the server, or its IP address if you must. Then I specify the path of the .arm certificate file created while setting up the server, and set the flag to turn on SSL. That’s all I need to do! No client key database, no password, no stash files.

Once I had the Java code debugged, I could then work backwards from that to work out the JDBC URL corresponding to what I’d done in Java. The DB2 JDBC URL syntax is

jdbc:db2://servername:port//database:flag1=value1;flag2=value2;

Note the trailing semicolon. The DB2DataSource properties all have corresponding URL flags; if the method is setPropName, the URL flag is propName, and it appears to be case-sensitive. I could now work out the JDBC URL I needed:

jdbc:db2://dbserver.example.com:50001/DATABASE:sslConnection=true;sslCertLocation=/home/myid/server.arm;

I’ve wrapped it for display here, but the real thing has no newlines or spaces in it. And it works! I can connect via TLS from Squirrel SQL and other handy open source tools.

As far as the DB2 CLI goes, you should just need to add/change a couple of parameters on the database configuration, for example:

db2cli writecfg add -database DATABASE -host dbserver.example.com port 50001 -parameter "SecurityTransportMode=SSL;SSLServerCertificate=/home/myid/server.arm;"

The last glitch

There was one more thing bothering me: It was taking 15-20 seconds to open a connection to DB2. That wasn’t reasonable. Some web searches pointed me in the direction of DNS as a cause – it seems that DB2 tries to perform a reverse DNS lookup for every incoming connection.

I have a solution to that: PowerDNS recursor. I added it to the server’s Puppet configuration and applied the changes. It seemed to improve things a little, but not to the point I’d consider normal.

It was time for more debugging, so I installed bind-utils (and its dependencies) to get the dig utility. Suddenly, the server began responding quickly. I don’t think it’s a coincidence, so I assume that DB2 has some sort of undocumented dependency on bind-utils that the db2prereqcheck utility doesn’t pick up. Not entirely surprising, as db2prereqcheck also fails to note a bunch of missing X11 dependencies. So, a thing to try if you have similar problems.

And that’s about it. I have TLS encrypted connections from JDBC clients.