Driver could not establish a secure connection to SQL Server
Recently windows patching was done on a server and also SQL server security got update with the same patch. Later were getting the below error while connecting to SQL Server from the JDBC application:
com.microsoft.sqlserver.jdbc.SQLServerException: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: “SQL Server did not return a response. The connection has been closed.”. at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1368) at com.microsoft.sqlserver.jdbc.TDSChannel.enableSSL(IOBuffer.java:1412) at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1058) at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:833) at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:716) at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:841) at java.sql.DriverManager.getConnection(DriverManager.java:571) at java.sql.DriverManager.getConnection(DriverManager.java:215)
……
……
Environment Details:JDBC 4.0 Microsoft SQL Server 2016 (SP2-CU17-GDR) (KB5014351) – 13.0.5893.48 (X64) May 24 2022 02:58:22 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)
Solution
We have enabled TLS 1.0 and its respective Cipher suits in the SQL box and rebooted the machine.
Now the connection worked fine.
In order to implement TLS 1.2, the following major steps must be taken:
1. Identify SQL Servers and install the latest patches on them. SQL 2016 and above by default support TLS 1.2 but below SQL 2016 you need to upgrade to a certain patch.
- Please find the below article which talks about this.
- KB3135244 – TLS 1.2 support for Microsoft SQL Server
- SQL Server below 2008 doesn’t support TLS 1.2
2. Configure TLS 1.2 on both SQL Server and application server.
Configuration
What is the correct registry setting to enable TLS 1.2 for SQL Server communication?
1. The correct registry settings are as follows:
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2]
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client] “DisabledByDefault”=dword:00000000 “Enabled”=dword:00000001
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Server] “DisabledByDefault”=dword:00000000 “Enabled”=dword:00000001
2. Above registry changes need to be in place for both SQL Server and Client Machine.
Note : Please take a backup of registry before doing any registry changes.
3. Reboot
Once all these changes are done, you need to restart the machine.
Resources/Tool
USE WITH CARE – CHANGING TLS SETTINGS CAN MAKE THE SYSTEM OR SERVICE UNUSUABLE
https://www.nartac.com/Products/IISCrypto/
This tool allows you to easily see what TLS and SSL client and server protocols are enabled and to change them.
It also allows you to enable or disable various cipher suites.
3. Identify the Driver/Provider using which you are trying to connect. Make sure that they support TLS 1.2 and are in supported version of TLS 1.2 if not then Patch them.
Following articles talks about the different patches and client component in details:
https://support.microsoft.com/en-us/help/3135244/tls-1.2-support-for-microsoft-sql-server
https://technet.microsoft.com/en-us/library/mt773991.aspx
TLS 1.2 Challenges
There are a number of challenges to the above. I have explained few details below.
· Identifying SQL Servers to be patched is relatively easy and it is always recommended to have then in the latest patch. The real challenge is in identifying the clients that require updates and which updates they need. A single machine may have applications that use several different drivers – OLE DB, ODBC, and .NET. This is going to require a very careful and thorough inventory.
· Some drivers CANNOT be patched to TLS 1.2. The most common are the SQL Server 2000 legacy drivers that ship with Windows, I.e. Provider=SQLOLEDB or Driver={SQL Server}. The customer will have to rewrite or reconfigure their application to use a SQL 2008 or later driver or Provider.
· If the application cannot be rewritten, e.g. source code lost or it is a 3rd-party application and the vendor went out of business or will not rewrite it, then you cannot turn on TLS 1.2 for that client or the target server.
· DBAs will have to work with the various application development teams to determine which drivers the applications use and upgrade and test.
Below are the sample provider or drivers which supports TLS 1.2
- Native Client 11.0
Download ENU\x64\sqlncli.msi from the above link.
- Native Client 10.0
https://www.microsoft.com/en-us/download/details.aspx?id=57606
Download ENU\x64\sqlncli.msi from the above link.
- Microsoft® OLE DB Driver 18 for SQL Server®
https://www.microsoft.com/en-us/download/details.aspx?id=56730
- Microsoft® ODBC Driver 17 for SQL Server® – Windows, Linux, & macOS
https://www.microsoft.com/en-us/download/details.aspx?id=56567
- For .NET Provider
.NET 4.6 by default support TLS 1.2 if you are below 4.6 then you have to apply a certain patch as per the below link:
you need to follow the link: https://support.microsoft.com/en-in/help/3135244/tls-1-2-support-for-microsoft-sql-server
- As far as the TLS 1.2 is concern JDBC 4.0 onwards it does support TLS 1.2.
But TLS 1.2 is not turned on by default in JDK 1.6 or JDK 1.7. You need to check with the IBM team on this if you are planning to use JDK 1.6 and 1.7 respectively.
Microsoft JDBC Driver for SQL Server Support Matrix:
Source: https://www.java.com/en/configure_crypto.html
How to change the protocol version on client side
Several options exist for changing the default client-side TLS protocol version in the JDK.
Option 1. Use the “jdk.tls.client.protocols” system property
This property was introduced to JDK 7 in 7u95 and to JDK 6 in 6u121.
To enable specific TLS protocols on the client, specify them in a comma-separated list within quotation marks; all other supported protocols are then disabled on the client. For example, if the value of this property is “TLSv1.1,TLSv1.2”, then the default protocol settings on the client for TLSv1.1 and TLSv1.2 are enabled on the client, while SSLv3, TLSv1, and SSLv2Hello are disabled on the client.
// Set the client default protocol versions to TLS 1.0, 1.1 and 1.2.
$ java ‑Djdk.tls.client.protocols=”TLSv1,TLSv1.1,TLSv1.2” myApp
// Set the client default protocol version to TLS 1.0.
$ java ‑Djdk.tls.client.protocols=”TLSv1” myApp
Note that the standard TLS protocol version names used in the JDK are SSLv3, TLSv1, TLSv1.1 and TLSv1.2.
##Read more on SQL Server Installation Best Practices