I know a lot of folks who like to hate-on SSRS. I feel like it is mostly unfounded. Well um, except when you are setting up a new SSRS instance. It will try to steal your joy, but I’ve got your back.
My last blog post talked about 3 kinda-stupid things about installing SSRS and setting it up.
…but none of those are as soul-sucking as the problems which can be caused by SSL/TLS. Buckle-up.
The problem / Symptoms
Last time I faced the crucible of SSRS installation & configuration, I completed the installation unharmed. Next I configured permissions, installed some RDL files, configured DB connections and was able to run some reports on localhost. 
I was about to reach for a cold-one, to celebrate, but first, I thought I should check connectivity from my app/web server to the new SSRS instance. Not-so-fast! You didn’t think it would be that easy, did you?
Okay, yeah. For me, stuff doesn’t usually *just work* on the first try. No biggie. This isn’t my first dance. So I applied some good troubleshooting strategies by isolating/reducing the problem. I started simply by running a report the simplest way, and then adding factors/complexity:
1) Use a web browser to check SSRS from localhost (RDP onto the SSRS host server) = OK
2) Use a web browser to check SSRS from another server (app/web server via RDP) = OK
3) Configure an app (on the app/web server) to point to the new SSRS server = #Fail!
4) Take a step-back, deconstruct this issue by using Visual Studio (from my PC, not the server) to step-debug through the code, which is calling SSRS, and see what errors/exceptions are thrown. = WTH! It works from Visual Studio?!
?) … Regroup. Scratch my head. Walk-it-off. Check S/O. More head scratching…
 | [ Legend ]
Other = apps tried |
So yeah. The only place it doesn’t work, is from my (Test/Dev) server to the new SSRS. Now what?!
I’m a developer. I own a golden hammer. So naturally I start looking for nails to pound: I add a lot more error handling, tracing, logging, etc. to the program running on the server, so I can get more insight into what is going-wrong.
I am also a good software architect. So I make a mini app, to reduce the problem and only demonstrate the issue on-the-smallest-scale-possible. I minimize it to a single-page app that calls one report. It has way more trace & setting sniffing code than actual running code. I also (temporarily) loosen the permissions & security as-much-as-possible, in case that is the issue. Focus on getting more details about the error message, stack trace, anything & everything.
I was feeling pretty optimistic and pretty cool. My genius-level strategy was going to have the bug cornered and caught off-guard. Ha. Wait for it. 4 – 3 – 2 – 1. Wait, what? The error just says “Could not create SSL/TLS secure channel”. That’s all. Well that sounds stupid because I totally can connect on https from a web browser. So n/w routing/fw, tcp, etc on the server must be okay. The certs must be fine too (matching names, trusted issuer, akimet, etc). Plus my local VStudio connected, so it can’t be a shortcoming of .net.
I’ll save you the pain of reading about my 2-3 weeks of back & forth. I will say it was kinda agnonizing.
Breakthough
The next-level troubleshooting that saved the day, was when an infrastructure guy recommended I use OpenSSL to check the SSL/TLS connection from the app/web server to the SSRS server. (download link) *
The command that I used to troubleshoot my connection was OpenSSL> s_client -host [my-new-ssrs.subdomain.domain.com] -port 443
It piped a bu-u-u-unch of stuff to the screen: root cert info, cert chain, server public cert, cert header info, SSL handshake info, cert cipher info, SSL-Session info. Maybe 90+ lines.
Ultimately, the problem stemmed from the new SSRS server (new OS too) having newer ciphers than our (not-so-new) app/web servers. Our security team was vigilant enough to disable those rusty-old ciphers that nobody-should-be-using-anymore on our fresh new servers, but didn’t exactly warn us about it.
In case you aren’t sure what I’m talking about, in the OpenSSL section (around line 60, like) “TLSv1.2, Cipher is ECDHE-R2D2-AES123-C3PO-SHA456” it sorts-out the cipher into 5 segments
* Key exchange (ECDH)
* Authentication (RSA)
* Block ciphers, session cipher (AES)
* Type of encryption (GCM cipher-block)
* Hash function / message auth (SHA/MD5)
There are a few OS tools which can look-up the list of ciphers installed. The really subtle part about it is the myriad of combinations that you can make from seemingly-minor variations in each of these 5 segments. However BOTH SERVERS (ssrs and app/web) MUST HAVE at-least-one suite that they both can agree upon. “Pretty close” won’t work. eg. AES128 is not AES 256. ECDHE is not ECDH.
I know it sounds stupid-obvious, like “why wouldn’t my servers have every cipher imaginable” and “um, shouldn’t those monthly updates just install the latest ciphers, like duh!” IKR. But no. Older servers don’t just get cool new ciphers when they come out. New servers have some of the old ciphers but your security guys get paid the-big-bucks to remove crusty old ciphers, so we all get to rock-n-roll with those spiffy new bling-bling ciphers. ooh! aah!
Mystery Solved
So, if the problem was “missing ciphers on the server” then why did it work from a web browser on the server (RDP), but not from the app on the server? Well, oddly, several web browsers have their own cipher suites built into the browser. So it doesn’t rely upon the OS. Users love it when their way-cool browser just works, even when the OS doesn’t. The support folks for Chrome/Firefox/Edge/Brave don’t want to troubleshoot your OS. So for them, NMFP as long as the browser works. So, yeah. Browsers are not always valid testing tools. Lesson learned.
But now you know. And hopefully TLS will not be successful at ruining your day, because you know what to look for now.
* Note: The download for OpenSSL is pretty heavy (66MB) and contains a bunch of stuff that you don’t need for this kind of troubleshooting. What you do need is the file openssl.exe. It will open a command window.
Aah, the pain of being an elitist 