On one of my web server, after installing an SSL certificate, I thought of hardening the web security by implementing SSL/TLS Deployment Best Practices. The server has both IIS and SQL Server running on it. Based on the findings from the SSL Server Test tool, I have used the IIS Crypto software to disable the SSL 3.0 and TLS 1.0 protocols. After disabling the protocols and restarting the server, my website stopped working. On analyzing the website log entries and events, I figured out that the SQL Server service was not starting, I learned in a hard way that disabling TLS 1.0 And SSL 3.0 affects SQL Server 2014. Lets go to the issue in detail.
Disabling TLS 1.0 And SSL 3.0
My web server is Windows Server 2012 R2 and has SQL Server 2014. I have used IIS Crypto software to disable the SSL 3.0 and TLS 1.0 protocols.
Impact On SQL Server 2014
After restarting the server the website was not working. The web site log has entries related to unable to connect the SQL Server. On trying to login to the SQL server using SQL Server Management Studio (SSMS), I got an error message saying that the server is not available. So, I checked the SQL Server Service and found that it is not yet started. I manually tried starting the server with no success. Finally, on checking the event logs, I found a series or error messages. Some of the errors are given below.
Log Name: Application Source: MSSQLSERVER Date: 11/22/2017 4:45:00 AM Event ID: 26011 Task Category: Server Level: Error Keywords: Classic User: N/A Description: The server was unable to initialize encryption because of a problem with a security library. The security library may be missing. Verify that security.dll exists on the system. Log Name: Application Source: MSSQLSERVER Date: 11/22/2017 4:45:00 AM Event ID: 17182 Task Category: Server Level: Error Keywords: Classic User: N/A Description: TDSSNIClient initialization failed with error 0x139f, status code 0x80. Reason: Unable to initialize SSL support. The group or resource is not in the correct state to perform the requested operation. Log Name: Application Source: MSSQLSERVER Date: 11/22/2017 4:45:00 AM Event ID: 17826 Task Category: Server Level: Error Keywords: Classic User: N/A Description: 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. Log Name: Application Source: MSSQLSERVER Date: 11/22/2017 4:45:00 AM Event ID: 17120 Task Category: Server Level: Error Keywords: Classic User: N/A Description: SQL Server could not spawn FRunCommunicationsManager thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.
As a temporary solution, I quickly enabled SSL 3.0 and TLS 1.0 protocols and restarted the server to bring back the website up and running. Then on looking for a permanent solution, I have identified tree solutions. I have listed the solutions below so as it will be helpful for someone.
1. Segregate SQL Server and IIS Server
The best solution is to segregate the SQL Server from IIS server. Segregating SQL Server from IIS Server is a best practice for security and performance of the website. However, it is not be always possible to have a separate server for MS SQL. Budget constraints may force you to have a single server with both SQL Server and IIS. In such a scenario, you can try the below solutions.
2. Upgrade the SQL Server to a TLS 1.2 supported version
Another solution is to upgrade to SQL Server 2016 or higher which by default supports TLS 1.2. You may also need to install .NET Framework 4.6 or higher. (I chose this option for my server as it has less impact and viable in my case)
3. Enable TLS 1.2 support for SQL Server 2012 or 2014
The above solution may not always possible for everyone. If you are planning to find a solution without upgrading SQL Server to 2016 or having a separate sever for MS SQL, then you may need to follow the Microsoft article https://support.microsoft.com/en-in/help/3135244/tls-1-2-support-for-microsoft-sql-server. This article will help you to find the cumulative update and other components needed for every version of SQL Server and install them to support TLS 1.2. The Microsoft support article will guide you through the process of patches and updates you need to install. Below is the highlight of the article. (NOTE: I will not recommend this solution because of some known issues. You can see the list of known issues in the above mentioned Microsoft article itself. Go through the issues and decide whether this solution is for you.)
- Based on your SQL Server and service package version, you have to select and install the SQL Server Cumulative Update from this list under the heading how to know whether you need this update.
- Then you have to choose the client component downloads based on your environment whether you are using SQL Server Native Client or ADO.NET SqlClient.
- Finally install .NET Framework 4.6. This is to make the SSMS, SSRS and report manager to connect with SQL Server Engine.
Hope this will help you to secure your web server with TLS 1.2.