Friday 18 August 2017

Java to DB2 via TLS 1.2 - A new one on me

So I've been helping a friend debug and resolve a problem whereby his Java 7 code wasn't connecting to a DB2 database via a TLS 1.2 connection.

To validate this, I setup a DB2 instance to support TLS 1.2: -

As root

echo "db2c_ssl 60007/tcp" >> /etc/services

As db2inst1

/home/db2inst1/sqllib/gskit/bin/gsk8capicmd_64 -keydb -create -db /home/db2inst1/keystore.kdb -pw passw0rd -stash

/home/db2inst1/sqllib/gskit/bin/gsk8capicmd_64 -cert -create -db /home/db2inst1/keystore.kdb -pw passw0rd -label "odm.uk.ibm.com" -dn "cn=odm.uk.ibm.com,dc=uk,dc=ibm,dc=com" 

db2 update dbm cfg using SSL_SVR_KEYDB /home/db2inst1/keystore.kdb
db2 update dbm cfg using SSL_SVR_STASH /home/db2inst1/keystore.sth
db2 update dbm cfg using SSL_SVR_LABEL odm.uk.ibm.com
db2 update dbm cfg using SSL_SVCENAME db2c_ssl
db2 update dbm cfg using SSL_VERSIONS TLSV12
db2 update dbm cfg using SSL_CIPHERSPECS TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384,TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
db2set DB2COMM=TCPIP,SSL
db2stop
db2start


and then validated what the WAS box was seeing: -

openssl s_client -connect odm.uk.ibm.com:60007 </dev/null

New, TLSv1/SSLv3, Cipher is AES256-GCM-SHA384
Server public key is 1024 bit
Secure Renegotiation IS supported
Compression: NONE
Expansion: NONE
SSL-Session:
    Protocol  : TLSv1.2
    Cipher    : AES256-GCM-SHA384


Having relieved the DB2 self-signed certificate: -

openssl s_client -showcerts -connect odm.uk.ibm.com:60007 </dev/null | openssl x509 -outform DER > ~/db2.cer

and created a JKS trust store: -

/opt/ibm/WebSphere/AppServer/java/jre/bin/keytool -import -file ~/db2.cer -keystore ~/davehay.jks -alias DB2 -storepass davehay

I then tested my Java code ( see below ) : -

java -cp /opt/ibm/WebSphere/AppServer/ODMjdbcdrivers/DB2/db2jcc.jar:/home/wasadmin DB2connect_ssl2

Instead of working, this generated a trace file ( as per the code ): -

view /tmp/foobar.trc_sds_1 

which contained: -

...
[jcc] BEGIN TRACE_DIAGNOSTICS
[jcc][Thread:main][SQLException@7807b259] java.sql.SQLException
[jcc][Thread:main][SQLException@7807b259] SQL state  = 08001
[jcc][Thread:main][SQLException@7807b259] Error code = -4499
[jcc][Thread:main][SQLException@7807b259] Message    = [jcc][t4][2030][11211][3.61.65] A communication error occurred during operations on the connection's underlying socket, socket input stream,
or socket output stream.  Error location: T4Agent.sendRequest() - flush (-2).  Message: Received fatal alert: handshake_failure. ERRORCODE=-4499, SQLSTATE=08001
[jcc][Thread:main][SQLException@7807b259] Stack trace follows
com.ibm.db2.jcc.am.DisconnectNonTransientConnectionException: [jcc][t4][2030][11211][3.61.65] A communication error occurred during operations on the connection's underlying socket, socket input stream, 
or socket output stream.  Error location: T4Agent.sendRequest() - flush (-2).  Message: Received fatal alert: handshake_failure. ERRORCODE=-4499, SQLSTATE=08001
        at com.ibm.db2.jcc.am.ed.a(ed.java:319)

...

I turned on debugging: -

java -cp /opt/ibm/WebSphere/AppServer/ODMjdbcdrivers/DB2/db2jcc.jar:/home/wasadmin -Djavax.net.debug=ssl DB2connect_ssl2

which told me what I already knew: -

main, WRITE: TLSv1 Handshake, length = 123
main, READ: TLSv1.2 Alert, length = 2
main, RECV TLSv1 ALERT:  fatal, handshake_failure
main, called closeSocket()
main, handling exception: javax.net.ssl.SSLHandshakeException: Received fatal alert: handshake_failure

Similarly, DB2 told me that things were awry: -

db2diag -f

which returned: -

2017-08-18-19.27.52.683893+060 I48627542E482         LEVEL: Error
PID     : 128279               TID : 140419925010176 PROC : db2sysc 0
INSTANCE: db2inst1             NODE : 000
APPHDL  : 0-24873
HOSTNAME: odm.uk.ibm.com
EDUID   : 23                   EDUNAME: db2agent () 0
FUNCTION: DB2 UDB, common communication, sqlccMapSSLErrorToDB2Error, probe:30
MESSAGE : DIA3604E The SSL function "gsk_secure_soc_init" failed with the 
          return code "402" in "sqlccSSLSocketSetup".


I've seen this before: -



However, this time around the solution was … different.

I experimented with various tweaks to the Java command-line: -

java -cp /opt/ibm/WebSphere/AppServer/ODMjdbcdrivers/DB2/db2jcc.jar:/home/wasadmin -Dcom.ibm.jsse2.overrideDefaultProtocol=TLSv12 DB2connect_ssl2

and: -

java -cp /opt/ibm/WebSphere/AppServer/ODMjdbcdrivers/DB2/db2jcc.jar:/home/wasadmin -Dcom.ibm.jsse2.overrideDefaultProtocol=TLSv12 -Dhttps.cipherSuites=TLS_RSA_WITH_AES_256_GCM_SHA384 DB2connect_ssl2

etc. but to no avail.

I'd also verified that my JRE was set to support the stronger export-grade ciphers: -

java -cp /home/wasadmin/ CipherTest

PASSED: Max AES key length OK! - >= 256 (2147483647).

At which point, I was scratching my head ….

… and then I read this: -


which said, in part, this: -

...
3. Identify your JCC driver version by issuing the following command:
java -cp db2jcc4.jar com.ibm.db2.jcc.DB2Jcc -version

4. Determine if you need to update your JCC driver.

• If you use Java 8 and an IBM JDK, the minimum recommended level is 4.18.
• Otherwise, update to version 4.22.29 or higher. This is the version that shipped with DB2 v11.1 FP1.
You can download a new JCC driver here. If you deploy your application in a Bluemix environment, either remove the JCC driver ( db2jcc4.jar) in the application's WebContent/WEB-INF/lib/ folder or else ensure that the driver in this folder is up to date before you build and deploy the application. 
...

Given that I'm using DB2 11.1, as per this: -

db2level 

DB21085I  This instance or install (instance name, where applicable: 
"db2inst1") uses "64" bits and DB2 code release "SQL11012" with level 
identifier "0203010F".
Informational tokens are "DB2 v11.1.2.2", "s1706091900", "DYN1701310100AMD64", 
and Fix Pack "2".
Product is installed at "/opt/ibm/db2/V11.1".

I checked the version of the JDBC driver that I was using ( this was shipped with IBM ODM Rules 8.9, which is what I happen to using for my tests, even though this is ONLY Java to DB2 testing ): -

java -cp /opt/ibm/WebSphere/AppServer/ODMjdbcdrivers/DB2/db2jcc.jar com.ibm.db2.jcc.DB2Jcc -version

IBM DB2 JDBC Universal Driver Architecture 3.61.65

java -cp /opt/ibm/WebSphere/AppServer/ODMjdbcdrivers/DB2/db2jcc4.jar com.ibm.db2.jcc.DB2Jcc -version

IBM Data Server Driver for JDBC and SQLJ 4.11.69

which is below the recommended version 4.22.29.

Therefore, I checked the versions that ship with DB2 itself: -

java -cp /opt/ibm/db2/V11.1/java/db2jcc.jar com.ibm.db2.jcc.DB2Jcc -version

IBM DB2 JDBC Universal Driver Architecture 3.72.30

java -cp /opt/ibm/db2/V11.1/java/db2jcc4.jar com.ibm.db2.jcc.DB2Jcc -version

IBM Data Server Driver for JDBC and SQLJ 4.23.42

so I tested my Java class using this updated driver: -

java -cp /opt/ibm/db2/V11.1/java/db2jcc.jar:/home/wasadmin DB2connect_ssl2

and it returned: -

 Connected to database with type 4 url
000010 CHRISTINE
000020 MICHAEL
000030 SALLY
000050 JOHN
000060 IRVING
000070 EVA
000090 EILEEN
000100 THEODORE
000110 VINCENZO
000120 SEAN
000130 DELORES
000140 HEATHER
000150 BRUCE
000160 ELIZABETH
000170 MASATOSHI
000180 MARILYN
000190 JAMES
000200 DAVID
000210 WILLIAM
000220 JENNIFER
000230 JAMES
000240 SALVATORE
000250 DANIEL
000260 SYBIL
000270 MARIA
000280 ETHEL
000290 JOHN
000300 PHILIP
000310 MAUDE
000320 RAMLAL
000330 WING
000340 JASON
200010 DIAN
200120 GREG
200140 KIM
200170 KIYOSHI
200220 REBA
200240 ROBERT
200280 EILEEN
200310 MICHELLE
200330 HELENA
200340 ROY


The moral of the story - the version of the JDBC driver MAY well be more important than first I had realised.

If you need an updated driver, please go here: -


Final point, here's the Java code that I'm using: -

DB2connect_ssl2.java

import java.lang.*;
import java.sql.*;
import java.io.*;

class DB2connect_ssl2
{
public static void main(String argv[])
{
try
{
Connection con = null;
PreparedStatement pstmt = null;

try
{
Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
con = DriverManager.getConnection( "jdbc:db2://odm.uk.ibm.com:60007/SAMPLE" +
              ":user=db2inst1;password=passw0rd;" +
              "sslConnection=true;sslTrustStoreLocation=/home/wasadmin/davehay.jks;sslTrustStorePassword=davehay;" +
              "keepAliveTimeout=10;" +
      "traceDirectory=/tmp;" +
      "traceFile=foobar.trc;" +
      "traceFileAppend=false;" +
      "traceLevel=-1;" );
System.out.println(" Connected to database with type 4 url") ;
}
catch (Throwable e)
{
System.out.println("Connect failed" + e);
}

try
{
pstmt = con.prepareStatement("select * from DB2INST1.EMP");
}
catch (Throwable e)
{
System.out.println("Statement Prepare failed" + e);
}

try
{
ResultSet s = pstmt.executeQuery();

        while (s.next())
{
String v_id = s.getString(1);
String v_stream_id = s.getString(2);
System.out.println( v_id + " " + v_stream_id );
}
s.close();
}
catch (Throwable e)
{
System.out.println("fetch processing failed");
}
con.close();
}
catch (Exception e)
{
System.out.println("Connect failed" + e);
}
}
}


and here's another one: -

JdbcTestDB2.java
 
import java.sql.Connection ;
import java.sql.DriverManager ;
import java.sql.ResultSet ;
import java.sql.Statement ;
import java.sql.SQLException;

import org.omg.CORBA.VersionSpecHelper;

class JdbcTestDB2
{
public static void main (String args[])
{
try
{
Class.forName("com.ibm.db2.jcc.DB2Driver");
}
catch (ClassNotFoundException e)
{
System.err.println (e) ;
System.exit (-1) ;
}
String hostname = "odm.uk.ibm.com";
int port = 60007;
String dbName = "SAMPLE";
String userName = "db2inst1";
String password = "passw0rd";
String sslConnection = "true";

java.util.Properties properties = new java.util.Properties();
properties.put("user",userName);
properties.put("password", password);
properties.put("sslConnection", sslConnection);
properties.put("sslTrustStoreLocation","/home/wasadmin/davehay.jks");
properties.put("sslTrustStorePassword","davehay");

String url = "jdbc:db2://" + hostname + ":" + port + "/" + dbName;
try
{
Connection connection = DriverManager.getConnection(url,properties);

String query = "select EMPNO,FIRSTNME,LASTNAME from DB2INST1.EMPLOYEE" ;

Statement statement = connection.createStatement () ;
ResultSet rs = statement.executeQuery (query) ;

while ( rs.next () )
System.out.println (rs.getString (1) + " " + rs.getString(2) + " " + rs.getString(3)) ;
connection.close () ;
}
catch (java.sql.SQLException e)
{
System.err.println (e) ;
System.exit (-1) ;
}
}
}


10 comments:

Prashant More said...

Hi Dave,

Thanks for ur efforts, i ll check with updated db jars and keep u updated accordingly.

Its weekend now expect some delay in response. Hv a good day

Prashant More said...

Hi Dave,

I hv DB2 v10.5.700.375 and also changed db2 jars from LIB with proper version(4.19.49) mentioned in ur blog link.
But still facing same issue. Even tried by using latest db2 driver jar with version 4.23.42 but no luck on that front also.

i hv followed http://www-01.ibm.com/support/docview.wss?uid=swg21993801 link to configure DB2 for TLS12

SSL_SVR_KEYDB=C:\db2SSL\mydbserver.kdb
SSL_SVR_STASH=C:\db2SSL\mydbserver.sth
SSL_SVR_LABEL=dbmyselfsigned
SSL_SVCENAME=db2c_DB2_ssl
SSL_CIPHERSPECS= TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384
SSL_VERSIONS=TLSV12

On WAS profile configuration level i used this link for TLS1.2 config
https://jazz.net/help-dev/clm/index.jsp?topic=%2Fcom.ibm.jazz.install.doc%2Ftopics%2Ft_enable_tls1.2_was.html.

Refered following link to Signer certificates retrived from port and under Datasource added custom property sslConnection=true
http://www-01.ibm.com/support/docview.wss?uid=swg21667093



>>>when i try to use openssl to see cipher specs following err came. not the output mentioned like ur command

\openssl-0.9.8h-1-bin\bin>openssl s_client -showcerts -connect localhost:60006
Loading 'screen' into random state - done
CONNECTED(000001A0)
13356:error:140770FC:SSL routines:SSL23_GET_SERVER_HELLO:unknown protocol:./ssl/s23_clnt.c:585:

and in db2diag -f following msg got
2017-08-21-16.09.40.708000+330 I15963551F486 LEVEL: Error
PID : 12960 TID : 9436 PROC : db2syscs.exe
INSTANCE: DB2 NODE : 000
APPHDL : 0-150
HOSTNAME: DL-4RX3LC2
EDUID : 9436 EDUNAME: db2agent () 0
FUNCTION: DB2 UDB, common communication, sqlccMapSSLErrorToDB2Error, probe:30
MESSAGE : DIA3604E The SSL function "gsk_secure_soc_init" failed with the
return code "402" in "sqlccSSLSocketSetup".


If u provide ur mail i can send details logs.

Dave Hay said...

Hi Prashant

Thanks for the feedback.

With specific regard to the openssl command, that MAY only indicate that the OS from where you're running the command doesn't support TLS 1.2. I see that problem with macOS, as per this post - https://portal2portal.blogspot.co.uk/2016/12/openssl-tripped-and-fell-on-macos.html - whereas Red Hat Enterprise Linux 6 and 7 both include a later level of the openssl client code.

The symptoms that you're reporting are a 100% mirror for what I saw on Friday, which led me to update the JDBC drivers.

You could experiment by switching to a different, weaker cipher, in order to see whether that is blocking the handshake.

From where are you running your Java code ? I was running it on RHEL using IBM Java7. What JRE are you using ?

Finally, have you yet raised a PMR with IBM Support ? If not, I'd definitely recommend that approach.

Cheers, Dave

Prashant More said...

Hi Dave,

Appreciated your quick reply.

For our local system we create WAS profile and configure datasource . In eclipse we add our project in WAS server and start server which will normally start on configured ports.

My local env is as follows,
WIndows 7, WAS 8.5.5.8(IBM java_1.7_64), DB2 10.5.7 .

Surprisingly following code direcly run as java application in eclipse works normally with prooper server hello message and key exchange,

package com.ibm.ejs.container;

import java.security.Security;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

import javax.net.ssl.SSLContext;

public class TestTLSDB2Connection {
static String isSSLEnabled= "true";
static String driver ="com.ibm.db2.jcc.DB2Driver";
static String url = "jdbc:db2://localhost:60006/DBOQ26";
static String user = "devuser";
static String password = "Password1";

public static void main(String sa[]){
Connection conn = null;
try {

Class.forName(driver);

// Security.setProperty("ssl.SocketFactory.provider", "com.ibm.jsse2.SSLSocketFactoryImpl");
// Security.setProperty("ssl.ServerSocketFactory.provider", "com.ibm.jsse2.SSLServerSocketFactoryImpl");
// System.setProperty("javax.net.ssl.trustStoreType", "JKS");
// System.setProperty("javax.net.ssl.trustStore", "C:/db2SSL/keystore");
// System.setProperty("javax.net.ssl.trustStorePassword","passw0rd");


Properties props = new Properties();
props.setProperty("user", user);
props.setProperty("password", password);

System.err.println("security.realm.ConnectionFactory.newInstance.........");

if ((isSSLEnabled != null) && ("true".equalsIgnoreCase(isSSLEnabled))) {
props.setProperty("sslConnection", "true");
props.setProperty("sslTrustStoreLocation","C:/db2SSL/keystore");
props.setProperty("sslTrustStorePassword","passw0rd");

// SSLContext sslContext = SSLContext.getInstance("TLSv1.2");
// sslContext.init(null, null, null);
// SSLContext.setDefault(sslContext);


System.err.println("security.realm.ConnectionFactory.........SSL ON");
} else {
System.err.println("security.realm.ConnectionFactory.........SSL OFF:"+ isSSLEnabled);
}

conn = DriverManager.getConnection(url, props);
System.err.println("Connection :"+conn);

String query = "select * from TESTTABLE" ;

Statement statement = conn.createStatement () ;
ResultSet rs = statement.executeQuery (query) ;
System.err.println(">>>Data");
while ( rs.next () )
System.err.println (rs.getString (1) + " " + rs.getString(2) ) ;

conn.close () ;


}
catch (Exception e){
e.printStackTrace();
}


}
}

I have never raised PMR request , can u pls give me URL where i can log this issue.

Dave Hay said...

Hi Prashant

Sure, here you go: -

http://www-01.ibm.com/support/docview.wss?uid=swg21593214

Note that you'll need to log in with an IBM ID that's associated with the organisation that has purchased the WAS and/or DB2 licenses, typically via IBM Passport Advantage.

If you're an IBM Business Partner or Independent Software Vendor, then there will be someone in your organisation who has a similar level of access.

Cheers, Dave

Prashant More said...

Hi Dave,

On Windows is there any tool like open SSL to check hv we configured TLS1.2 correctly on DB2?

Dave Hay said...

Hi Prashant

Yeah, there a few mentioned here - https://wiki.openssl.org/index.php/Binaries

Good luck :-)

Prashant More said...

Hi Dave,

Thanks for ur valuable time for all those comments,

i was able to resolve TLS 1.2 handshake issue by following way,

Basically on local env earlier i created self signed certificate using following DB2 bin command,

gsk8capicmd_64 -cert -create -db "mydbserver.kdb" -pw "passw0rd" -label "dbmyselfsigned" -dn "CN=db.test.lfin.internal,O=test,OU=test,L=test,ST=test,C=US"

after further reading and reiterating found out above certificate genrated with with default KeySize=1024 and with SHA1 signature alogrithm which is supposidly vulernable
and i read that you shud use 256,384,512 SHA alg.

so i created other certificate using follwing command

gsk8capicmd_64 -cert -create -db "mydbserver.kdb" -pw "passw0rd" -label "dbmyselfsigned" -dn "CN=db.test.lfin.internal,O=test,OU=test,L=test,ST=test,C=US" -size 2048 -sigalg SHA256withRSA

and extracted certificate using follwing command(usual step)
gsk8capicmd -cert -extract -db "mydbserver.kdb" -pw "passw0rd" -label "dbmyselfsigned" -target "mydbserver.arm" -format ascii -fips

and created keystore from .arm file using keytool command
keytool -import -file mydbserver.arm -keystore keystore

and provided this keystore and pwd before ssl connection and it worked with proper server hello and key exchanges steps in debug logs.


Best Regards,
Prashant

Prashant More said...

Hi Dave,

If u know anything abt following error ur comments will be helpful to me,

Currently i hv strictly enabled TLS1.2 on WAS and DB2, and this error repeat continuously in server startup logs

i m getting following error,
[8/24/17 16:27:18:209 IST] 00000048 SSLHandshakeE E SSLC0008E: Unable to initialize SSL connection. Unauthorized access was denied or security settings have expired. Exception is javax.net.ssl.SSLHandshakeException: Client requested protocol TLSv1 not enabled or not supported
at com.ibm.jsse2.ab.B(ab.java:421)
at com.ibm.jsse2.nc.b(nc.java:423)
at com.ibm.jsse2.nc.c(nc.java:42)
at com.ibm.jsse2.nc.wrap(nc.java:457)
at javax.net.ssl.SSLEngine.wrap(SSLEngine.java:39)
at com.ibm.ws.ssl.channel.impl.SSLUtils.handleHandshake(SSLUtils.java:747)
at com.ibm.ws.ssl.channel.impl.SSLConnectionLink.readyInbound(SSLConnectionLink.java:566)
at com.ibm.ws.ssl.channel.impl.SSLConnectionLink.ready(SSLConnectionLink.java:295)
at com.ibm.ws.tcp.channel.impl.NewConnectionInitialReadCallback.sendToDiscriminators(NewConnectionInitialReadCallback.java:214)
at com.ibm.ws.tcp.channel.impl.NewConnectionInitialReadCallback.complete(NewConnectionInitialReadCallback.java:113)
at com.ibm.ws.tcp.channel.impl.AioReadCompletionListener.futureCompleted(AioReadCompletionListener.java:175)
at com.ibm.io.async.AbstractAsyncFuture.invokeCallback(AbstractAsyncFuture.java:217)
at com.ibm.io.async.AsyncChannelFuture.fireCompletionActions(AsyncChannelFuture.java:161)
at com.ibm.io.async.AsyncFuture.completed(AsyncFuture.java:138)
at com.ibm.io.async.ResultHandler.complete(ResultHandler.java:204)
at com.ibm.io.async.ResultHandler.runEventProcessingLoop(ResultHandler.java:775)
at com.ibm.io.async.ResultHandler$2.run(ResultHandler.java:905)
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:1881)
Caused by: javax.net.ssl.SSLHandshakeException: Client requested protocol TLSv1 not enabled or not supported
at com.ibm.jsse2.j.a(j.java:9)
at com.ibm.jsse2.nc.a(nc.java:556)
at com.ibm.jsse2.ab.a(ab.java:133)
at com.ibm.jsse2.ab.a(ab.java:304)
at com.ibm.jsse2.cb.a(cb.java:611)
at com.ibm.jsse2.cb.a(cb.java:244)
at com.ibm.jsse2.ab.t(ab.java:74)
at com.ibm.jsse2.ab$1.a(ab$1.java:2)
at com.ibm.jsse2.ab$1.run(ab$1.java:3)
at java.security.AccessController.doPrivileged(AccessController.java:456)
at com.ibm.jsse2.ab$c_.run(ab$c_.java:4)
at com.ibm.ws.ssl.channel.impl.SSLUtils.handleHandshake(SSLUtils.java:834)

Dave Hay said...

Hi Prashant

Glad you've made progress.

The most recent exception implies that something is attempting to make a client connection INTO WAS from an external source, using an unsupported protocol ( TLS v1.0 ) whereas you've probably got WAS locked down to only support TLS v1.2.

You can enable SSL tracing in WAS ( check the Must Gather ) to get further intel.

In addition, remember that any client tooling such as the WSAdmin client ( which uses SOAP ) will also need to be configured to use TLS 1.2 ( which requires soap.client.props and ssl.client.props ) to be updated.

Final consideration; remember that you may have clients making connections using browsers that don't support TLS v1.2 e.g. older versions of Internet Explorer etc.

Cheers, Dave

Visual Studio Code - Wow 🙀

Why did I not know that I can merely hit [cmd] [p]  to bring up a search box allowing me to search my project e.g. a repo cloned from GitHub...