Tuesday, March 14, 2017

SQL Server SSL Connectivity Issue



I'm sure most of you may face different types of error while connecting to SQL Server via Management Studio or ODBC (or different methods). Exactly same thing happened in my client side when we encountered with the following:

SSL Security Error
SSL Security Error
 
Problem Description:

After monthly scheduled restart of one Application Server, the connectivity to Database Server from .Net Client was unable to establish connection to our Database Server. In addition, tried from connecting through ODBC that shows the error message which I mentioned above.

Application Server: Windows Server 2012 R2
Database Server: SQL Server 2005

Troubleshooting / Solution:

As the errors states "SSL Security error", it was pointing to something in the registry level encryption. After hours of troubleshooting (almost a day) and visiting 50+ blogs, we were able to solve this connectivity issue. 

Some observations while troubleshooting are as follows:

      a. This problem was specific to SQL Server 2005 version only because we tried the connectivity from Application Server to different version of SQL Server like 2008 and 2012 that was successfully able to connect. 

      b. TLS 1.0 in registry was not found. You can check here for this error in detail here

      c. No traces found in Windows Event Viewer as well as SQL Server Error Log.

      d. Tried playing with the dbnetlib.dll files too (May be for some this solution has worked).

      e. Installed SQL Server 2005 specific drives and tried connecting through ODBC.
       
      f. Raised this concerned with Microsoft. Since also the extended support for SQL Server 2005 ended last year on April 2016, Microsoft denied to support.

Last week there was some patching at Windows level. Due to this, two new entries (Triple DES & AES) were found in Ciphers on the below location:

Registry
After this discovery, we checked the connectivity by disabling both this algorithms. However, the issue still existed. Finally, tried the connectivity by deleting these algorithms and this time we were lucky and we were able to establish the connectivity to SQL Server 2005 again.

Thank you for reading. You can leave the feedback in the below comments space and stay tuned for more articles on SQL Server.