Skip to Content
0
May 01, 2020 at 01:47 PM

SAP PI 7.4 to SQL 2016 JDBC Connectivity Issue

996 Views

Hi team,

I am having an intermittent issue while pishing data to a third party SQL Server Database ( 2016) using my SAP PI 7.4. The issue is intermittent and the error messgae is as below

Unable to create new pooled resource: DriverManagerException: Cannot establish connection with the registered driver. com.microsoft.sqlserver.jdbc.SQLServerDriver returns: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "SQL Server returned an incomplete response. The connection has been closed.


I have checked MS Link which seems to be relevant

Symptoms:

When connections are repeatedly made to SQL Server, one can observe about ~ 1% of connections having inability to connect. The user witnesses the following error message,

Error:

The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "SQL Server returned an incomplete response. The connection has been closed. "

Background:

Due to some recent Microsoft updates, customer’s may experience connectivity issues to trading partners when using SSL / TLS to secure the connection. Recently, Microsoft has added two new ciphers to Windows, which use a different Key Algorithm. The minimum key length for allowed by these ciphers is 1024 characters. If the key length used by the trading partners is less than this, the SSL /TLS Handshake will fail.

The issue is only witnessed when using the older JDBC drivers below version 4.2. Other drivers works fine. We were unable to reproduce the issue when using Sqlclient/ADO.net stack (even when we forced a DHE suite).

Resolution/Workaround:

Please perform the action plan below.

Option-1

  • Update the JDBC Driver to 4.2 or later version :

We implemented a workaround where the JDBC driver will retry the connection in the specific case where the SSL handshake receives an incomplete response from the Server.

Update the JDBC Driver to 4.2 or later version. This has a re-try logic inside. Make sure you have a supported JVM / JRE on that machine.

I have checked the JDBC Driver version and if I am nto stupid the below one is showing the target JDBC driver which is 4.0. As per the blog 4.2 or later JDBC driver won't have this issue.

Attempting to load driver class: com.microsoft.sqlserver.jdbc.SQLServerDriver
Attempting to obtain connection using DriverManager.getConnection() ...
Sucessfully obtained connection object = ConnectionID:491 ClientConnectionId: b9172a2f-d5d1-47a7-91b7-d47d98f785bc
conn.getMetaData().getDatabaseProductName() = Microsoft SQL Server
conn.getMetaData().getDatabaseProductVersion() = 13.00.5026
conn.getMetaData().getDriverName() = Microsoft JDBC Driver 4.0 for SQL Server
conn.getMetaData().getDriverVersion() = 4.0.4621.201
conn.getMetaData().getJDBCMajorVersion() = 4
conn.getMetaData().getJDBCMinorVersion() = 0
Closing JDBC resources...
Closing JDBC connection ...
Closed JDBC Resources.

However my quesiton here is which JDBC driver PI uses to connect to the destination SQL instance - is it the target or it's own? which one is to be upgraded?

Your help is much appreciated and aplogies if I have asked stupid question

Thanks,

Mofizur