SQL Server 2014 enabling TLS 1.1 along with TLS 1.2

Question

I have a Windows Server 2012 R2 which is a DC with SQL Server 2014 (Express) updated to the latest SP2 with CU10 12.2.5571.0 (testing environment). I disabled all protocols except TLS1.1 and TLS1.2 by setting the registry keys in the path:

HKLM SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols.

I initially enabled only TLS1.2 but I need also TLS1.1 for backward compatibility with a Java application which run on another machine of the same domain. In order to use TLS1.2 a specific JDBC driver is needed and a property must be provided to the JDBC url (see the discussion here).

In the meanwhile I want to let the application work with TLS1.1 so I re-enabled it. However the application can't connect to the database and it seems like TLS1.1 is not used by the SQL Server. In order to test the connection i tried the following commands:

1) openssl s_client -connect <Server IP>:1433 -ssl3

2) openssl s_client -connect <Server IP>:1433 -tls1_1

3) openssl s_client -connect <Server IP>:1433 -tls_1_2

The test 1) fails as expected (handshake failure) while the test 2) takes several time (about 30 seconds or more) to produce the response which is successful. The test 3) produce a successful output as expected.

I also tried using Management Studio from the client machine and the connection only works if TLS1.2 is enabled on both the client and server. Otherwise if only TLS1.1 is enable the following error is displayed:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The client and server cannot communicate, because they do not possess a common algorithm.)

How can it possible ?

Do I need to force something on the Secure Channel ?

Why does the SQL Server doesn't allow TLS1.1 connections anymore ?

Solution 1

I verified that in order to use the protocol TLS1.1 the following keys must be set:

• SHA hash Enabled (Enabled key set to 1)

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Hashes\SHA

• TLS 1.0 protocol Enabled (Enabled key set to 1 and DisabledByDefault set to 0 for both Client and Server)

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Client

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Server

NOTE: After changing those keys restart the machine so that the Secure Channel connection is reset.

If either the SHA hash or the TLS 1.0 protocol is disabled then the protocol TLS 1.1 stop working for unknown reasons.

Solution 2

Protocol is not the same as algorithm.

Regardless how you configure SQL Server, the OS has to allow the protocols and support the algorithms too.

For more information please read: https://blogs.msdn.microsoft.com/sql_protocols/2007/06/29/ssl-cipher-suites-used-with-sql-server/

Determine the highest level protocol mutually supported by the client and the server. The currently recognised protocols are, from highest to lowest: TLS1.1, TLS1.0, SSL3.0, SSL2.0 The client will provide the server with a list of its cipher suites from the negotiated protocol The server will chose the strongest cipher suite that it is able to support from the client's list.

You may find more information about best practices on the subject of SSL, here https://github.com/ssllabs/research/wiki/SSL-and-TLS-Deployment-Best-Practices

If you don't feel confortable fiddling with the Windows' registry, you could use Nartact free tool IIS Crypto to configure your Windows server. The IIS part is irrelevant. This tool actually configures the OS.

Comments

[HackSlash]

What version of .NET do you have installed? Make sure to install the latest patches for your .NET runtimes.

[HackSlash]

Update the SNAC/OBDC driver on all clients connecting to the SQL Server: microsoft.com/en-us/download/details.aspx?id=36434

[Bemipefe]

@HackSlash on the server: Microsoft .NET Framework 4 Multi-Targeting Pack 4.0.30319 (latest patch installed). ODBC 11 already installed to the latest version.

[Bemipefe]

Thanks Ricardo but the problem is not related to cipher suites. The problem is related to the TLS protocol. If TLS1.2 is disabled I can't connect even by using SQL Management Studio (with sa account). I read in this support.centrestack.com/hc/en-us/articles/… that disabling SHA-1 algoritm imply disabling TLS1.1. So I tried re-enabling SHA but the connection still fails if TLS1.2 is disabled. So it seems like TLS1.1 doesn't work properly on newer SQL Server versions.

[Bemipefe]

I renamed the Ciphers key so that all disabled ciphers were enabled again and I was able to connect with Management Studio even if TLS1.2 was disabled. Anyway the JDBC driver is still unable to connect to the same server. The same JDBC driver and application works if I put in the JDBC Url the address of an old SQL server. So it's definitively the latest SQL Server version that doesn't handle correctly the TLS1.1 connections.

[Ricardo C]

Disabling SHA-1 does not disables TLS 1.1 that is incorrect. Renaming the ciphers key will affect ALL protocols! You wont be able to connect using... does not matter what tool you use because you disabled it at the OS level. Stop messing with the registry, specially if you dont fully understand those keys; please use IIS Crypto instead. Also, make sure you patched your SQL Server and added support for TLS 1.2 support.microsoft.com/en-us/help/3135244/…

[Ricardo C]

As stated in the answer; protocol and algorithm are not the same thing: A protocol is used to negotiate a ciphers/algorithms, and to exchange keys.

[Bemipefe]

The server is already at the latest patch level as weel as all the components including ODBC, .NET etc ... as stated in the post TLS1.2 works as soos as I use the right JDBC driver but the problem appears when TLS1.2 is disabled (The Java application only works in TLS1.1 ).

[Bemipefe]

Renaming the ciphers key means that the default values are restored. There were no cipher keys before that I added them.