Python IBM_DB package using encryptionAlgorithm and securityMechanism parameters

I have a requirement to use the encryptionAlgorithm and securityMechnism parameters when connecting to a DB2 database. I have not come across any information about how to include this in the connection string for ibm_db. This is how my current connection string looks:

import ibm_db_dbi 

connect = ibm_db_dbi.connect("DATABASE=; \
    HOSTNAME=; \
    PORT=; \
    PROTOCOL=; \
    UID=; \
    PWD=; \
    CURRENTSCHEMA=;", "", "")

I can achieve connection to the database like this. But I have no idea where to include these parameters. In one git hub issue that was opened, a rep from IBM_DB said:

We use ODBC and CLI Driver under the cover and hence AUTHENTICATION is the property you would be interested in. You could set this property in db2cli.ini file or db2dsdriver.cfg file or as a connection string attribute to ibm_db.connect() API. https://github.com/ibmdb/python-ibmdb/issues/279

However, it is not specfied how to include the AUTHENTICATION parameter in the connection string. It is just to stick it in like so, or can I just put in securityMechanism and encryptionAlgorithm directly as their own parameters in the connection string as such?

AUTHENTICATION={securityMechanism:9;encryptionAlgorithm:2}

Another person said that it is totally possible to use the encrptionAlgorithm and securityMechanism parameters as part of the connection string, but doesn't indicate how:

For Db2 servers that run on Linux/Unix/Windows , the python ibm_db module with clidriver (or other IBM supplied equivalent), supports connection-strings that have the AUTHENTICATION parameter matching the value of that parameter on the Db2-instance connect db2 using python with securityMechanism=13

I would appreciate some help in determning where I can place these values, encryptionAlgorithm=2 and securityMechanism=9, in the connection string for IBM_DB.

Solution

Both of these attributes securityMechanism and EncryptionAlgorithm are for JDBC/SQLJ applications only. Do not confuse a JDBC/SQLJ application with a CLI application.

Python ibm_db module does not use jdbc or java, it is written in the 'C' language and interfaces to the database via the CLI (Call Level Interface). So your python script is perceived by Db2 as a CLI application. Python ibm_db apps are not JDBC/SQLJ applications.

The connection attributes (also known as keywords) for CLI applications can differ from those of JDBC/SQLJ applications, although many are in common or have different names or values.

A CLI application can specify the AUTHENTICATION mechanism via connection-string attribute or db2dsdriver.cfg (or for MS-Windows only via db2cli.ini). In all cases this appears as authentication=xxxx, where xxx is the value of the authentication which must match that of the Db2-server hosting the database to which you are connecting. In a connection string, attributes need a leading and trailing semicolon (but the very first attribute does not have a leading semicolon).

When connecting to Db2-LUW servers, currently supported IBM CLI drivers will decide (by default) the encryption automatically to be one perceived as most secure and implemented both by the CLI driver and Db2-server. That is the reason your connection succeeds without specifying these attributes.

For CLI applications you can additionally specify the algorithm with keyword/attribute ClientEncAlg=2 (meaning AES) although if you omit this the driver can choose it anyway if both sides of the connection implement the algorithm at the same key length.

For the full list of all the possible connection keywords / parameters, please refer to the Db2 online Knowledge centre for your Db2-platform and your Db2-server-version. Keywords can be version specific and Db2-server-platform specific, in addition to configuration dependent. For Db2-LUW see here.

For automatic client reroute, check the AltHostName keyword, which can also be part of a connection string. However, for ACR, correct configuration of the Db2-server instance will allow the server to return alternate host/port details to the CLI driver on clients, as long as the CLI driver is properly maintained and kept 'current' with fixpacks. Related parameters/keywords may also need to be set for seamless failovers, refer to the docs.

So in your code, you could try:

connect = ibm_db_dbi.connect("DATABASE=whatever; \
    HOSTNAME=whatever; \
    PORT=whatever; \
    PROTOCOL=TCPIP; \
    UID=whatever; \
    PWD=whatever; \
    CURRENTSCHEMA=whatever;\
    AUTHENTICATION=SERVER_ENCRYPT;\
    ClientEncAlg=2;", "", "")

You could also try AUTHENTICATION=SERVER_ENCRYPT_AES , it depends on the target Db2-server platform (Z/OS, i-series, Linux/Unix/Windows) and configuration. Your question did not specify these facts.

But beware! If your Db2-server configuration is not appropriately configured then the connection will then fail. That's the reason for omitting such details and letting the CLI driver work out what's possible.

Many sites prefer to omit such configuration from python script source code, and instead store the configuration details externally (in db2dsdriver.cfg) and in the code simply reference a DSN or have that name also external, but that is a separate matter. External configuration means the connection-string won't change between platforms and environments and versions, but most importantly it allows any CLI or ODBC application to benefit, not only python ibm_db, and lets the configuration be testable/verifiable without using python.