Introduction

TLS 1.2 was introduced as far back as 2009 as an improvement to previous protocols created to ensure connections. TLS 1.2 was advertised to be a more secure option than its predecessors, SSL versions 1, 2 and 3 as well as TLS1.0 and 1.1. In mid-2018 it became imperative to ensure that all systems within our environment were TLS 1.2 compliant. We had a number of experiences in this exercise one of which was the impact on Transaction Log Shipping. This article intends to share the experience in some level of detail.

Transaction Log Shipping Scenario

In our environment, we run AlwayOn Failover Cluster Instances at two Data Centres and meet our DR requirements using Transaction Log Shipping. Backup, Copy and Restore Jobs are configured to run at ten-minute intervals starting at midnight. All references to the instances are made using Virtual Names – instances, backup paths, copy paths, etc.

After implementing TLS1.2 on the operating system level, we found among other things that the Log Shipping Copy Job failed intermittently. Some digging in the Job History exposed the cause of the failures as connection problems between the SQL Server Agent on the Secondary Clustered instance and the Secondary Clustered Instance itself. The interesting part of the problem was that when the job is executed manually, the job succeeds! A possible reason for this is suggested later in the article.

Fig. 1 SSL Errors in Log Shipping Jobs

Remediation Attempts

Fig 1 shows the key error we found in the job history. An analysis of this error showed that there was a mismatch somewhere with respect to the supported protocols.

Our first action therefore was to ensure that we had exactly the same configuration on the Operating System with respect to enabled protocols. This Microsoft article gives details of (among other thing) what can be done to enable or disable TLS 1.2 support from Windows Registry, but we took the easy part using the tool known as IIS Crypto, which is available for download here.

IIS Crypto gives a GUI which makes it easy to identify which security protocols and ciphers are enabled. Using the checkboxes on the tool, it is possible to accomplish what manipulating the registry keys at HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols does.

# -- Listing 1
Get-ItemProperty -Path 'Registry::HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Server'
Get-ItemProperty -Path 'Registry::HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Server'
Get-ItemProperty -Path 'Registry::HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Server'

You can see the results of these calls.

Fig. 2 Querying Registry Keys for TLS version

Here is the IISCrypto Interface:

Fig. 3 Enabling TLS 1.2 Support Only Using IISCrypto

Incompatible Clients

Another interesting challenge we had was the number of colleagues in the Application Support and Development Teams who complained about a similar error to what we saw in our Log Shipping Jobs. Of course, we now had experience and we were able to pin point the issue right away but, there was more to it. With a little help from Microsoft, we realized we also needed to apply very specific patches to specific clients ranging from ODBC to Native client in order for these clients to support TLS 1.2 (yeah. I hear you say, “Read the support notes when they are published not when you have issues”). The description of the patches are in the URL shared earlier: TLS 1.2 Support for Microsoft SQL Server.

We actually had cases where on the surface it seemed clients were running .NET 4 which is compatible with TLS 1.2 by design but upon closer examination we discovered that earlier versions of .NET were indeed installed on the server in such cases and needed to be updated. This Github Repository has a PowerShell Script which can be used to confirm whether any clients running on your server need a TLS 1.2. patch. We used the it to isolate the offending client in a number of cases.

ODBC Driver 13 for SQL Server

In my environment it is typical to confirm connectivity to a SQL instance by creating a Data Source Name with ODBC. During this TLS 1.2 drama as expected previous versions of ODBC did not work and we also had to upgrade. We did this “upgrade” by installing SQL Server Management Studio 17 which comes with ODBC 13 not by using the patch specified by Microsoft. Using this version when performing ODBC Tests circumvented errors were previously getting from clients.

Fig. 4a Adding a DSN Using ODBC 13

Fig. 4b Adding a DSN Using ODBC 13

Conclusion

In summary, Microsoft provides detailed documentation and support for implementing TLS 1.2 support successfully. Starting at this article: TLS 1.2 Support for Microsoft SQL Server, you can work your way through complying with the current security requirements in this regard. And when you do encounter “Handshake” errors, you will more likely have a  good idea where they may be coming from.