Enable SSL on SQL Server Reporting Services with a Wildcard Certificate

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

SecureConnectionLevel

Change the ReportServer_INSTANCENAME to https://+:443

ReportServer

Change the ReportManager URL to https://+:443 and add a second URL for https://mydomain.com:443

ReportManager

Under UrlRoot and ReportServerUrl sections add your report site URL e.g. https://mydomain.com/ReportServer_INSTANCENAME

UrlRoot

ReportServerUrl

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.

SSRSWebServiceURL

Under the Report Manager URL section select advanced and add an SSL identity for the wildcard certificate

SSRSReportManagerURL

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 🙂

13 Replies to “Enable SSL on SQL Server Reporting Services with a Wildcard Certificate”

  1. Thanks for this post. The registry key changes section was the missing bit I needed to get our SSRS website working properly again!

  2. 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.

  3. 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.

  4. Thanks for the write-up. It fixed my issue. Too bad Microsoft can’t give some standard, clear direction on this setup.

  5. 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

  6. 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!

  7. 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

Leave a Reply to Nidhi Cancel reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.