Db2

I have error when trying to connect from my app to cataloged database using SSL. I will describe performed steps.

• Create database TDB
• db2 "CATALOG TCPIP NODE SSLNODE REMOTE <HOST> SERVER <PORT> SECURITY SSL"
• db2 "CATALOG DB TDB AS TDBSSL AT NODE SSLNODE"
• Create self signed certificate and GSK storages:

gsk8capicmd_64.exe -keydb -create -db "C:\ssl\server.kdb" -stash -genpw

gsk8capicmd_64.exe -keydb -create -db "C:\ssl\storage.kdb" -stash -genpw

gsk8capicmd_64.exe -cert -create -db "C:\ssl\server.kdb" -stashed -label "selfsignedcert" -dn "CN=TestCompany"

gsk8capicmd_64.exe -cert -extract -db "C:\ssl\server.kdb" -stashed -label "selfsignedcert" -target "C:\ssl\server_cert.arm" -format ascii

gsk8capicmd_64.exe -cert -add -db "C:\ssl\storage.kdb" -stashed -label "selfsignedcert" -file "C:\ssl\server_cert.arm" -format ascii

• Configure instance to use SSL

db2 "UPDATE DBM CFG USING SSL_SVR_KEYDB C:\ssl\server.kdb"

db2 "UPDATE DBM CFG USING SSL_SVR_STASH C:\ssl\server.sth"

db2 "UPDATE DBM CFG USING SSL_SVR_LABEL selfsignedcert"

db2 "UPDATE DBM CFG USING SVCENAME NULL"

db2 "UPDATE DBM CFG USING SSL_SVCENAME db2c_DB2"

db2set -i DB2 DB2COMM=SSL

db2 terminate

db2stop

db2start

Connection string used in SQLDriverConnect API function:

Database=SSLDS;UID=<USER>;PWD=<PWD>;Protocol=TCPIP;Servicename=<PORT>;Security=SSL;SSLClientKeystoredb=c:\ssl\storage.kdb;SSLClientKeystash=c:\ssl\storage.sth;

First connection attempt to cataloged database SSLDS shows error

[IBM][CLI Driver] SQL1224N The database manager is not able to accept new requests, has terminated all requests in progress, or has terminated the specified request because of an error or a forced interrupt. SQLSTATE=55032

Next connections attempts to cataloged database SSLDS show error:

[IBM][CLI Driver] SQL30040N Execution failed because of unavailable resources that will not affect the successful execution of subsequent commands and SQL statements: Reason "0x0", Type of Resource "MEMORY", Resource Name "", Product ID "SQL11013 ". SQLSTATE=57012

But if I use database name instead of database alias, i.e TDB instead of SSLDS, connection is established.

Also, from the Db2 CLP I can connect both using the name and alias (after install variables SSL_CLNT_KEYDB and SSL_CLNT_STASH)

All tests performed on Windows machine.

Db2 version

DB21085I This instance or install (instance name, where applicable: "DB2")

uses "64" bits and DB2 code release "SQL11013" with level identifier

"0204010F".

Informational tokens are "DB2 v11.1.3030.239", "s1803021700",

"DYN1803021700WIN64", and Fix Pack "3".

Product is installed at "C:\PROGRA~1\IBM\SQLLIB" with DB2 Copy Name "DB2COPY1"

gsk8capicmd_64.exe -version

GSKCAPICMD

==========

(#)CompanyName: IBM Corporation

(#)LegalTrademarks: IBM

(#)FileDescription: IBM Global Security Toolkit

(#)FileVersion: 8.0.50.86

(#)InternalName: gskcapicmd

(#)LegalCopyright: Licensed Materials - Property of IBM GSKit

(C) Copyright IBM Corp.1995, 2017

All Rights Reserved. US Government Users

Restricted Rights - Use, duplication or disclosure

restricted by GSA ADP Schedule Contract with IBM Corp.

(#)OriginalFilename: gsk8capicmd_64.exe

(#)ProductName: gsk8l (GoldCoast Build) 171214

(#)ProductVersion: 8.0.50.86

(#)ProductInfo: 17/11/16.21:20:03.17/12/14.17:37:15

(#)CMVCInfo: gsk8l_171103/gsk8l_ikm gsk8l_170602/gsk8l_acme gsk8l_171122/gsk8l_ssl gsk8l_171207/gsk8l_cms gsk8l_171207/gsk8l_support gsk8l_171207/gsk8l_doc gsk8l_170908/gsk8l_pkg

db2set

DB2INSTOWNER=*******

DB2PORTRANGE=60000:60005

DB2_GRP_LOOKUP=LOCAL,TOKENLOCAL

DB2INSTPROF=C:\PROGRAMDATA\IBM\DB2\DB2COPY1

DB2COMM=SSL

db2 get dbm cfg | grep SSL

SSL server keydb file (SSL_SVR_KEYDB) = C:\ssl\server.kdb

SSL server stash file (SSL_SVR_STASH) = C:\ssl\server.sth

SSL server certificate label (SSL_SVR_LABEL) = selfsignedcert

SSL service name (SSL_SVCENAME) = db2c_DB2

SSL cipher specs (SSL_CIPHERSPECS) =

SSL versions (SSL_VERSIONS) =

SSL client keydb file (SSL_CLNT_KEYDB) =

SSL client stash file (SSL_CLNT_STASH) =

Some errors from db2diag.log

2021-02-24-17.23.36.988000+120 I116028103F512 LEVEL: Error

PID : 11892 TID : 5940 PROC : db2syscs.exe

INSTANCE: DB2 NODE : 000 DB : TDB

APPHDL : 0-37

HOSTNAME: *****

EDUID : 5940 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".

2021-02-24-17.23.36.990000+120 I116028617F755 LEVEL: Info

PID : 11892 TID : 3148 PROC : db2syscs.exe

INSTANCE: DB2 NODE : 000

APPHDL : 0-38

HOSTNAME: *****

EDUID : 3148 EDUNAME: db2agent () 0

FUNCTION: DB2 UDB, DRDA Application Server, sqljsInitAgent, probe:10

DATA #1 : String, 15 bytes

sqlccinit rc =

DATA #2 : unsigned integer, 4 bytes

54

DATA #3 : Communication Failure Condition, PD_TYPE_COMCONDITION, 284 bytes

Communication Condition

SEVERITY = 46371

NUMBER TOKENS = 7

MSG TOKEN[0] = 420

MSG TOKEN[1] = *

MSG TOKEN[2] = *

MSG TOKEN[3] = SSL

MSG TOKEN[4] = SOCKETS

MSG TOKEN[5] =

MSG TOKEN[6] = sqlccSSLSocketSetup

2021-02-24-17.23.36.991000+120 I116029374F471 LEVEL: Info

PID : 11892 TID : 5940 PROC : db2syscs.exe

INSTANCE: DB2 NODE : 000 DB : TDB

APPHDL : 0-37

HOSTNAME: *****

EDUID : 5940 EDUNAME: db2agent () 0

FUNCTION: DB2 UDB, DRDA Communication Manager, sqljcCmnMgrInit, probe:6

MESSAGE : ZRC=0x81360012=-2127167470=SQLZ_RC_CMERR, SQLT_SQLJC

"External Comm error"

2021-02-24-17.23.36.992000+120 I116029847F481 LEVEL: Info

PID : 11892 TID : 3148 PROC : db2syscs.exe

INSTANCE: DB2 NODE : 000

APPHDL : 0-38

HOSTNAME: *****

EDUID : 3148 EDUNAME: db2agent () 0

FUNCTION: DB2 UDB, DRDA Application Server, sqljsDrdaAsInnerDriver, probe:10

MESSAGE : ZRC=0x824B0001=-2109014015=SQLJS_ICE "Internal Error"

DIA8532C An internal processing error has occurred.

2021-02-24-17.23.36.995000+120 I116030330F461 LEVEL: Info

PID : 11892 TID : 3148 PROC : db2syscs.exe

INSTANCE: DB2 NODE : 000

HOSTNAME: *****

EDUID : 3148 EDUNAME: db2agent () 0

FUNCTION: DB2 UDB, DRDA Application Server, sqljsDrdaAsDriver, probe:100

MESSAGE : ZRC=0x824B0001=-2109014015=SQLJS_ICE "Internal Error"

DIA8532C An internal processing error has occurred.

...........

2021-02-24-17.24.08.785000+120 I116239146F634 LEVEL: Error

PID : 11892 TID : 8524 PROC : db2syscs.exe

INSTANCE: DB2 NODE : 000 DB : TDB

APPHDL : 0-69

HOSTNAME: *****

EDUID : 8524 EDUNAME: db2agent () 0

FUNCTION: DB2 UDB, common communication, sqlccLoadSSLLibrary, probe:258

MESSAGE : ZRC=0x00000064=100=SQLUDI_RC_RECORD_WARNING "Unknown"

DIA8111C Authorization failure.

DATA #1 : <preformatted>

GSKKM_attribute_get_buffer: GSKit Error = 100,

KeyDB = ,

EncPassLen = 32, PwdLen = 30, DefaultKeyDBUsed = 1,

ServerCert = .

2021-02-24-17.24.08.786000+120 I116239782F520 LEVEL: Error

PID : 11892 TID : 8524 PROC : db2syscs.exe

INSTANCE: DB2 NODE : 000 DB : TDB

APPHDL : 0-69

HOSTNAME: *****

EDUID : 8524 EDUNAME: db2agent () 0

FUNCTION: DB2 UDB, common communication, sqlccMapSSLErrorToDB2Error, probe:258

MESSAGE : DIA3604E The SSL function "GSKKM_attribute_get_buffer" failed with

the return code "100" in "sqlccLoadSSLLibrary".

2021-02-24-17.24.08.787000+120 I116240304F434 LEVEL: Error

PID : 11892 TID : 8524 PROC : db2syscs.exe

INSTANCE: DB2 NODE : 000 DB : TDB

APPHDL : 0-69

HOSTNAME: *****

EDUID : 8524 EDUNAME: db2agent () 0

FUNCTION: DB2 UDB, common communication, sqlccLoadSSLLibrary, probe:998

MESSAGE : DIA3603E SSL was not setup. Return code = "91".

2021-02-24-17.24.08.789000+120 I116240740F1997 LEVEL: Severe

PID : 11892 TID : 8524 PROC : db2syscs.exe

INSTANCE: DB2 NODE : 000 DB : TDB

APPHDL : 0-69

HOSTNAME: *****

EDUID : 8524 EDUNAME: db2agent () 0

FUNCTION: DB2 UDB, DRDA Communication Manager, sqljcCommConnect, probe:10

MESSAGE : ZRC=0x8636000A=-2043281398=SQLZ_RC_FNEX, SQLT_SQLJC

"File Does Not Exist"

DIA8411C A file "" could not be found.

I used wrong CLI keyword "DATABASE". In my case correct connections string is

DSN=...;UID=...;PWD=...;Security=SSL;SSLServerCertificate=...

But if I use SSL_CLNT_KEYDB and SSL_CLNT_STASH keywords, connection fails with error