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
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