Windows PC - Python - ibm-db 3.20 - sqlachemy: how to setup a connection with SSL

Question

Windows PC - Python - ibm-db 3.20 - sqlachemy: how to setup a connection with SSL

SandorSzalma1 opened this issue a year ago · 9 comments

Operating System Name: Windows 10
db2level output from clidriver if in use: 11.5.40000
Target Db2 Server Version: 11.5.x
Python Version: 3.11.5
ibm_db version: 3.2.0

I would like to setup a connection to a IBM DB2 database on the mainframe with SSL but it is not working yet.
The same connection without SSL is already working.

Error message:
ibm_db_dbi::OperationalError: [IBM][CLI Driver] 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", "*", "*". SQLSTATE=08001 SQLCODE=-30081

Test script to reproduce the problem.

stage = "xxx_se"

stage_dict = {
    "xxx_se": {"database": "Pxx", "hostname": "Sxx", "port": "4xxx", "username": "Hxxx",  "options": "security=ssl;sslservercertificate=C:/Users/Cxxx/.conda/envs/db2/Lib/site-packages/certs/sxx_certs.pem"},    
    }
 
# db_engine parameters
database = stage_dict[stage]["database"]
hostname = stage_dict[stage]["hostname"]
port     = stage_dict[stage]["port"]
username = stage_dict[stage]["username"]
options  = stage_dict[stage]["options"]

connect_str = f"db2+ibm_db://{username}:{quote(password)}@{hostname}:{port}/{database}?{options};"

The host Sxx has a self signed certificate with an intermediate issuer:
certificate <- intermediate issuer <- root issuer

In the file sxx_certs.pem I have used the two issuer certificates.

Error 414 is documented here.

I have found certificates at two places:

C:\Users\Cxxx.conda\envs\db2\Lib\site-packages\certs\ibm_certs.pem
C:\Users\Cxxx.conda\envs\db2\Lib\site-packages\clidriver\cfg\DigiCertGlobalRootCA.arm

I am wondering whether it is possible to use them. (I would add my certificates to the file).

What do you think, how we could make this SSL connection working?
Thanks in advance for your ideas!

Answer 1 · 2023-11-20T08:54:10.000Z

Hello @SandorSzalma1
ssl connection using ibm_db_sa, you need to give the connection string as
engine = create_engine(
'ibm_db_sa://username:password@host:portNumber/database?SECURITY=SSL'
';SSLSERVERCERTIFICATE=C:\\Users\\Cxxx.conda\\envs\\db2\\Lib\\site-packages\\clidriver\\cfg\\DigiCertGlobalRootCA.arm')
conn = engine.connect()

ssl connection using ibm_db, you need to give the connection as
conn = ibm_db.connect("DATABASE=<DATABASE_NAME>;HOSTNAME=;PORT=<SSL_PORT>;SECURITY=SSL;SSLServerCertificate=<FULL_PATH_TO_SERVER_CERTIFICATE>;UID=<USER_ID>;PWD=",'','')

Also you have give certificate path in options as
"options": "security=ssl;sslservercertificate=C:/Users/Cxxx/.conda/envs/db2/Lib/site-packages/certs/sxx_certs.pem"}
and showed that the path is C:\Users\Cxxx.conda\envs\db2\Lib\site-packages\clidriver\cfg\DigiCertGlobalRootCA.arm
the path may be incorrect at Cxxx/.conda , Please make sure that you are giving correct path.

I'm able to connect using ssl with correct certificate, I also tried with wrong certificate and getting the above similar error as you are getting.
I will suggest you to use latest and correct certificate.

Thank you

Answer 2 · 2023-11-20T14:42:49.000Z

Hello @bchoudhary6415 ,
thank you for your follow up! It is good to hear that the SSL-connection is working, we only need to make it working for us, too!

We use self-signed certificates and I put the two issuer certificates in this file:
C:/Users/Cxxx/.conda/envs/db2/Lib/site-packages/certs/sxx_certs.pem
C:/Users/Cxxx/.conda/envs/db2/Lib/site-packages/clidriver/cfg/DigiCertGlobalRootCA.arm is useless for us.

I would say that I have the right file:

openssl s_client -showcerts -connect Sxx:4801 -CAfile sxx_certs.pem </dev/null
---
SSL handshake has read 2327 bytes and written 446 bytes
Verification: OK
---

I am not sure whether UPPERCASE or lowercase is important, but I have changed the connection string:

from sqlalchemy import create_engine

stage = "xxx_se"
security = "SECURITY=SSL;SSLServerCertificate=C:/Users/xxx/.conda/envs/db2/Lib/site-packages/certs/sxx_certs.pem"

stage_dict = {
    "prod_se": {"database": "Pxxx", "hostname": "Sxxx", "port": "4801", "username": "Hxxx",  "options": security },    
    }
 
# db_engine parameters
database = stage_dict[stage]["database"]
hostname = stage_dict[stage]["hostname"]
port     = stage_dict[stage]["port"]
username = stage_dict[stage]["username"]
options  = stage_dict[stage]["options"]

connect_str = f"db2+ibm_db://{username}:{quote(password)}@{hostname}:{port}/{database}?{options};"
db_engine = create_engine(connect_str, echo=True)
db_engine.connect()

I still have the same issue...

Do you have some other ideas?

Thank you!

Answer 3 · 2023-11-20T15:33:17.000Z

@SandorSzalma1 Update your code as below, i.e. use \\ in certificate file path instead of / on Windows and put file path under double quote:

security = 'SECURITY=SSL;SSLServerCertificate="C:\\Users\\xxx\\.conda\\envs\\db2\\Lib\\site-packages\\certs\\sxx_certs.pem";'

You can print final connect_str too in your script to see what uri is getting passed to create_engine().
Thanks.

Answer 4 · 2023-11-20T15:53:38.000Z

@bimalkjha
Thank you for your update!
I have updated the security option and added the connect string, the error message is still the same.

security = "SECURITY=SSL;SSLServerCertificate=C:\\Users\\xxx\\.conda\\envs\\db2\\Lib\\site-packages\\certs\\sxx_certs.pem"`

print(connect_str)

connect_str = "db2+ibm_db://Hxx:pwd@Sxxx:4801/Pxxx?SECURITY=SSL;SSLServerCertificate=C:\Users\xxx\.conda\envs\db2\Lib\site-packages\certs\sxx_certs.pem;"

Error message:

    conn = ibm_db.connect(dsn, '', '', conn_options)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Exception: [IBM][CLI Driver] 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", "*", "*".  SQLSTATE=08001  SQLCODE=-30081

Answer 5 · 2023-11-20T20:26:23.000Z

You are getting Protocol specific error code(s): "414". 414 means:

414 - GSK_ERROR_BAD_CERT - Incorrectly formatted certificate received from partner.

It looks issue with certificate file. If you have transferred it from non windows OS, make sure to transfer it as text file to avoid any kind of corruption.
You mentioned you have multiple certificates in .pem file. The correct certificate may not be the first one? In that case clidriver may cause issue. Is it possible to get the *.arm file as certificate and try? Thanks.

Answer 6 · 2023-11-21T08:19:11.000Z

@bimalkjha
Thank you for your update!

The certificate file sxx_certs.pem (with our intermediate, and root issuer certs) works with openssl.

openssl s_client -showcerts -connect Sxx:4801 -CAfile sxx_certs.pem </dev/null
---
SSL handshake has read 2327 bytes and written 446 bytes
Verification: OK
---

I have tried to test the db connection with only one issuer certificate. It is the same error.

Possible issues according to this:

You are using self-signed certificates and the certificate is missing.
We are using self-signed certificates, I have them all.
The certificate that is being used is from a local certificate authority that does not have the Basic Constraints extension active.
It has this extension active:
X509v3 Basic Constraints: critical
CA:TRUE
You are using CA-signed certificates, and the CA root certificate is missing.
No, it is not missing.
The client does not support Server Name Indication (SNI), and the server environment requires SNI to route TLS connections to the correct endpoint. Upgrade the client to Db2 11.5.7 or later if the server relies on SNI.
We use client to Db2 11.5.4, I ask our Db2 experts about it.
One of the certificates in the chain is expired.
No, they are not expired.

Answer 7 · 2023-11-21T11:13:34.000Z

@SandorSzalma1 To rule out any issue with existing clidriver, I would suggest to download https://public.dhe.ibm.com/ibmdl/export/pub/software/data/db2/drivers/odbc_cli/ntx64_odbc_cli.zip and unzip it. Then replace C:/Users/Cxxx/.conda/envs/db2/Lib/site-packages/clidriver with the downloaded one and test connection.
If still connection is failing, then run below commands from windows command prompt:

set PATH=C:/Users/Cxxx/.conda/envs/db2/Lib/site-packages/clidriver/bin;%PATH%
set LIB=C:/Users/Cxxx/.conda/envs/db2/Lib/site-packages/clidriver/bin;C:/Users/Cxxx/.conda/envs/db2/Lib/site-packages/clidriver/lib;%LIB%
db2cli writecfg add -dsn ssldsn -database dbname -host hostnmae -port sslport -parameters "userid=db2user;password=db2passwd;SecurityTransportMode=SSL;SSLServerCertificate=C:\\Users\\xxx\\.conda\\envs\\db2\\Lib\\site-packages\\certs\\sxx_certs.pem;"
db2trc on -f 1.trc
db2cli validate -dsn ssldsn -connect
db2trc off
db2trc flw 1.trc 1.flw
db2trc fmt 1.trc 1.fmt
db2trc fmt -cli 1.trc 1.cli

Update database connection info in db2cli writecfg command before running it. zip all generated 1.* files along with complete output of validate command and share here. We need complete output of validate command, not partial. You can mask password and other connection info if you want. Thanks.

Answer 8 · 2023-11-21T11:42:38.000Z

@bimalkjha
Thank you for your update!

With the Db2 client version 11.5.8 (move from version 11.5.4) my connection is working:

2023-11-21 12:39:30,220 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-11-21 12:39:30,221 INFO sqlalchemy.engine.Engine SELECT * FROM SYSIBM.SYSDUMMY1
2023-11-21 12:39:30,223 INFO sqlalchemy.engine.Engine [dialect ibm_db_sa+ibm_db_sa does not support caching 0.00248s] ()
[('Y',)]

Answer 9 · 2023-11-21T13:05:55.000Z

Glad to hear about it! Thanks.