Recently at work I was given the task of enabling SSL on a new SQL Server 2014 Reporting Services server instance.
Previously when securing this sort of thing we would have grabbed an internal single name certificate dished out by our PKI infrastructure, for various other reasons (which I won’t get into) we wanted to use a Wildcard certificate for one of our public top level domains.
Since I had performed this before using single named certificates I figured that the process would be similar if not identical, however it seemed to end up being a bit of a headache with a lot of trail and error, including some research.
First of all I’d like to mention that this topic has been covered on other blogs and forums each offering different suggestions with various levels of success. Combining some of these along with my own findings resulted in a configuration that works in my environment, therefore yours may differ.
Before beginning you must ensure the following:
- A valid and trusted wildcard certificate (This already assumes you have the certificate imported onto the Report server)
- Inbound traffic on TCP Port 443 is opened on the Report Server Firewall (e.g. Windows Firewall)
- The Report Server service is stopped
Stage 1: Remove existing HTTPS 443 reservations and create new ones
If you have tried this previously then there may be existing HTTPS 443 reservations still active on the Report server, to remove these open an Administrative Command Prompt and type the following:
netsh http delete urlacl url=https://+:443/ReportServer/
netsh http delete urlacl url=https://+:443/Reports/
Now we can create new reservations by typing the following:
netsh http add urlacl url=https://mydomain.com:443/ReportServer user=MYSERVER\MyAccout
netsh http add urlacl url=https://mydomain.com:443/Reports user=MYSERVER\MyAccout
Where mydomain.com is the name of your reporting URL and MYSERVER\MyAccount is the name of the service account running the reporting services
Stage 2: Reporting Services Configuration
With the correct reservations in place the next stage is to edit the rsreportserver.config file stored in C:\Program Files\Microsoft SQL Server\MSSRS.INSTANCENAME\Reporting Services\Report Server
Change the SecureConnectionLevel value from 0 to 3
Change the ReportServer_INSTANCENAME to https://+:443
Change the ReportManager URL to https://+:443 and add a second URL for https://mydomain.com:443
Under UrlRoot and ReportServerUrl sections add your report site URL e.g. https://mydomain.com/ReportServer_INSTANCENAME
For the next part we now need to start the report services service again from the Reporting Services Configuration Manager and select the wildcard certificate under the Web Service URL section and hit apply. This will create an IPv4 and IPv6 SSL identity which you can view by hitting the advanced button.
Under the Report Manager URL section select advanced and add an SSL identity for the wildcard certificate
Stage 3: Windows Registry Changes
The third and final part is to modify two Windows Registry keys on the report server
Under HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa create a new DWORD called DisableLoopBackCheck and set it’s value to 1
Under HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\Lsa\FipsAlgorithmPolicy set the Enabled DWORD value to 0 if it isn’t already.
Finally to test this all worked stop and start the report services service again and verify you can reach your report server at https://mydomain.com/ReportServer_INSTANCENAME and https://mydomain.com/Reports
If all was successful then you should see a directory listing from the ReportServer URL and a graphical report view from the Reports URL just like before.
Hopefully this helps anybody struggling with getting SQL Server Reporting Services working with wildcard SSL certificates 🙂
Thanks for this post. The registry key changes section was the missing bit I needed to get our SSRS website working properly again!
Thank you for this blog post. This save my countless hours of research to make it work. I did not do ‘HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\Lsa\FipsAlgorithmPolicy ‘ , but without this, it worked.
Hi, we have tried above steps to access the SSRS Reportserver URL with wildcard certificate but it was unable to load, its showing 503 error.Anything I have missed out here could you please help me.
Hi Rajesh,
This sometimes happens after changing the port and certificate bindings. In most causes I’ve found that simply restarting the SSRS service will start it working again with the new port and certificate bindings.
Thanks for the write-up. It fixed my issue. Too bad Microsoft can’t give some standard, clear direction on this setup.
HI
I Get HTTP Error 404. The requested resource is not found.
Can any one please suggest
Apparently SSRS 2016 doesn’t have any proper re-write functionality? Got it to easily serve up the cert with FQDN but we want to redirect users that have http://server/reports/report1 to https://server.domain.com/reports/report1. IIS rewrite module seems to only work at the apex domain and it still has the port 80 binding so they can get to the http // shortname site still , even though the SSL FQDN does work.
Have struggled with this for days, the registry settings was the missing part. Thanks
Trying to configure PowerBI report Server Sep 2019 version with wildcard cert but getting error “The remote server returned an error: (503) Server Unavailable.” when I try to publish new reports (using upload button)
and get “OData exception occurred: System.Net.WebException: The remote server returned an error: (503) Server Unavailable.” If I access any reports that were published when using port 80
Woohoo! I’ve been struggling with this since yesterday. Your directions had the magic order of steps to get it working for me!
Thank you!
Hi Nidhi;
Did you resolve problem.
Il faut aussi ajouter une entrée dans le DNS si cela n’est pas déjà fait!
Hello Sir,
I’m trying to follow your steps in the SSRS 2019 fresh install.
But I get the error when I try to start SSRS Service after completing all the steps.
Here is the error:
System.InvalidOperationException: Cannot start service SQLServerReportingServices on computer ‘SSRS_SERVERNAME’. —> System.ComponentModel.Win32Exception: The service did not respond to the start or control request in a timely fashion
— End of inner exception stack trace —
at System.ServiceProcess.ServiceController.Start(String[] args)
at ReportServicesConfigUI.Panels.ConfigurationPanelWithErrors.StartOrStopServiceTask(Boolean start, String serviceName)
Any Idea what could be wrong here??
Greatly appreciate your help.
Wesley
I followed your steps on a clean SQL 2019 install and when I go to my url: rsl.mycompany.com/ReportServer I get a login popup, I enter my user name and password and I get: Microsoft SQL Server Reporting Services Version 15.0.1102.1167. But when I try: rsl.mycompany.com/Reports I get: HTTP Error 503. The service is unavailable.
Can anyone please tell me what I missed?
I did this three times with another sql server already being used for testing before I build a freach new one and got the same thing…