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 🙂

Leave a Reply

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

*