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) ;
}
}
}


IBM API Connect - Now a Happy Bunny - Was Notworking, Now Networking

As per the past few posts, I've been learning my way around IBM API Connect, and had it nearly working - until about 15 minutes ago when it ALL started working.

Without getting too deep into the What and How, the TL;DR; is this …..

I have three VMs; the API Manager, the Developer Portal and the DataPower Gateway.


I'd moved DataPower onto a different Mac, as it requires 8 GB RAM.

I'd sensibly configured that VM to use Bridged, rather than NAT, networking, in order that the API Manager ( APIM ) could "see" DataPower.

However, I'd left APIM and the Portal using NAT networking on the main Mac, for no other reason that laziness.

That was where things went wrong.

Whilst I could happily add the DataPower Service and Service into the APIM configuration, using the Cloud Management Console, I was hitting a 404 whenever I tried to test my APIs, even though the Endpoint was correctly configured: -


So here's the thing ….

Whilst it's crucial that APIM can see DataPower, it's also quite important that DataPower can see APIM :-)

One thing that gave me a clue was this UI: -


specifically the Information icon.

This NOW shows: -


but had previously reported the Management Load Balancing Addresses as (a) being the old NAT address of APIM itself and (b) as down.

Once I switched to Bridged, and sorted out my IP addresses, making sure that I was using addresses in the same subnet, to avoid the need for messy routing …. things suddenly became better.

I did sketch out the networking topology a few months back : -


so I really should've known better.

Bottom line, whilst APIM needs to "see" DataPower, DataPower also needs "see" APIM

If it helps, this is also a rather useful introduction to the networking requirements: -

Firewall requirements

IBM API Connect - "Unexpected token ILLEGAL"

So I'm continuing my voyage of discovery with IBM API Connect, and had followed this tutorial to create a simple API using Loopback: -


Annoyingly, when I came to test my API, I was seeing this: -


and, via Debug, this: -


This is the Gateway Script that I have: -

Can you see what I can see ?

Yeah, I'm at home to Cap'n Copy Paste :-(

I fixed my code: -

saved the API and republished the product.

Now we have this: -


which is far far better.

So, for the record, the copy/paste from Firefox to Safari had introduced some glitches with regard to the apostrophes ( single quotes ).

This has bitten me before on Mac, but I'm stupid and forgot :-(

For the record, the DataPower logs also showed similar: -



IBM API Connect - Webhooks not configured

I'm learning my way around IBM API Connect at present, which is giving me a great opportunity to also learn IBM DataPower Gateway, which is nice.

Whilst trying to determine why I'm unable to test my APIs on the DataPower Gateway from within the API Manager, I was looking at some of the underlying Developer Portal commands, including: -

list_sites

which returned: -

5995c388e4b0d25e2a2ee8f2.5995c388e4b0d25e2a2ee8fe => portal.uk.ibm.com/davehay/sb (INSTALLED) <!> Webhooks not configured

and: -

status

System version: 7.x-5.0.7.2-20170629-1629
Distribution version: 7.x-5.0.7.2-20170628-2109

Free disk space: 16G
   Set Hostname: OK
     DNS Server: Reachable (192.168.153.2)
   APIC SSH Key: OK

Configuration:
  APIC Hostname: management.uk.ibm.com
  APIC IP: 192.168.153.130
  Devportal Hostname: portal.uk.ibm.com
  Devportal IP: 192.168.153.132
  APIC Certificate Status (Default): ERROR - The certificate that was presented by the remote was self signed, but with the expectation that it would be CA signed. 
                                             Please ensure that there is a valid CA signed certificate being used by the APIM servers and ensure any intermediate public certificates are presented or are in the trust store.

Node is standalone

Site web check: All sites OK

Site services:
         Webhooks: Some DOWN <!> (portal.uk.ibm.com/davehay/sb)
  Background sync: All sites Up

Services:
  Queue                      is Up
  Database   [Mysql]         is Up (Standalone)
  Web Server [Nginx]         is Up
  PHP Pool   [Php5-fpm]      is Up
  Inetd      [Openbsd-inetd] is Up
  REST       [Restservice]   is Up

FAILURE: Some services are DOWN.


which was less than impressive.

Thankfully the Knowledge Centre saved me: -


which said, in part: -


So I ran the command: -

download_apim_cert

which returned: -

depth=0 C = US, ST = NY, L = Armonk, O = IBM Corporation, CN = API Connect
verify error:num=18:self signed certificate
verify return:1
depth=0 C = US, ST = NY, L = Armonk, O = IBM Corporation, CN = API Connect
verify return:1
DONE
WARNING: The common name provided in the certificate, "Connect", does not match the configured IBM APIC server: "management.uk.ibm.com".  The developer portal will be configured to trust the provided certificate without checking that the server name matches the common name.
Setting user provided https certificate for portal.uk.ibm.com/davehay/sb


I then re-ran the status command: -

status

System version: 7.x-5.0.7.2-20170629-1629
Distribution version: 7.x-5.0.7.2-20170628-2109

Free disk space: 16G
   Set Hostname: OK
     DNS Server: Reachable (192.168.153.2)
   APIC SSH Key: OK

Configuration:
  APIC Hostname: management.uk.ibm.com
  APIC IP: 192.168.153.130
  Devportal Hostname: portal.uk.ibm.com
  Devportal IP: 192.168.153.132
  APIC Certificate Status (Mismatch): WARNING - Only suitable for development and PoC purposes.

Node is standalone

Site web check: All sites OK

Site services:
         Webhooks: All sites Up
  Background sync: All sites Up

Services:
  Queue                      is Up
  Database   [Mysql]         is Up (Standalone)
  Web Server [Nginx]         is Up
  PHP Pool   [Php5-fpm]      is Up
  Inetd      [Openbsd-inetd] is Up
  REST       [Restservice]   is Up

SUCCESS: All services are Up.


and the list_sites command: -

list_sites

5995c388e4b0d25e2a2ee8f2.5995c388e4b0d25e2a2ee8fe => portal.uk.ibm.com/davehay/sb (INSTALLED)

So that's all good then ….

IBM DataPower Playground - on IBM Bluemix

I found this serendipitously whilst looking for something completely different: -

Welcome to the IBM DataPower Playground.

The IBM DataPower Playground is an interactive website that lets you write GatewayScript code and execute it on a cloud hosted DataPower Gateway for learning purposes.

GatewayScript is a new transformation technology for API, Web and Mobile and is available in DataPower as a Processing Policy Action starting from firmware version 7.0. For more information see the documentation.

To get started click on the Samples tab.


For the record, I'm tinkering with DataPower in multiple contexts at present, both DataPower on Docker ( see previous posts ) and DataPower with IBM API Connect ( see previous posts ).

So that's all good then ...

Thursday, 17 August 2017

IBM API Connect - Adding IBM DataPower Gateway into the mix - SOMA import failed

I've been tinkering with an IBM API Connect 5.0.7.2 implementation on my Mac, running all three VMs; the API Manager, the Developer Portal AND the DataPower Gateway on the same box.

However, I saw this: -


when attempting to integrate APIC and IDG.

I dumped out the APIC CMC logs: -

debug postmortem export sftp 10.10.2.3 user fred file /tmp/foobar.tar.gz

and, on the Mac, exploded the resulting tarball, giving me this: -

log/cmc.out:2017-08-17 13:19:30.076 SEVERE [T-83] [com.ibm.datapower.gwapi.impl.GatewaysImpl.deployFilesAndConfig] Deploy: APIMgmt_9A8A3AFACB/gw1: failed: IOException:SOMA import failed
log/cmc.out:Throwable occurred: java.io.IOException: SOMA import failed
log/cmc.out: at com.ibm.datapower.gwapi.soma.Manager.importConfig(Manager.java:657)
log/cmc.out: at com.ibm.datapower.gwapi.soma.Manager.importConfig(Manager.java:625)
log/cmc.out: at com.ibm.datapower.gwapi.soma.Manager.importConfig(Manager.java:621)
log/cmc.out:2017-08-17 13:19:30.084 SEVERE [T-83] [com.ibm.apimgmt.exception.APIGenericException$Serializer.serialize] Error(599597e2e4b0d25e2a2ee700), Http-Code(500), Message(SOMA import failed), User(cmc/admin), Path(post:/gatewayClusters/599426b4e4b04960a3daf1dd/servers/59959798e4b0d25e2a2ee6fa)


As a quick test, I'm moving the IDG VM from the main Mac to another Mac, in order to spread the load, leaving the Manager and Portal VMs on the primary box.

We'll see ……

*UPDATE*

Yes, as I suspected, once I moved the IDG VM to another box, and increased it's RAM from 4 GB to 8 GB, all is now well.


Now to test my API ....

Wednesday, 16 August 2017

What's the opposite of "cat" ? Another in the "I did not know that" series

Thanks to a friend, Paul Smith, for sharing this.

He had a requirement - to take a list of items ( a text file ) and invert the list.

Initially I thought he meant that he wanted to sort the list, which seemed simple.

Here's a list: -

cat list.txt 

Cat
Rabbit
Dog
Gerbil
Hamster
Horse
Rhino
Hippo
Alosaur


so here's the sorted list: -

sort list.txt 

Alosaur
Cat
Dog
Gerbil
Hamster
Hippo
Horse
Rabbit
Rhino


and here's the reverse sort: -

sort -r list.txt 

Rhino
Rabbit
Horse
Hippo
Hamster
Gerbil
Dog
Cat
Alosaur


but NO, I didn't understand his requirement - he wanted the list reversed BUT not sorted.

Introducing …. TAC

Why tac ? 'cos it's the reverse of cat  :-)

Unix and puns, what's not to like

So here's the list, reversed BUT NOT sorted: -

tac list.txt 

Alosaur
Hippo
Rhino
Horse
Hamster
Gerbil
Dog
Rabbit
Cat