Using IIS Crypto to disable TLS 1.0
Update – 2.1.2016 – I would really only use this as a last resort and I highly recommend testing this solution prior to putting it into production. At this time it does not appear that all of SQL’s features fully support disabling TLS 1.0 and you may run into issues with things like Reporting Services(SSRS). Also some third party utilities and backup clients for SQL may require TLS 1.0 to be enabled, so be sure to check with your application vendors.
Also, as of now the PCI compliance date for TLS 1.0 has been pushed back to June 30, 2018.
The Issue:
With all of the recent exploits related to SSL/TLS and any situation that requires some sort of PCI compliance, it is fairly common to disable SSL v3 and TLS 1.0 on your IIS or application servers. However, if you do this on a server that is also hosting MS SQL, you will quickly find out that the MS SQL services will not start after disabling both SSL v3 and TLS 1.0.
In the Event Viewer application logs, you should see something similar to this when SQL fails to start:
“TDSSNIClient initialization failed with error 0x80090331, status code 0x80. Reason: Unable to initialize SSL support. The client and server cannot communicate, because they do not possess a common algorithm.”
“TDSSNIClient initialization failed with error 0x80090331, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. The client and server cannot communicate, because they do not possess a common algorithm.”
“Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.”
“SQL Server could not spawn FRunCommunicationsManager thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.”
The Fix:
To resolve this issue on MS SQL 2012 & 2014 you will need to install the one of the available cumulative update packages, I have been able to test and confirm both CU1 and CU4 for SQL 2014 SP1 and CU6 for SQL 2012 SP2 are all functional. The CU packages can be found here:
https://support.microsoft.com/en-us/kb/3052404
https://www.microsoft.com/en-us/download/details.aspx?id=50402
After the CU package has been installed, disable TLS 1.0, reboot the server and the SQL services should start automatically like normal. Sweet, that was easy right? Except for now when logging into SQL Server Management Studio (SSMS) you get the following error:
“A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 – No process is on the other end of the pipe.) (.Net SqlClient Data Provider)
(Microsoft SQL Server, Error: 233)”
To resolve this error, install .Net 4.6+ which can be found here:
https://www.microsoft.com/en-us/download/details.aspx?id=48130
You may also run into some issues with the SQL Server Agent not starting with something similar to the following in the SQL Agent log:
2016-01-31 00:37:28 – ? [100] Microsoft SQLServerAgent version 12.0.4416.0 (X64 unicode retail build) : Process ID 2044
2016-01-31 00:37:28 – ? [495] The SQL Server Agent startup service account is NT Service\SQLSERVERAGENT.
2016-01-31 00:37:34 – ! [150] SQL Server does not accept the connection (error: 233). Waiting for Sql Server to allow connections. Operation attempted was: Verify Connection On Start.
2016-01-31 00:37:35 – ! [000] Unable to connect to server ‘(local)’; SQLServerAgent cannot start
2016-01-31 00:37:35 – ! [298] SQLServer Error: 233, Shared Memory Provider: No process is on the other end of the pipe. [SQLSTATE 08001]
2016-01-31 00:37:35 – ! [298] SQLServer Error: 233, Client unable to establish connection [SQLSTATE 08001]
2016-01-31 00:37:35 – ! [382] Logon to server ‘(local)’ failed (DisableAgentXPs)
2016-01-31 00:37:35 – ? [098] SQLServerAgent terminated (normally)
To resolve this issue, update the SQL Native Client (SNAC) to the latest version. Now it’s important to note here as mentioned in KB3106660 (Cumulative Update 4 for SQL Server 2014 SP1), that there will not be a 2014 or later version of the Native Client. So you will need the latest 2012 version which is now available as a separate download, but I have also had success pulling the Native Client install out of the SQL 2012 SP2 CU8 update. The latest version can be found here:
https://www.microsoft.com/en-us/download/details.aspx?id=50402
You should now be able to run MS SQL 2012/2014 with support for both TLS 1.1 and 1.2.
For more info on how to disable TLS 1.0, check out this post:
Hardening SSL & TLS connections on Windows Server 2008 R2 & 2012 R2