4

(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?

  • 1
    The only way I'm currently aware of tracking which TLS version is being used for a connection is via the Windows System Event Log (e.g. eventvwr.msc) filtering it down to SCHANNEL and SCHANNEL-EVENTS event sources. By default I think it only tracks failures as well, so if you want to track which TLS version all connections are using you must make a registry setting change, which is outlined here: blogs.technet.microsoft.com/kevinjustin/2017/11/08/… – John Eisbrener Jun 10 at 21:05
1
+300

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.

  • Yes, I'm aware of that KB article (here is the US-EN version), all my SQL builds are up to date, but that doesn't solve the entirety of the problem. Regarding your comment about Windows Server 2008 vs R2 and later; do you mean that only Windows 2008 R2 and above will support TLS 1.2 at all? Or that only R2 and above support it by default and prior versions of Windows need the registry keys set to enable it? – BradC Jun 10 at 13:55
  • 2008(sp2) requires a patch to support tls1.2. Most older OS require registry changes to turn on TLS1.2 in Schannel. And in addition to that you need the client and .net updates as mentioned in the article (the article references required net2.0 patches indirectly) – eckes Jun 10 at 14:00
  • Ah, sorry, I was thinking about Windows Server 2012 vs 2012R2: this MSDN blog article says that TLS 1.1 and 1.2 are enabled by default on Windows 2012r2 and later, but must be enabled by registry key in Windows 2012 and prior. It also appear to provide a way to manually check, via IE options dialog. – BradC Jun 10 at 15:28
  • You could add some concrete examples to your question, like what client, what application, what client, framework and os Version, what registry settings, which sql Server and what outcome. Then people can dig up the config/patches you might be missing (for each). But the general idea is all combinations should be made possible to work. – eckes Jun 10 at 16:00
  • Thanks, eckes. The problem is that I want to have a better understanding of the layout of the forest before I dive back into the detail of each problematic tree, which is what this question is for. The os-level setting isn't the end of the discussion, but its an important beginning. I'm also still interested in knowing if there are SQL queries or something similar that would tell me what protocols (if any) are currently being used by different clients. – BradC Jun 10 at 18:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.