topic Re: SSL Connection to DB2 Z/OS in Qlik Replicate

SSL Connection to DB2 Z/OS

benyang — Thu, 27 Jul 2023 11:31:49 GMT

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?

Re: SSL Connection to DB2 Z/OS

SachinB — Thu, 27 Jul 2023 12:24:58 GMT

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-error-when-enabling-tls-transport-layer-security-due-dia3604e

Thanks for understanding !!

Regards,

Sachin B

Re: SSL Connection to DB2 Z/OS

john_wang — Thu, 27 Jul 2023 12:28:34 GMT

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.

Re: SSL Connection to DB2 Z/OS

benyang — Fri, 28 Jul 2023 01:41:04 GMT

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

Re: SSL Connection to DB2 Z/OS

john_wang — Fri, 28 Jul 2023 02:16:29 GMT

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.

Re: SSL Connection to DB2 Z/OS

benyang — Fri, 28 Jul 2023 03:31:08 GMT

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

Re: SSL Connection to DB2 Z/OS

john_wang — Sat, 29 Jul 2023 05:14:54 GMT

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.

Re: SSL Connection to DB2 Z/OS

benyang — Mon, 31 Jul 2023 11:02:13 GMT

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

Re: SSL Connection to DB2 Z/OS

john_wang — Mon, 31 Jul 2023 14:45:32 GMT

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.

Re: SSL Connection to DB2 Z/OS

benyang — Mon, 31 Jul 2023 15:24:44 GMT

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

Re: SSL Connection to DB2 Z/OS

Dana_Baldwin — Mon, 31 Jul 2023 17:48:56 GMT

Hi @benyang

In ODBC Data Source Administrator (64-bit) you can define a path to create a log and trace the activity on the Tracing tab. Maybe that will capture some helpful information to pursue.

Thanks,

Dana

Re: SSL Connection to DB2 Z/OS

benyang — Tue, 01 Aug 2023 03:20:56 GMT

Hi All,

Just an update, I managed to connect successfully from Qlik to the DB2 server 😀

However when I try to click on Table Selection in a new task, I received the above error.
From what I understand, SYSIBM.SQLTABLES does not exist in the DB2 environment at all.

And the ID that's connecting to DB2 has the permissions granted below:

Does anyone know what is the cause for this?

Regards,
Benjamin

Re: SSL Connection to DB2 Z/OS

john_wang — Tue, 01 Aug 2023 05:25:40 GMT

Hello @benyang ,

Great news! Thank you so much for your support.

Regarding the error please check article SqlState: 42501 NativeError: -551 Message: [IBM][CLI Driver][DB2] SQL0551N.

Hope this helps.

Regards,

John.