Have you ever encountered a situation where a process that used to work suddenly stops working? It can be frustrating, especially when you need to repeat that process regularly. In this blog post, we will discuss a common issue related to TLS connection in SQL Server and how to fix it.

The Problem

Recently, a client needed to install an application that required testing the database connection using a utility called minisql.exe. However, this utility only supports TLS 1.0/1.1, while our systems had TLS 1.2 enabled. This created a roadblock as enabling TLS 1.0 on the database server was not an option due to security concerns.

The Workaround

To overcome this issue, we suggested installing a SQL Server Express Edition instance on the application server. This allowed us to bypass the TLS 1.0/1.1 requirement during the application setup. Surprisingly, after the installation, the application worked perfectly fine with any other driver that supported TLS 1.2.

While this workaround solved the immediate problem, it raised questions about the vendor’s decision to have two different ways to test database access, especially when one of them was limited. Perhaps it was a legacy approach or a specific requirement from the application.

Repeating the Process

After obtaining the necessary approvals to temporarily enable TLS 1.0/1.1, we needed to repeat the steps that had previously worked. This involved modifying the registry keys to enable TLS 1.0 and 1.1. Here are the registry keys that needed to be changed:

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Server]
"Enabled"=dword:00000001
"DisabledByDefault"=dword:00000000

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Client]
"Enabled"=dword:00000001
"DisabledByDefault"=dword:00000000

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Server]
"Enabled"=dword:00000001
"DisabledByDefault"=dword:00000000

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Client]
"Enabled"=dword:00000001
"DisabledByDefault"=dword:00000000

After making these changes, a server restart was required. However, even after following these steps, we encountered the following error message:

Connection Problem: [DBNETLIB][ConnectionOpen (SECDoClientHandshake()).]SSL Security error

Identifying the Issue

Since the previous solution didn’t work, we had to dig deeper to understand what could be different this time. After searching online, we found numerous articles discussing the same error message and the registry keys that needed to be modified. However, none of these solutions resolved our issue.

Realizing that we needed a fresh perspective, we reached out to a colleague from the Windows team for assistance. After comparing the registry keys on both servers, we discovered a difference. The most recent server had a “Hashes” folder under “HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL”. This folder contained two subfolders (MD5/SHA) with an “Enabled” entry set to 0.

The Solution

To fix the issue, we backed up the registry keys and then deleted the “Hashes” folder. After restarting the server, the problem was resolved, and the application was able to establish a TLS connection successfully.

It’s important to note that when exporting and importing registry keys, the “Hashes” entries were not included. Therefore, it was necessary to manually delete the folder to ensure a clean configuration.

Automating the Process with PowerShell

If you prefer a more automated approach, you can use PowerShell to disable TLS 1.0 and 1.1. Here are the commands:

New-Item 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Server' -Force | Out-Null
New-ItemProperty -Path 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Server' -Name 'Enabled' -Value '0' -PropertyType 'DWord' -Force | Out-Null
New-ItemProperty -Path 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Server' -Name 'DisabledByDefault' -Value 1 -PropertyType 'DWord' -Force | Out-Null

# Repeat the above commands for TLS 1.0 Client

New-Item 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Server' -Force | Out-Null
New-ItemProperty -Path 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Server' -Name 'Enabled' -Value '0' -PropertyType 'DWord' -Force | Out-Null
New-ItemProperty -Path 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Server' -Name 'DisabledByDefault' -Value 1 -PropertyType 'DWord' -Force | Out-Null

# Repeat the above commands for TLS 1.1 Client

New-Item 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Hashes' -Force | Out-Null
New-Item 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Hashes\MD5' -Force | Out-Null
New-ItemProperty -Path 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Hashes\MD5' -Name 'Enabled' -Value '0' -PropertyType 'DWord' -Force | Out-Null
New-Item 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Hashes\SHA' -Force | Out-Null
New-ItemProperty -Path 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Hashes\SHA' -Name 'Enabled' -Value '0' -PropertyType 'DWord' -Force | Out-Null

Write-Host 'TLS 1.0 and 1.1 have been disabled.'

Remember to revert these changes once you have completed your tests or workaround.

Conclusion

When encountering issues with TLS connections in SQL Server, it’s essential to explore all possible solutions and compare the registry settings between servers. In our case, the presence of the “Hashes” folder was the key difference that needed to be addressed. By understanding the problem and finding the appropriate solution, we were able to overcome the TLS connection issue and ensure the smooth functioning of the application.

Thank you for reading!