Reporting Services is a really useful way of designing reports for data extracted from SQL Server databases, which can be exported to various formats and run on a schedule. It is, however, slightly rough around the edges and takes a bit more effort than it used to, even when installed from SQL Server 2012 SP2!

This post describes what I had to do to get it working over HTTPS with a wildcard certificate but because I ended up doing so much, I don't know which bits related to the cert and which related to other errors and compatibility issues!

Installation

Installation is directly from the SQL Server CD. I had some problems with SP1 of SQL Server 2012 where some kind of broken installation could never be recovered but SQL Server 2012 with SP2 seemed to be fine once I ran an MS fix tool to uninstall all the old stuff. If this happens to you, the setup files install and then nothing more happens!

In my case, I was installing the databases and report server on the same machine (see my previous post for why!) so I chose to install the Database Engine, Reporting Services and the full set of Management Tools. I then just chose the defaults for the instance name and also told it to configure the report server as well.

Broken

So then I ran up the Reporting Services Configuration Manager and went through and set the URLs for the report server and report manager to he https and use the wildcard sll cert that was already installed on the box. I then visited both URLs from a client browser (you MUST use Internet Explorer to work properly) and the ReportServer endpoint looked OK but the Reports (report manager) was blank and said there was an Internal Server Error.

Fixing It

The first thing to look for are the error logs, which are NOT in the Windows Event Log but are written to file in a folder under the SQL Server installation, in my case: C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\LogFiles and they begin with ReportServerService and then have a date and time added onto the end - just look for the one that is modified most recently.

Problem 1 - FIPS compliance

FIPS is a scheme which verifies that encryption-type libraries have been verified secure and correct. More recent versions of Windows contain these new libraries and more importantly, a registry switch which will block an attempt to use a non-FIPS compliant encryption library. Guess what? Reporting Services does and for some reason, this hasn't been updated sadly. The error you get at the bottom of the log is something like: "This implementation is not part of the Windows Platform FIPS validated cryptographic algorithms".

There are two claimed fixes, although the first didn't seem to work. The one that didn't work suggested telling the report server to use triple-des instead of AES for machine key encryption by adding the following line into C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\web.config


I restarted the service (as you should do after anything you change to be sure) and it didn't seem to fix it. The second workaround involves disabling the FIPs compliant requirement in the registry, which seems a bit severe but shouldn't cause any real problems in most cases. You do this by running regedit and modifying the key HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\FipsAlgorithmPolicy and set Enabled = 0. Again, restart the report server service (easiest to do this from the SQL Server Configuration Manager)

Problem 2 - Wildcard Certificates and SSL

I don't know if this problem is specifically about wildcard certificates or SSL in general but one of the errors I found in the log was about not being able to establish a trust relationship. What is happening is that when you visit the report manager page, under the covers, other web services are being called and by default, they are accessed using "localhost", which is fine unless you are using SSL which requires a domain name to match to the SSL certificate.

Fixing this is reasonable easy. Open your report server configuration file: C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\rsreportserver.config and set SecureConnectionLevel to 3 instead of 1 (not sure why!). Also, set the value of ReportServerUrl and UrlRoot elements to the same value which should be a fully-qualified domain name such as https://myfqdn.compmany.com/ReportServer, which will cause the web application to use this FQDN instead of localhost to access the web service.

Double-check your setup in the Reporting Services Configuration Manager for the endpoints. I used the advanced button on both to just set an SSL endpoint. Even though I run IIS on the same box, it seems to work OK. It ends up looking something like https://+:443/ReportServer and https://+:443/Reports

Problem 3 - Permissions

At this point, I could reach the report manager endpoint and it was displayed correctly but then had an error displayed: The request failed with HTTP status 401: Unauthorized

Obviously related to permissions but which ones? Well first, without really knowing what the report server was trying to access, I decided to set my Windows service credentials to use the Network Service instead of a special account. This way, I could know that it would be able to access most stuff locally without a problem.

This didn't work but then I found another unusual error report that suggested there was a problem with loopback addresses and the credentials used to log in to the report manager page. I don't really understand what was happening but another visit to regedit and creating a DWORD key underneath HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa called DisableLoopbackCheck and set to 1 made it all come to life! The MS support article said this would only happen when run locally but this was happening to me from a browser on another machine so who knows!