IBM i DB2 JDBC Encryption using SSL

Question

Remote server is an IBM i (7.1) with DB2 installed on it. I am trying to connect to this remote db2 database on IBM i machine via JDBC encrypted link using SSL from my windows machine, I am using jt400-6.7.jar. I can see that SSL is correctly configured on IBM i machine as I see the following in Digital Certificate Manager :

Current Certificate Store
You have selected to work with the certificate store listed below. The left frame is being refreshed to show the task list for this certificate store. Select a task from the left frame to begin working with this certificate store.

Certificate type:   Server or client
Certificate store:  *SYSTEM
Certificate store path and filename:    
/QIBM/USERDATA/ICSS/CERT/SERVER/DEFAULT.KDB

I followed this link to set up SSL on my IBM i machine : https://isupport.krengeltech.com/rxs/setting_up_ssl/

This is my JDBC program executed from my windows 10 machine:

import java.sql.*;

public class IBMiSSLConnect 
{
    public static void main(String[] args) throws Exception 
    {
          try 
          {
              Class.forName("com.ibm.as400.access.AS400JDBCDriver");
              Connection con = DriverManager.getConnection("jdbc:as400://IBMiMachineIP:5021/DBNAME&secure=true", "USER", "PASSWORD");

          }  
          catch(Exception e) 
          {
              System.out.println(e.getMessage());
          }

    }
}

I get the following error :

[PWS0082] library(s) not added to the library list.

If however I replace url as below (adding system library):

Connection con = DriverManager.getConnection("jdbc:as400://IBMiMachineIP:5021/DBNAME;naming=system;libraries=QSYS;secure=true", "USER", "PASSWORD");

I get the following error instead :

The application requester cannot establish the connection. (sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target)

I have two questions:

Q 1: Does one always needs to add naming and libraries in url for JDBC encryption using SSL ?
something like this:

dbc:as400://someserver;naming=system;libraries=devfiles,prodfiles,sysibm,etc

I am refering to this link :

How can I insert additional libraries to my jdbc/DB2 connection?

Q 2: Should I use secure or sslConnection as url parameter ? that is:

a: jdbc:as400://IBMiMachineIP:5021/DBNAME&secure=true

or

b: jdbc:as400://IBMiMachineIP:5021/DBNAME&sslConnection=true

*Note: I have already made changes to SSL permissions for truststore files default.kdb and default.rdb as mentioned here :

https://isupport.krengeltech.com/rxs/configuring_ssl_permissions/

Answer 1

Q1. No, you do not need to add the naming and libraries properties when using SSL.

Q2. You shouldn't be using the :5021 as part of the URL. It is currently ignored, but may be used in the future. You should be using secure=true to get an SSL connection.

I suspect your problem is that the truststore used by the JVM on the client system does not have the certificate for your Certificate Authority (it looks like you are using a self signed certificate on the server). If you still have problems, turn on SSL trace on the client by using the following when starting java:

-Djavax.net.debug=ssl:handshake:verbose

Note, jt400.jar comes with a jdbcClient, so you can used that to test your connection. Here is an example of connecting using SSL. In this case, the cacerts is the trust store that contains a certificate for the CA that signed the server certificate.

java -Djavax.net.debug=ssl:handshake:verbose -Djavax.net.ssl.trustStore=cacerts -jar jt400.jar 'jdbc:as400:SYSTEM;secure=true' USERID PASSWORD

This will show the SSL negotiation that the JVM is doing.

Answer 2

what @jweberhard said in terms of question, use of library is not required and ssl port is not 5021 is correct, thanks @jweberhard .However I realized in my case particularly I was making one wrong assumption based on prior knowledge .

I have done a similar SSL encrypted connection from windows machine to remote machines with MySQL DB and Postgres DB and in both cases you see in wireshark something like this:

1   source IP   Dest IP TLSv1.2 220 Client Hello
2   Dest IP     source IP TLSv1.2   1140    Server Hello, Certificate, Server Key Exchange, Server Hello Done
3   source IP   Dest IP TLSv1.2 129 Client Key Exchange
4   source IP   Dest IP TLSv1.2 60  Change Cipher Spec
5   source IP   Dest IP TLSv1.2 99  Encrypted Handshake Message 

I was looking for TLSv1.2 protocol usage in wireshark , but however I realized that specially when you are working with DB2 database on IBM i you would still see TCP protocol being used, but the litmus test is if you see :

1: Port 9471 being used for SSL Connection in wireshark ( if it is not secure you will see port 8471 being used instead). Refer this link for port usage for IBM i :

https://www-03.ibm.com/systems/power/software/i/toolbox/faq/ports.html

and

2: QZDASSINIT job being created on your IBM i machine( Use green screen to check your job by using WRKACTJOB command and check for this job , this job is created for SSL connection to your DB2 database , else you would see only QZDASONIT job which is for a non-secure connection.