SSL Errors after Upgrading to TLS 1.2

There are three main problems that can occur after upgrading to TLS 1.2:

1. SQL Server is using a certificate that is signed by a weak hash algorithm. These include: MD5, SHA224, and SHA512.
2. TLS 1.2 upgrades were only applied to the client or the server but not both and TLS 1.0 is disabled.
3. There are no matching cryptographic algorithms between the client and the server.

1. Examine the server certificate.

Reference: https://technet.microsoft.com/en-us/library/ms189067(v=sql.105).aspx Public

Problems with the server certificate will affect local connections as well as connections from client computers.

The application may throw one of these errors:

Named Pipes

A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - No process on the other end of the pipe) Microsoft SQL Server, Error: 233

TCP

A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 – The connection was forcibly closed by remote host 10054) Microsoft SQL Server, Error: 233

If you have a network capture, it may look like this, where the server responds to the Client Hello packet by closing the connection:

1.1. In SQL Server Configuration Manager, right-click on Protocols for instancename, and select Properties.

1.2. Click the **Certificate **tab and see what certificate is being used:

If a certificate is present, you can use the **View **button to examine it and then **Clear **it. Skip to 1.4.

1.3. If the certificate is not present (as above), look in the SQL Server ERRORLOG file and get the hash code the is being used. You will see one of two entries:

2017-05-30 14:59:30.89 spid15s The certificate [Cert Hash(sha1) "B3029394BB92AA8EDA0B8E37BAD09345B4992E3D"] was successfully loaded for encryption.

or

2017-05-19 04:58:56.42 spid11s A self-generated certificate was successfully loaded for encryption.

If the certificate is self-generated, skip to step 2.

1.3.1. Open the Computer Certificate Store in MMC.

1.3.2. Navigate to the Personal Certificates. Expand the Intended Purposes column and double-click certificates that are enabled for Server authentication.

1.3.3. Check that the thumb-print matches that in the ERRORLOG file. If not, try another certificate.

1.4. Check the Signature hash algorithm. If it is one of the following, then it will not support TLS 1.2: MD5, SHA224, and SHA512

1.5. If it is one of the weak algorithms, then disable the Server Authentication function of the certificate so SQL Server won't use it.

1.5.1. If the certificate is explicitly specified in SQL Server Configuration Manager, click the Clear button to remove it. Locate the certificate in MMC (see 1.3).

1.5.2. In MMC, right-click the certificate and choose Properties.

Either disable the certificate completely, or you can selectively disable the Server Authentication function of the certificate. Save the changes.

1.6. Restart SQL Server.

The ERRORLOG should now indicate a self-generated certificate is being used.

If the problem is resolved, SQL Server can run fine with the self-signed certificate. If you want a Verisign or other certificate, then you will need to talk to the certificate provider to make sure a strong hash is used that is suitable for TLS 1.2.

If the problem is not resolved, continue with step 2.

2. Check Enabled and Disabled TLS Protocols.

2.1. Check the Background and Basic Upgrade Workflow if not already done. Both the client and server need to be upgraded to enforce TLS 1.2. It may be okay to upgrade the server but leave TLS 1.0 enabled so non-upgraded clients can connect.

2.2. Check the SSL/TLS registry via REGEDIT.

Enabled and Disabled SSL/TLS versions are under the following registry key:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols

There is a Client and Server sub-key for each version of SSL/TLS, with Enabled and Disabled values, e.g.

[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

*** Please note that any non-zero value is take for TRUE. However, 1 is generally preferred over FFFFFFFF (or –1).

2.2. Check that there are no incompatible settings, e.g. on the server TLS 1.2 is enabled and TLS 1.0 is disabled, whereas on the Client the reverse may be true or the client driver may not be upgraded.

2.3. One test you can do is to Set Enabled=0 for TLS 1.2 (and re-enable TLS 1.0 if disabled). Restart SQL Server. This will tell if the issue is related to specifically TLS 1.2 or is a general SSL issue.

3. No Matching cipher Suites

3.1. The client and server TLS versions and cipher suites may be readily examined in the Client Hello and Server Hello packets. The Client Hello packet will advertise all the client cipher suites, while the Server Hello packet will specify one of them. If there are no matching suites, the server will close the connection instead of responding with the Server Hello packet.

3.2. If a network trace is not available, you can check the Functions value under following registry key:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Cryptography\Configuration\Local\SSL\00010002

3.3. If no matching algorithms are found you should contact Microsoft support, but while waiting for the engineer, you can also perform the capture network traces and/or BID traces as specified at 0720 Advanced SSL Data Capture.