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" when executing Information Link in TIBCO Spotfire

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" when executing Information Link in TIBCO Spotfire

Information links sourcing data from Microsoft SQL Server (MSSQL) at times fail to load with the following error:

Could not create a connection: 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." Here are the probable causes for this error with their solutions:

Cause 1:
When connecting to SQL Server, if the application requests encryption and the instance of SQL Server is configured to support SSL encryption, the Microsoft JDBC Driver for SQL Server initiates the SSL handshake. The handshake allows the server and client to negotiate the encryption and cryptographic algorithms to be used to protect data.  If the application does not request encryption, the Microsoft JDBC Driver for SQL Server will not force SQL Server to support SSL encryption. If the SQL Server instance is not configured to force the SSL encryption, a connection is established without encryption. If the SQL Server instance is configured to force the SSL encryption, the driver will automatically enable SSL encryption when running on a properly configured Java Virtual Machine (JVM), or else the connection is terminated and the driver will raise the above mentioned error.

Solution
Ask your DBA to run this command on the MSSQL database to check if SSL is configured:

SELECT session_id, encrypt_option FROM sys.dm_exec_connections

This query dumps information about the connections to your SQL Server. Any of them that is using a secured connection will have a ‘True’ value in the encrypt_option column. If SSL is configured, it can be disabled in the jdbc url by adding encrypt=false at the end of the MSSQL jdbc url in the information link data sources, separated by ';' (;encrypt=false). Example:

jdbc:sqlserver://mysqlserver:1433;DatabaseName=emp;encrypt=false

Cause 2:
When connections are repeatedly made to SQL Server, one can observe about ~ 1% of connections having inability to connect.  The user witnesses the above error message. Due to some recent Microsoft updates, customers may experience connectivity issues with 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 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 older JDBC drivers, below version 4.2.  Newer drivers work fine. 

Solution
Upgrade the sqlserver jdbc jar to version 4.2. Out of the box, the version of sqlserver jdbc jar that ships with Spotfire Server is sqljdbc4. This jar is located in the Spotfire Server install /tomcat/lib folder. This jar can be replaced with version 4.2.

Link to download the sqljdbc42.jar: https://www.microsoft.com/en-us/download/details.aspx?id=54671 External: Microsoft JDBC Driver 4.2 for SQL Server

• https://www.microsoft.com/en-us/download/details.aspx?id=54671