search

ibmdb / node-ibm_db

IBM DB2 and IBM Informix bindings for node
MIT License
188 stars 151 forks source link

Unable to connect using TLS. Error - SQL30081N, Protocol Specific Error -414 #969

Closed divakargoel closed 6 months ago

divakargoel commented 6 months ago

Hello,

Kindly help me with this SQL30081N Error, Code 414. The SSL cert was generated outside of gsk however we loaded it into the GSK and then extracted it. We are using the public key. The same cert works well when I connect via DbVisualizer.

I am on Windows -

Edition Windows 10 Enterprise Version 22H2 Installed on ‎2/‎8/‎2023 OS build 19045.3693 Experience Windows Feature Experience Pack 1000.19053.1000.0

I installed DB2 Driver Package -

DB21085I This instance or install (instance name, where applicable: "*") uses "64" bits and DB2 code release "SQL11058" with level identifier "0609010F". Informational tokens are "DB2 v11.5.8000.317", "s2209201700", "DYN2209201700WIN64", and Fix Pack "0". Product is installed at "C:\PROGRA~1\IBM\IBMDAT~1" with DB2 Copy Name "IBMDBCL1".

Installed Python 3.11.6

Followed by ibm_db with option - pip3 install ibm_db --no-binary :all: --no-cache-dir

I am trying to access a remote db2 luw database version v11.5.8 with Python however getting this error-

Error occurred: [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

Python Code -

import os os.add_dll_directory('C:\Program Files\IBM\IBM DATA SERVER DRIVER\bin') import ibm_db

Connection parameters

dbname = "TESTDB" hostname = "127.0.0.1" port = "50409" uid = "awsuser" pwd = "***" security = "SSL" ssl_cert_path = "U:\Documents\testcert.arm"

Connection string

conn_str = ( f"DATABASE={dbname};" f"HOSTNAME={hostname};" f"PORT={port};" f"PROTOCOL=TCPIP;" f"UID={uid};" f"PWD={pwd};" f"SECURITY={security};" f"SSLServerCertificate={ssl_cert_path};" )

try:

Establishing a connection

conn = ibm_db.connect(conn_str, "", "")

# Preparing and executing a query
sql = "SELECT * FROM AWSUSER.EMPLOYEE limit 1"
stmt = ibm_db.exec_immediate(conn, sql)

# Fetching and displaying the rows
row = ibm_db.fetch_assoc(stmt)
while row:
    print(row)
    row = ibm_db.fetch_assoc(stmt)

# Close the statement
ibm_db.free_stmt(stmt)

except Exception as e: print("Error occurred:", e)

finally:

Close the connection

if conn:
    ibm_db.close(conn)

Target DB (db2level)

db2level DB21085I This instance or install (instance name, where applicable: "mmeiclt") uses "64" bits and DB2 code release "SQL11058" with level identifier "0609010F". Informational tokens are "DB2 v11.5.8.0", "s2209201700", "DYN2209201700AMD64", and Fix Pack "0". Product is installed at "/opt/ibm/db2/V11.5.8".

SSL Certificate Details

I am using a certificate which was generated on a AWS Private CA and loaded into the gsk8capicmd_64 keydb. We then extracted the cert from the keydb in pem / arm format (both come out the same)

Error Seen on server side -

2023-12-08-16.58.49.236121-300 I236184068E498 LEVEL: Error PID : 17062 TID : 139375983716096 PROC : db2sysc 0

EDUID : 3652 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 "414" in "sqlccSSLSocketSetup".

2023-12-08-16.58.49.236279-300 I236184567E563 LEVEL: Error PID : 17062 TID : 139375983716096 PROC : db2sysc 0

EDUID : 3652 EDUNAME: db2agent () 0 FUNCTION: DB2 UDB, common communication, sqlcctcpinit, probe:949 MESSAGE : ZRC=0x00000036=54

Other Env Details -

U:>echo %LIb% C:\Program Files\IBM\IBM DATA SERVER DRIVER\LIB;

U:> U:>echo %PATH% C:\WINDOWS\CCM\;C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;C:\WINDOWS\System32\WindowsPowerShell\v1.0\;C:\WINDOWS\System32\OpenSSH\;C:\Program Files (x86)\Webex\Plugins;C:\WINDOWS\system32\config\systemprofile\AppData\Local\Microsoft\WindowsApps;C:\Program Files (x86)\Adaptiva\AdaptivaClient\bin\x32;C:\Program Files (x86)\Adaptiva\AdaptivaClient\bin\x64;C:\Program Files\Microsoft VS Code\bin;C:\Program Files\Git\cmd;C:\Program Files\Git\mingw64\bin;C:\Program Files\Git\usr\bin;C:\Program Files\Amazon\AWSCLIV2\;C:\Program Files (x86)\Citrix\HDX\bin\;C:\Program Files\Citrix\HDX\bin\;C:\PROGRA~1\IBM\IBMDAT~1\BIN;C:\Users\C7T7NK\AppData\Local\Programs\Python\Python311\Scripts\;C:\Users\C7T7NK\AppData\Local\Programs\Python\Python311\;C:\Users\C7T7NK\AppData\Local\Microsoft\WindowsApps;C:\Users\C7T7NK\AppData\Local\Programs\Git\cmd;C:\Users\C7T7NK\AppData\Local\GitHubDesktop\bin

U:> U:>echo %IBM_DB_HOME% C:\Program Files\IBM\IBM DATA SERVER DRIVER

U:> U:>python --version Python 3.11.6

bimalkjha commented 6 months ago

@divakargoel Are you using certificate bundle containing multiple certificates as value of SSLServerCertificate keyword? If yes, you are facing this known issue: https://www.ibm.com/mysupport/s/defect/aCI3p000000XoWWGA0/dt244530

Your error looks similar to the one described in above known issue. I would suggest to uninstall the dsdriver that you installed at C:\PROGRA~1\IBM\IBMDAT~1 and have fresh installation of ibm_db without setting IBM_DB_HOME. It will download the latest clidriver that has fix for this issue under your site_packages directory.

If you have requirement of dsdriver, then download either of below dsdriver special build and install it. Then set system level environment variable IBM_DB_HOME=C:\PROGRA~1\IBM\IBMDAT~1 and then install ibm_db using pip3 install ibm_db --no-binary :all: --no-cache-dir command.

V11.5.9.0 SB_36648 dsdriver: http://www.ibm.com/support/fixcentral/swg/quickorder?parent=ibm~Information%2BManagement&product=ibm/Information+Management/IBM+Data+Server+Client+Packages&release=All&platform=All&function=fixId&fixids=special_36648_401634_DSClients-ntx64-dsdriver-11.5.9000.352-FP000%3A437325519827239552&includeSupersedes=0

V11.5.8.0 SB_36633 dsdriver: http://www.ibm.com/support/fixcentral/swg/quickorder?parent=ibm~Information%2BManagement&product=ibm/Information+Management/IBM+Data+Server+Client+Packages&release=All&platform=All&function=fixId&fixids=special_36633_396815_DSClients-ntx64-dsdriver-11.5.8000.317-FP000%3A898236501496662144&includeSupersedes=0

Thanks.

divakargoel commented 6 months ago

Hi @bimalkjha - Thank you for the response.

Your response helped me identify the gap. I did not had the root certificate in my cert file. I added the root certificate before the db server cert in the cert file and was able to connect successfully.

Thank you!