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.
Now, back to our Reporting Services Issue… SSRS Fails to Connect:
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):
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.
A Firewall or
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:
In the Client Hello - We Send Information that the Server Needs to Understand the TYPE of TLS Request: TLS Version, Cipher Suites etc..
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.
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:
SRSS calls .NET and asks for a TLS Session
.NETtries to use TLS 1.0 even though its Disabled at the system level.
The TCP Session Is Established
The Client Hello is Not Passed (because TLS 1.0 Is disabled)
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: