Some basic questions about SQL and TLS 1.0 and 1.2

Question

(I don't believe this is a duplicate of any other TLS-related question, like the authoritative question about SQL Compatibility with TLS or its many duplicates, or questions about specific TLS-related issues you might encounter with SQL CLRs or Classic ASP or SSRS or whatever. This question is a bit more broad.)

All our SQL servers (SQL 2012-2017) are on recent patch levels that support TLS 1.2 (see first link above), and yet when we disabled TLS 1.0 on our SQL servers, nearly all our applications broke (SharePoint farms, web apps, dot.net apps, PowerShell scripts, many vendor apps, heck, even some SQL jobs running on the same box failed).

I thought that clients already tried to negotiate the most secure encryption protocol that both the app and SQL support (TLS 1.2), but that's clearly not the case here.

So... what am I missing? Is there a master switch at the Windows OS level on our application servers that was never flipped to tell applications they should start using TLS 1.2? (We run Windows 2012 R2 and 2016.) Are there different registry entries for enabling "client" TLS 1.2 vs enabling "server" TLS 1.2?

I believe MS components like .Net are up to date as well. I know we will eventually need to look at individual applications, but I can't help but think there is something more obvious going on here.

Also, is there a SQL query that can tell me what connection method was attempted by each application (none vs TLS 1.0 vs TLS 1.2)? This page suggests encrypt_option from sys.dm_exec_connections, but that appears to be a boolean, not one that tells me the type (plus all my rows show FALSE, so I don't think that's right).

Update: Added bounty for more attention. I have determined that at least a couple of our older SQL servers are Windows 2012 (not R2), which (I believe) does not have TLS 1.2 enabled by default, where it should be for Windows 2012 R2 and later?? Is that right?

Also still interested if anyone has a query and/or other method to determine what protocols different clients are using when they attempt their first connection to SQL; or would that be a network-layer tool?

Answer 1

This might be different for each client, so you need to test them separately.

First of all for most clients (since they use SChannel) your Windows machines need to be recent enough to support TLSv1.2 (I.e. Server 2008 R2 and later). For those you also need to make sure TLSv1.2 is actually turned on in the Schannel registry:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client
    Dword: DisabledByDefault = 0

Some clients might need client software updates, this includes a certain minimum .Net Framework (before 4.6 all need patches) as well as a specific SQL Server Native Client version.

And also, if you have a Java application the JVM must actually be recent in order to be TLSv1.2 able (and have it enabled by default). Some Java applications might turn the TLSv1.2 protocol off despite it beeing enabled in Java 8, then you need to check the application.

But in regards to your assumption it requires other things to be negotiated, I don’t think so: in all cases TLSv1.2 will be negotiated if it is supported on both sides. If you are sure your SQL Server is capable then it’s the client configuration.

The TLSv1.2 KB article lists quite a few known issues and required patches (including client requirements):

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

In some cases you might also need to exchange provider=SQLOLEDB with SQLNCLI11.