python-ibmdb: Protocol specific error code(s): "414", "", "". SQLSTATE=08001 SQLCODE=-30081

Operating System Name: MacOS M1 Chip System (host) (but running the package ibm_db in the podman)
DB2
- Instance Type: Db2 Standalone
- Machine Type: power
- Database Version: 11.5.8
Python Version: 3.9.18 (container)
ibm_db version: ibm-db==3.2.1 (container)
For non-Windows, output of below commands: uname (container: Linux, host: Darwin) uname -m (contaner: x86_64, host: arm64)
Test script to reproduce the problem.
ibmdb2_connect.py

import os
import ibm_db

database = os.getenv("DB2_DB")
hostname = os.getenv("DB2_HOST")
username = os.getenv("DB2_USER")
password = os.getenv("DB2_PASSWORD")

conn = ibm_db.connect(
    f"DATABASE={database};"
    + f"HOSTNAME={hostname};"
    + "PORT=50001;"
    + "PROTOCOL=TCPIP;"
    + "SECURITY=SSL;"
    + f"UID={username};"
    + f"PWD={password};"
    + "sslConnection=true;"
    + "sslTrustStoreLocation=trustore.jks",
    "",
    "",
)

connState = ibm_db.active(conn)
print(f"connState: {connState}")

Dockerfile

FROM python:3.9-bullseye
RUN apt update && apt install -y neovim tmux
COPY requirements.txt .
COPY trustore.jks .
COPY load_archive_ticket.py .
COPY .bashrc /root
COPY .tmux.conf /root
RUN pip install virtualenv
RUN virtualenv venv 
RUN /bin/bash -c "source venv/bin/activate && pip install -r requirements.txt"

Steps to Reproduce:

podman build --platform linux/amd64 -t ibmdb2 .
podman run --platform linux/amd64 --env-file .env -it ibmdb2 /bin/bash
In the container source venv/bin/activate
In the container python ibmdb2_connect.py
Output

Traceback (most recent call last):
  File "//load_archive_ticket.py", line 9, in <module>
    conn = ibm_db.connect(
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

PS: with R language and DBeaver I was able to connect in the same DB:

drv <- RJDBC::JDBC(
  "com.ibm.db2.jcc.DB2Driver",
  "jars/db2jcc4.jar"
)

host <- Sys.getenv("DB2_HOST")
db <- Sys.getenv("DB2_DB")
user <- Sys.getenv("DB2_USER")
password <- Sys.getenv("DB2_PASSWORD")

uri <- sprintf(
  paste0(
    "jdbc:db2://%s:50001/%s:user=%s;password=%s;",
    "sslConnection=true;sslTrustStoreLocation=trustore.jks"
  ),
  host,
  db,
  user,
  password
)

db2 <- DBI::dbConnect(drv, uri)

About this issue

Original URL
State: closed
Created 4 months ago
Comments: 17 (9 by maintainers)

Most upvoted comments

@th1460 You need to use SSLClientKeyStoreDB keyword too pointing to full path of *.kdb file along with SSLClientKeystash keyword in connection string. If you do not provide *.kdb file in connection string, how driver will know which kdb it has to look for certificate file using the *.sth file? Note that *.sth file stores the encrypted password for the *.kdb file. Driver need password to read from the kdb file. Basically, your connection string should be as below:

connstr = "DATABASE=BLUDB;HOSTNAME=db2host.ibm.com;PORT=50001;PROTOCOL=TCPIP;SECURITY=SSL;SSLClientKeyStoreDB=/home/user/certs/keystore.kdb;SSLClientKeystash=/home/user/certs/trustore.sth;UID=dbuser;PWD=dbpass"
conn = ibm_db.connect(connstr, "", "")

Note that this *.kdb and *.sth file should be generated using GSKit on db2server system. Otherwise, get *.arm or *.cert file from trustore.jks using KeyTool and use below connection string. If your trustore.sth file is for trustore.jks, it will not work in connection string of ibm_db. connstr = "DATABASE=BLUDB;HOSTNAME=db2host.ibm.com;PORT=50001;PROTOCOL=TCPIP;SECURITY=SSL;SSLServerCertificate=/home/user/certs/servercert.arm;UID=dbuser;PWD=dbpass" Only above two types of connection string will work for SSL connection. Either using SSLServerCertificate in connection string or using (SSLClientKeyStoreDB && SSLClientKeystash in connection string OR SSLClientKeyStoreDB && SSLClientKeyStoreDBPassword in connection string). Thanks.

bimalkjha on Mar 7, 2024

@th1460 Below two keywords used in your connection string is JDBC connection keywords which are not supported for ibm_db. ibm_db uses ODBC connection keywords.

    + "sslConnection=true;"
    + "sslTrustStoreLocation=trustore.jks",

Please remove it. You have already used "SECURITY=SSL;" which is equivalent keyword for JDBC keyword sslConnection=true;. You need to generated *.arm certificate using GSKit and use it as SSLServerCertificate=<full path of *.arm file>. The corresponding ODBC keyword for sslTrustStoreLocation is SSLKeystoreDB which should point to *.kdb file created by GSKit. Please check this doc link for three formats of SSL connection string supported by ibm_db: https://github.com/ibmdb/python-ibmdb?tab=readme-ov-file#example-of-ssl-connection-string

Let us know if it resolves your problem. Thanks.

bimalkjha on Mar 2, 2024

Your question states sslTrustStoreLocation=trustore.jks ( the .jks extension suggests that the file contains a java key store, as used by java/jdbc applications).

python ibm_db is not a java application and it cannot use a java-keystore (.jks file).

Your options include: use a .kdb file created with gsk8capicmd_64, or use a PKCS12 format file, or use no keystore.

If you only have a single certificate file , then you do not need a keystore, and instead you can specify the absolute (fully qualified) pathname to the certificate file in your connection-string via the SSLServerCertificate=/path/to/your_certificate_file method. This is documented in the Db2-LUW knowledge centre.

imavo on Feb 23, 2024

python-ibmdb: Protocol specific error code(s): "414", "*", "*". SQLSTATE=08001 SQLCODE=-30081

About this issue

Most upvoted comments

python-ibmdb: Protocol specific error code(s): "414", "", "". SQLSTATE=08001 SQLCODE=-30081