Ssl – SQL Server 2014 enabling TLS 1.1 along with TLS 1.2

jdbcSecuritysql serverssl

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 ?

Best Answer

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.

Related Topic