Determine TLS version from established SqlConnection

Question

In a live system, we are making multiple connections to various MSSQL servers using the SqlConnection object in C#.

As a client is in the process of updating their SQL servers to TLS 1.2, I would like to know if there is a way to determine which implementation of TLS is in use on an active connection.

I would like to achieve this entirely in C# if possible, but can use alternative languages so long as the end result is a self contained application.

The purpose of this is to provide the client with a quick test tool which will help in ensuring that the software is working correctly with TLS 1.2 on an integration environment before the changes are made to the live system.

I have spent several hours looking into this, but am so far unable to find anything helpful. All the resources I have found are aimed at ASP developers, and do not go into detail on connecting directly to the SQL server in a desktop application.

The application is using .NET 4.5, so TLS 1.2 should be available.

Many thanks

Fusspot

I don't think there's a way to determine the TLS version in use by a connection from within SQL Server. You might have to resort to packet capture to determine the TLS version. – Engage

This is what I was worried about. If the security is negotiated on the transport layer, the connection object is pretty unlikely to be able to see what security protocol is in use, it either works or doesn't. – Fusspot

@EdHarper - Thank you for your help, you were indeed correct. I have summarily posted an answer to this effect, but it you can summarise this in an answer yourself, I will accept it as the correct solution. Many thanks. – Fusspot

I think you should accept your own answer - after all you've done the work to prove my speculation. – Engage

@EdHarper - True, but credit where it's due, you did support what I thought would be the case which saved me a great many hours of testing! I've accepted my own answer, but if you do want to post one to the same effect, I will accept that as the solution. Thanks for your help. – Fusspot

Answer 1

For the benefit of anybody who stumbles accross this question looking for a solution to the same problem, the comment posted by Ed Harper is correct - I don't think there's a way to determine the TLS version in use by a connection from within SQL Server. You might have to resort to packet capture to determine the TLS version.

When using .NET 4.5 and above, TLS 1.2 is supported, and if a connection is established to a service which explicitly specifies TLS 1.2, the connection should work correctly.

As TLS applies to the transport OSI layer, packet capture is the correct way to confirm the TLS version, as described in the link also posted by Ed Harper - https://networkengineering.stackexchange.com/questions/20227/find-ssl-version-in-tcp-packets-in-established-tcp-connection

As our code is written for .NET 4.5, the switch to enforcing TLS 1.2 did not require any code changes.

Fusspot

It is now possible to get the TLS version from the SQL Server. support.microsoft.com/en-au/help/3191296/… – Flintlock

Answer 2

You can get the TLS version for existing connection with extended events

https://support.microsoft.com/en-au/help/3191296/update-extends-the-trace-extended-event-with-security-protocol-handsha

Flintlock

See the note at the very end of that article; this is possible to do with extended events only on SQL 2016 and later. For prior versions (2012 and 2014), the underlying hooks might have been added by those patches, but you have to use a much more difficult and obscure tool ("Data Access Tracing") to capture them. – Tickle

Answer 3

You can have your C# application pull the product version with query below:

SELECT SERVERPROPERTY('ProductVersion'), SERVERPROPERTY('ProductLevel')

Then, compare this version with minimum versions from Microsoft for each product level/version (you'll have to customize the method to check if the build version is at least as high for each level).

SQL Server release - First build that supports TLS 1.2

SQL Server 2014 SP1 - 12.0.4439.1
SQL Server 2014 SP1 GDR - 12.0.4219.0
SQL Server 2014 RTM - 12.0.2564.0
SQL Server 2014 RTM GDR - 12.0.2271.0
SQL Server 2012 SP3 GDR - 11.0.6216.27
SQL Server 2012 SP3 - 11.0.6518.0
SQL Server 2012 SP2 GDR - 11.0.5352.0
SQL Server 2012 SP2 - 11.0.5644.2
SQL Server 2008 R2 SP3 - 10.50.6542.0
SQL Server 2008 R2 SP2 GDR (IA-64 only) - 10.50.4047.0
SQL Server 2008 R2 SP2 (IA-64 only) - 10.50.4344.0
SQL Server 2008 SP4 - 10.0.6547.0
SQL Server 2008 SP3 GDR (IA-64 only) - 10.0.5545.0
SQL Server 2008 SP3 (IA-64 only) - 10.0.5896.0

Source: https://support.microsoft.com/en-us/help/3135244/tls-1.2-support-for-microsoft-sql-server

Doggy

I did wonder if there was a way to do this pulling product versions, however this will only really tell me whether the SQL server supports TLS 1.2, rather than whether it is being used for a connection. We know the servers will support TLS 1.2 as they are being patched specifically for that purpose. – Fusspot