SSL Connection to DB2 Z/OS

[benyang]

Hi all,

I have Qlik Replicate setup on a Windows VM, and in my setup I am trying to set DB2 Z/OS as my source endpoint and I need to enable SSL due to security reasons.

I installed the IBM Data Server Client 11.5.8 (v11.5.8_ntx64_client.exe) and imported the DB2 cert that was used for SSL connection for all applications and VMs in the environment, I also enable the UseSSL via the Internal Parameter as seen below:

But when I run the Test Connection to the source endpoint, I receive this error:

SQL30081N A communication error has been detected. Communication protocol being used: "SSL". Communication API being used: "SOCKETS". Location where the error was detected: "SOCKETS". Communication function detecting the error: "sqlccSSLSocketSetup". Protocol specific error code(s): "414", "*", "*". SQLSTATE=08001

On DB2 side, the error I got was:
SOCKET=RECV RETURN CODE=1121 REASON CODE=77A9733D

JrTlsHandshakeFailed
AT-TLS was unable to successfully negotiate a secure TCP connection with the remote end.

Does anyone know about this?

How do I know that Qlik Replicate is using the correct certificate to connect to my DB2?
Because I have a self-signed cert  for accessing the Qlik Replicate UI Console via web browser and a DB2 cert that is for the SSL connection.

Are there anything that I have missed during this configuration?

[SachinB]

Hello @benyang ,

The above error message related to TLS (Transport Layer Security) handshake failure. The TLS handshake is the process that occurs when two parties (client and server) establish a secure encrypted connection

Could you please refer the below IBM article for the same, If did not solve the issue, we would request you create a support case in order to investigate it further with below enhanced logs?

Kindly set all parameter for verbose for server logs and Do the test connection for endpoint, Later attach repsrv logs to the case for further investigation

https://www.ibm.com/support/pages/sql30081n-sqlccsslsocketsetup-protocol-specific-error-codes-420-er...

Thanks for understanding !!

Regards,

Sachin B

[john_wang]

Hello @benyang ,

Thanks for posting in Qlik Community!

There are some steps to setup the SSL connection, please check the article Replicate-DB2z SSL Configuration.

However looks to me the error caused by the certificate, see:

414 – Incorrectly formatted certificate received from partner

BTW, please try to open a 64-bit ODBC DSN and config it to connect to DB2z with SSL configuration, it's straighter to troubleshoot than doing that in Replicate endpoint.

Hope this helps.

Regards,

John.

[benyang]

Hi @john_wang ,

I've seen the link that you provided: Replicate-DB2z SSL Configuration.
However the concern now is that all the certificates in my environment are signed by a CA, we cannot create a new DB2 certificate using the GSK command in the DB2 server.

BTW, please try to open a 64-bit ODBC DSN and config it to connect to DB2z with SSL configuration, it's straighter to troubleshoot than doing that in Replicate endpoint.

With the scenario above, do I still go through the 64 bit ODBC DSN process that was mentioned in the link? Because I cannot go through the GSK steps that was mentioned.

Otherwise do you have any suggestions that I can look into it?

Hi @SachinB ,

I will enable verbose logging and do a test connection again and update here.

Thanks!

Regards,
Benjamin

[john_wang]

Hello @benyang ,

Thanks for the update.

we cannot create a new DB2 certificate using the GSK command in the DB2 server

---

Looks like this is the key point. The previous error message complained about the certificate format error too. To avoid misleading I'd like to suggest contacting IBM, this is the DB2z ODBC Client and Server SSL setup subject.

Feel free to let us know if you need additional information.

Regards,

John.

[benyang]

Hi @john_wang ,

Thanks for prompt reply.

Can I just clarify with you that for the SSL connection from Qlik Replicate to DB2 Z/OS, is it a must to use a DB2 certificate that was created with the GSK commands in the DB2 server?

We cannot use a CA-signed certificate or any other certificate for this?

Just want to make sure I look for the right people on this.

Thanks!

Regards,
Benjamin

[john_wang]

Hello @benyang ,

Thanks for the update.

Qlik Replicate relies on IBM DB2z SSL community protocols, it's pure IBM DB2 client/server connectivity scope. From many IBM DB2z docs for example Configuring the IBM DS Driver non-Java interfaces:
Command-line interface, ODBC, and .NET, the GSK is used too.  I'm afraid I cannot help too much with this question this time, sorry for that.

Regards,

John.

[benyang]

Hi @john_wang ,

No worries.

So I managed to get the kdb and sth file in my Qlik Replicate VM through other means.

When I click Test Connection on the Qlik Replicate console.

I receive the error:

SYS-E-HTTPFAIL, Cannot connect to DB2 zOS Server.
SYS, GENERAL_EXCEPTION, Cannot connect to DB2 zOS Server, RetCode: SQL_ERROR
SqlState: IM004 NativeError: 0 Message: [Microsoft][ODBC Driver Manager] 
Driver's SQLAllocHandle on SQL_HANDLE_ENV failed

But on DB2 side, the message I receive:

SQL11058   S                         0 
L209-Functional Level = s2209201700

^The above message shows that Qlik can connect to DB2 successfully

Do you know what does this mean?

Regards,
Benjamin

[john_wang]

Hello @benyang ,

I'm not familiar with coding... the SQL_HANDLE_ENV is one beginning step in ODBC connection establishing stage, this step fails means the connection cannot work. I'd like to suggest troubleshooting the connectivity by define 64-bit ODBC DSN, then try to access the DSN. It's much straighter than Replicate endpoint testing.

Google the error may help too, for example: [IM004] [Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed.

Good luck,

John.

[benyang]

Hi @john_wang 

Thanks for the help and I agree with you, DSN is way easier to test/troubleshoot instead of endpoint testing

But do you know where or how should I troubleshoot for the 64bit ODBC DSN? 
Because I pretty much follow the DSN configurations from: Replicate-DB2z SSL Configuration already.
Just to clarify- access the DSN refers to connecting to the DB2 server through the ODBC DSN? Or you meant something else?

Apologies as I am not familiar to this DB2 infrastructure.

And thank you for the help, appreciate it 🙂 

Regards,
Benjamin