0

While trying to connect to Db2 Warehouse Local from clpplus as below giving an error

  ````
  clpplus -nw db2inst1@WP
  ````

jcc][t4][2030][11211][4.24.92] A communication error occurred during operations on the connection's underlying socket, socket input stream, or socket output stream. Error location: Reply.fill() - socketInputStream.read (-1). Message: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target. ERRORCODE=-4499, SQLSTATE=08001

My DB is SSL enabled and I have downloaded IBM data server client on Mac and created db2dsdriver.cfg as below

 db2cli writecfg add -database BLUDB -host db2inst1.zc.com -port 50001 

 db2cli writecfg add -dsn WP -database BLUDB -host db2inst1.zc.com -port 50001

 db2cli writecfg add -database BLUDB -host db2inst1.zc.com -port 50001 -parameter "SecurityTransportMode=SSL" `

Generally when connecting from dbvizualizer we use the below db connection url

BLUDB:sslConnection=true;sslTrustStoreLocation=/Users/Documents/truststore/ibm-truststore.jks;sslTrustStorePassword=<>;

I even tried to create the file as below but the same error persists

db2cli writecfg add -database BLUDB -host db2inst1.zc.com -port 50001 

db2cli writecfg add -dsn WP -database BLUDB -host db2inst1.zc.com -port 50001

db2cli writecfg add -database BLUDB -host db2inst1.zc.com -port 50001 -parameter "SecurityTransportMode=SSL" 

db2cli writecfg add -database BLUDB -host db2inst1.zc.com -port 50001 -parameter "sslTrustStoreLocation=/Users/Documents/truststore/ibm-truststore.jks" 

 db2cli writecfg add -database BLUDB -host db2inst1.zc.com -port 50001 -parameter "sslTrustStorePassword=<>"

Actually, I'm trying to connect to Db2 Warehouse using ibm_db in a Juypter Notebook but for this, the DB should be cataloged and I'm unable to use the notebook because of this issue and also on mac db2 client is not supported and hence I have to go with data server client. So I need help in achieving this

data_henrik
  • 16,724
  • 2
  • 28
  • 49
svdb2
  • 1
  • 1
  • How is the SSL certificate managed? Does your connection have access to it? clpplus is based on JDBC, ibm_db in Jupyter likely is Python and based on ODBC. – data_henrik Jun 12 '19 at 10:32
  • Hope you realise that clpplus is a JAVA application, while the ibm_db add-in for python (for juypter notebook) does not use java but instead it is a CLI application. The JVM and the CLI cannot use the same truststore. With current versions of IBM data server driver (higher than v10.5 fp5) you don't need a statically created keystore if you use the additional connection attribute to indicate the fully qualified path+filename of the server certificate. – mao Jun 12 '19 at 11:16
  • For CLI applications, export the server certificate to ARM format, and use an additional command line for db2dsdriver configuration on the workstation after copying the ARM file to the workstation `db2cli writecfg add -dsn ${DSN_NAME} -host ${DB2_SERVER_HOSTNAME} -port ${DB2_SERVER_PORTNUMBER} -parameter "SSLServerCertificate=${YOUR_ARM_FILE_FULLY_QUALIFIED_NAME}"` – mao Jun 12 '19 at 11:35
  • does a `db2cli validate -dsn alias -connect -user userid -passwd password` test work? https://www.ibm.com/support/knowledgecenter/en/SS6NHC/com.ibm.swg.im.dashdb.doc/connecting/connect_connecting_cli_and_odbc_applications.html – Paul Vernon Jun 12 '19 at 16:49
  • @data_henrik I have downloaded IBM internal CA root certificate and IBM intenal intermediate CA certificate and created a Java truststore and import the above certifacates into the new created store. keytool -importcert -alias IBMCA -trustcacerts -file carootcert.der -keystore ibm-truststore.jks keytool -importcert -alias IBMCAintermediate -trustcacerts -file caintermediatecert.der -keystore ibm-truststore.jks – svdb2 Jun 13 '19 at 05:23
  • hi @mao. I tried with the command you have provided db2cli writecfg add -database BLUDB -host db2inst1.zc.com -port 50001 -parameter "SSLServerCertificate=/Applications/dsdriver/cfg/DigiCertGlobalRootCA.arm. Even then it is failing with same error. – svdb2 Jun 13 '19 at 05:24
  • @PaulVernon this test is also failed with same error db2cli validate -dsn alias -connect -user userid -passwd password – svdb2 Jun 13 '19 at 05:25
  • Please help me in connecting to db2 warehouse using ssl with dsd driver – svdb2 Jun 13 '19 at 05:26
  • Stepping back: Do you want to connect from the notebook or from clpplus? You don't need to catalog the db to connect from a notebook – data_henrik Jun 14 '19 at 07:26

1 Answers1

1

To not have everything as comment, let's start composing an answer....

If you want to connect to Db2 Warehouse using clpplus and using SSL, there were changes starting in one of the latest versions of Db2 Warehouse. You would need to set up an IBM data server driver configuration file like this:

<configuration>
  <dsncollection>
    <dsn alias="SSLAMPLE" name="SAMPLE" host="9.121.221.159" port="50001">
    </dsn>
  </dsncollection>
  <databases>
    <database name="SAMPLE" host="9.121.221.159" port="50001">
      <parameter name="SecurityTransportMode" value="SSL"/>
    </database>
  </databases>
</configuration>

The above configures an alias SSLAMPLE for the database SAMPLE. With clpplus you would then connect to SSLAMPLE.

data_henrik
  • 16,724
  • 2
  • 28
  • 49
  • Thanks for the reply. I have the similar config in my db2dsddriver.cfg file – svdb2 Jun 14 '19 at 05:06
  • And i tried to connect using clpplus resulting in the below error again clpplus -nw db2inst1@WP [jcc][t4][2030][11211][4.24.92] A communication error occurred during operations on the connection's underlying socket, socket input stream, or socket output stream. Error location: Reply.fill() - socketInputStream.read (-1). Message: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target. ERRORCODE=-4499, SQLSTATE=08001 – svdb2 Jun 14 '19 at 05:09