Microsoft Secure Tech Accelerator
Apr 03 2024, 07:00 AM - 11:00 AM (PDT)
Microsoft Tech Community
TLS and SQL Server Reporting Services: An Interesting Interaction
Published Jun 23 2021 10:10 AM 12K Views
Microsoft

 

Our guest blogger, Lex Thomas, writes about ASCENT cases that are escalated and require networking expertise, usually with tracing.  He joins us today to hopefully save everyone some troubleshooting time.  Edited with added context by Jarred Mooney, Customer Engineer.


Today I had an Issue that I want to share because I am hearing that it’s widespread.  SQL Reporting Service (SSRS) “Fails to Connect”, but in this case the cause can take a while to track down.  
 

In today’s security-minded environment companies are disabling support for TLS 1.0 and 1.1 and forcing TLS 1.2/1.3.  

 

That’s a great thing.  

 

But I have run into several situations where applications seem to break, and unless you understand the correlation between TLS restrictions being added and applications failing to connect to SQL Server Reporting Services, it’s easy to miss this one. 
 

Setup: 
I have added the registry keys to disable TLS, so if you do not have them, you do now. 
Here is a decent Article on doing that. 
 
Transport Layer Security (TLS) registry settings | Microsoft Docs 

 

Now, back to our Reporting Services Issue… 
 
SSRS Fails to Connect: 

JarredMooney_0-1624464977515.png

 

 

I got an Ascent Case and the issue appeared to be networking. Generally, when I hear the words “Can’t Connect,” I Immediately think Firewall or issue with a target service and I ask for a network trace. 

 

That’s exactly what I asked for here.    

 

Here is what I Look For, and why: 

First, we have to Be able to Connect Via TCP.  That means we have be able to do a 3 Way Hand shake: (Here is what that looks like): 
 

JarredMooney_1-1624464977507.png

 

 
 
The Client Sends a SYN request to the Endpoint  ( ….S…. ) 

The Server Sends and ACK SYN (..A….S… ) 

And the Client Responds With an ACK (….A…..) 

 

If that fails, we have one of 2 possibilities. 

  1.  A Firewall or
  2.  The service we want to talk to is not running on the box at that IP address. 

 
Second, we Need to Negotiate TLS.  When that’s Successful, It looks like this: 
 

JarredMooney_2-1624464977510.png

 

  
In the Client Hello -  We Send  Information that the Server Needs to Understand the TYPE of TLS Request:  TLS Version,  Cipher Suites etc.. 

JarredMooney_3-1624464977511.png

 

 

 

If the TCP Session succeeds, and the TLS Session Negotiation is successful, we connect! 

 

Now the Strange Part: 
When SSRS Fails in this specific case, you can see below that it opens the TCP session and immediately closes it 

 

Notice No TLS Handshake. 

JarredMooney_4-1624464977516.png

Why Does This Happen? 
This happens because an application it trying to USE a TLS/SSL Version that is disabled. 

In the above example, SSRS can connect to the target, TCP Works! But it immediately tears the TCP session down because the application is TRYING TO FORCE TLS 1.0. 

TLS 1.0 is disabled on this box, so the Client Hello never gets sent and the application “SRSS” logs this as a Connection Failure. 
 
It’s Important to understand that the TCP Connection worked but the TLS SESSION failed. 

So again, why did this Happen? 
It happened because in this case SRSS was using  an older version of .NET Framework.   
.NET Framework negotiates TLS/SSL independently of the O.S. by Default (at least this version), and I suspect anything written earlier than a couple years ago does the same. 
 
So in this case: 

  1. SRSS calls .NET and asks for a TLS Session 
  1. .NET tries to use TLS 1.0 even though its Disabled at the system level. 
  1. The TCP Session Is Established 
  1. The Client Hello is Not Passed (because TLS 1.0 Is disabled) 
  1. The TCP Session Gets Torn Down. 

 

HOW TO FIX: 

Note: While enabling TLS 1.0 would work here, it’s not the correct solution and should be avoided..) 

 

Here is the Correct Solution: 

Add SystemDefaultTLSVersions and Set that DWORD to 1.  This instructs .NET to use the system-defined TLS Settings. The registry entries look similar to this (depending on your .NETFramework versions): 
 
For 64-bit Apps: 
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\v4.0.30319] – “SystemDefaultTlsVersions”=dword:00000001 

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\v2.0.50727] – “SystemDefaultTlsVersions”=dword:00000001 
 
For 32-bit Apps: 

[HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\.NETFramework\v2.0.50727] – “SystemDefaultTlsVersions”=dword:00000001 
[HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\.NETFramework\v4.0.30319] – “SystemDefaultTlsVersions”=dword:00000001 

 

Making this change will allow .NET Apps to use the OS-Level TLS Settings. 

 

There you have it!

3 Comments
Co-Authors
Version history
Last update:
‎Jun 23 2021 10:10 AM
Updated by: