In the blog post, I will share a case of troubleshooting a connectivity issue with SQL Server.

The problem occurred in a migration context. The SQL Server databases (in version 2008 to 2014) from multiple applications were consolidated on a new server with SQL Server 2019. Application servers were also migrated to new VMs.
The new servers are hosted on a brand new infrastructure managed by a third-party provider which my customer has very little knowledge about.

Problem

Before I actually connect and have a look at the servers the only information I had, as it’s often the case, was:

The application server can’t connect to the new SQL Server.

My first thought when it comes to a client connection problem is firewall blocking, a network configuration issue on the MSSQL server, or even a simple authentication problem.

So, in a screen share session, I asked my customer (who is not a DBA) to show me exactly what he is doing so I can understand the issue.

What he did was to open the ODBC Data Sources tool on the application server to try a connection to the SQL Server instance on the remote MSSQL server.
Several attempts prompted different errors, but it mostly looked like this:

The keywords are “ConnectionOpen (SECDoClientHandshake())” and “SSL Security Error”.

 Troubleshooting

So the first thing I did was to check the connectivity between both servers on the TCP Port the SQL Server instance is listening on.
After I excluded any Firewall issue or SQL Server configuration issue I used my best Google-fu and I started to check the TLS configuration.
So I went to the Windows Registry to look for any Keys under

HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\

Here is what I found on both the application and the SQL server:

These keys do not exist by default on Windows. When created as shown in the screenshot they disable TLS 1.0 and TLS 1.1 protocols and implicitly force the use of TLS 1.2.

So TLS 1.2 needs to be used. SQL Server 2019 of course supports TLS 1.2 so the issue is certainly client-side.

The problem here is the use of the SQL Server ODBC driver version 10.00.14393 not supporting TLS 1.2.

To help troubleshooting, you can use this kind of simple query to get information about the current connections on an SQL Server instance, I’ve used it to compare the results for the client_interface_name column on different servers.

SELECT   
    c.session_id, c.net_transport, c.encrypt_option,   
    c.auth_scheme, s.host_name, s.program_name,   
    s.client_interface_name, s.login_name, s.nt_domain,   
    s.nt_user_name, s.original_login_name, c.connect_time,   
    s.login_time   
FROM sys.dm_exec_connections AS c  
	JOIN sys.dm_exec_sessions AS s  
		ON c.session_id = s.session_id;

 

Also, the Microsoft SQL Server Tiger Team published a PowerShell script to check if driver updates are required to use TLS 1.2.
It has not been updated lately so I’m not sure if it’s still useful but it’s a good starting point if you want to automate the check of your SQL clients versions.

Resolution

Installing and using the latest ODBC driver for SQL Server on the application server allows for a TLS 1.2 connection to SQL Server.
I hope this can help you troubleshoot this kind of connectivity error.