Connecting to IBM DB2 using CLI and SSL

0
votes

I was using JDBC to connect to IBM DB2 AS400 for iSeries in my Rails Application. Now it looks like I will have to move to use the ibm_db gem.

https://github.com/ibmdb/ruby-ibmdb

I attempted this with irb first and used the following in a command like:

require 'ibm_db'
conn=IBM_DB.connect("DATABASE=ABCUATDT;hostname=TEST.HERE.COM;PORT=9471;SECURITY=SSL;PROTOCOL=TCPIP;AUTHENTICATION=SERVER;UID=username;PWD=abc1234;", "", "")

This gives me the following error:

SQL30081N A communication error has been detected. Communication protocol being used: \"SSL\". Communication API being used: \"SOCKETS\". Location where the error was detected: \"\". Communication function detecting the error: \"sqlccSSLSocketSetup\". Protocol specific error code(s): \"414\"

I then try without SSL:

conn=IBM_DB.connect("DATABASE=ABCUATDT;hostname=TEST.HERE.COM;PORT=446;PROTOCOL=TCPIP;AUTHENTICATION=SERVER;UID=username;PWD=abc1234;", "", "")

SQL30082N Security processing failed with reason \"15\" (\"PROCESSING FAILURE\"). SQLSTATE=08001 SQLCODE=-30082"

These ports and username/pw etc all work fine with JDBC, but not using the ibm_db gem.

EDIT: I tried using different ports, I know that 8471 is open for non-SSL and 9471 is open for SSL. I decided to skip SSL altogether and I started using the db2cli on my Mac OSX.

db2cli execsql -connstring "database=ABCUATDT;hostname=TEST.HERE.COM;port=8471;uid=username;pwd=abcd1234"

This just hangs and doesn't give me a prompt to write SQL:

IBM DATABASE 2 Interactive CLI Sample Program (C) COPYRIGHT International Business Machines Corp. 1993,1996 All Rights Reserved Licensed Materials - Property of IBM US Government Users Restricted Rights - Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp.

I then tried the python library AND the DB2 CLI - both of which gave the same errors. Is there something needed to be changed at the DB2 side?

Why would it work for JDBC but not for the IBM ODBC driver?

1
db2odbcpyodbc
The jdbc driver has no shared code with the CLI driver. and the SSL keystores are different for each. It's unclear from your question if or how you've configured your keystore (for CLI) or how you are providing the cert(s). Error code 414 is "Certificate is not valid". – mao
I am not providing any certs or keystore. I didn't see this mentioned in the docs anywhere. I have no access to the DB2 database other than the server name and user id and pw. Can you not simply authenticate with a username and password? – user3437721
Verify your Db2 client software is the latest-version from the vendor (and latest fixes of that version). Your question asked about SSL (i.e. encrypted connections), and the encryption key depends on at least one certficate - though how the certificate(s) gets used varies with client software/vendor/versions. The configuration of the Db2-server determines the authentication methods allowed at your site. – mao
I downloaded the IBM db2cli and tried WITHOUT SSL. The connection just hangs, I think its something at the server side – user3437721
I updated the question, you have to realise that the DB2 server is a black box to me. I have no idea what version the as400 is, some told me DB2/400 V7R3 v7.3 - but I don't know how to confirm. Is there any SQL I can run using JDBC to get this info, since JDBC works. this is iseries for as400 – user3437721

1 Answers

0
votes

Speak with your i administrator(s) or management team. Stackoverflow may not be not what you need right now. When asking questions, it's smart to fully detail your environment for obvious reasons, if necessary speaking with relevant persons at the client site to establish the facts before asking questions.

Find out whether there is a Db2-connect server (separate purchase) already installed on the i-series. The regular Db2-client for MAC can then communicate with CLI/ODBC via that product which does the required protocol conversions while acting as a gateway.

Find out if there is a Db2-connect server already deployed anywhere that runs on Windows/Linux/Unix that you can use (it does the required protocol conversions while acting as a gateway) once configured with connections for i-series. The regular Db2-client for MAC OS X can then use that as a gateway for CLI/ODBC to i-series.

IBM does not currently sell "IBM i access" for MAC OS X afaik, which offers the CLI/ODBC interface and does not require a Db2-connect server. That product is available for Microsoft Windows and Linux platforms. Those platforms may offer you an alternative if MAC OS X is currently unsupported.

The regular Db2-driver from IBM for MAC OS X requires for CLI/ODBC a Db2-connect server for the protocol conversions with i-series, so db2cli tool will not work without a Db2-connect gateway.

Other non IBM vendors may offer solutions in this area.

Another possibility is to use a ODBC to JDBC bridge, running on your MAC OS X, which would reroute ODBC calls to your working jdbc driver. You might research that.