SSL Certificates on SQL Server 2005 for Reporting Services
Installing an SSL certificate should be almost automatic. It should not be such a difficult thing to do so as to make you not even try; but that seems to be the case. This article does not go into how to obtain a certificate, or how much one should cost, as there are plenty of articles out there on that subject. In fact, we are not even going to deal with a "real" certificate.
Download the SSL Toolkit from Microsoft. Be sure to get the version (5 or 6) appropriate for your release of Internet Information Service (IIS). If you are using a current release of XP or Server 2003 then you will need version six. The version number so far refers to the version of IIS. The version of the tool kit is 1.0, and apparently there will never be a patch or fix because Microsoft rules for posting software on their download site prohibit them from spending the time on a free, unsupported product such as this.
It should also be noted that there is a bug in the tool kit that only allows a certificate to be installed on the first site in your IIS server. If you cannot make your web server the first site you can not use the SSL Toolkit to install a test certificate. That aside, the toolkit generated (selfssl.exe) certificate works just fine for testing purposes.
Do NOT Install the Certificate Yet
In the April SQL Server 2005 CTP (Customer Technology Preview) you could install the certificate into IIS before beginning the setup program and everything would go fine - to a point. There was still quite a bit of manual configuration and a lot of experimentation; but the setup did not fail. The setup program failed in the September CTP and we elected to not even try until the RTM (Release to Manufacture) release.
Well, guess what? The RTM setup failed, too. If you install the certificate prior to running setup, when you get to the Collation Settings screen a box pops up with the following message:
"SQL Server Setup failed to retrieve the SSL certificate name."
and you are asked if that is "ok" before it exits. I hope by now that someone at Microsoft has responded to some of my posts at various places on the web. And frankly, there is a little more information out there now about how to fix the problem. When I first figured out the various answers my boss questioned how I had done it. Now that Microsoft documents one particular aspect which I will point out later, I feel vindicated.
So then, How do you do it?
Once you have finished the SQL Server installation (i.e., setup) successfully you go back and add the certificate to the IIS Server. You will then have to go into a couple of configuration (.config) files and manually make changes.
The first change is made in the file named rsreportserver.config. Change the value of the SecureConnectionLevel from the default value of zero to two. Check for yourself what one and three are as it is not my intention to tell you to do something you do not want to do.
While in that file search for "http" and you will find the tag for UrlRoot. Change this to the fully qualified name of your reporting server including any non-standard port designation (i.e., not port 80), for instance, the default used with SSL is 433. And do not forget that your certificate means https: vs. http:
For our server the change looked as follows:
<UrlRoot>https://p7reporting:433/reportserver</UrlRoot>
Next go into the file named rswebapplication.config and change the URL for the reportserver; but do NOT qualify it with the port number.
For our server the change looked as follows:
<ReportServerUrl>https://p7reporting/reportserver</ReportServerUrl>
Now, for the part that my boss did not believe four months ago. Remove the line above the ReportServerUrl entry for the ReportServerVirtualDirectory. These two lines are "mutually exclusive" according to a document on MSDN today. You will just have to imagine how painful it was to find this fact out before Microsoft told us.
You must now go into Internet Manager (inetmgr.exe) and change the settings to make the page only available with encryption. Select "Require SSL" and "128 bit encryption". Once this is done you should restart reporting services and IIS.
I have shortcuts on my desktop for 1) restarting reporting services, 2) IIS Reset and 3) INet Manager. Two of these are just shortcuts to the appropriate programs; but the one for restarting reporting services was a custom command file.
A pet peeve of mine is calling these "command files". Command files are actually "batch files", i.e., files that contain commands which are executed in batch; but because the DOS extension for batch files is ".bat" and the NT extension for them is ".cmd", I prefer them to be called command files. if this convention is followed you can quickly tell when a "batch file" is written for 16 bit dos or 32/64 bit Windows.
That said, assuming you accept the lecture, here is the code for the "restart reporting services.cmd" file:
@echo off >nulcls.echo.echo this script restarts the reporting servicesecho.net stop "SQL Server Reporting Services (MSSQLSERVER)"net start "SQL Server Reporting Services (MSSQLSERVER)"echo.pauseecho.:exitexitOne Last Note - Testing and Using
If you had previously made bookmarks for the Report Server and Report Manager be sure to go back and change them to https. If you have done all of this correctly they will no longer work as http. If you did everything but forgot to restart IIS the old links will work until the machine or IIS gets restarted and then they will quit working.
The first time you navigate to either of the URLs you will be prompted about the certificate from a non-trusted CA (Certificate Authority). Since you know that this is a test certificate you might want to go ahead and "view certificate" and then select "install certificate". This will get that out of the way once and for all.
Navigate around a bit and check that the little padlock icon is on the status bar. On the Report Manager, from Home, navigate from Contents to Properties. The padlock icon will still be present. Then click on My Subscriptions and Site Settings and it will still be present. Finally, click on Help and you will get a message that this screen requires https: in order to be seen. Manually change the address (in the browser address bar) from http: to https: and the screen will display. This seems to be pretty clearly a bug in the software. If anyone comes up with a solution to get this to display correctly please let me know.
Conclusions
It was a long time coming and sometimes awfully painful; but the end product appears to be pretty good. Support remains an issue; but there are solutions out there to be found. And SQL Server Central is as good a place as any for finding them, and talking about them when you were the first to find them. There is a really good book on Reporting Services that includes six months of "premium service" with a purchase. Unfortunately, the premium service is only available to purchasers, and as a result there is very little content. As a non-purchaser you can see the original posts but not the answers... you can even post; but you cannot see any of the answers until you buy the book, and there are no answers. Okay, not "no answers"; but very few.
Yes. SQL Server Central seems to be the place these days. Microsoft apparently freezes all of their documentation about three months before delivery of the final product so that all languages can ship with the same content. This will catch up, in time, in the meantime, I hope you enjoy your new toys, and I hope this has made it a bit easier to configure. Oh, and by the way, if you haven't figured it out already - configure your help to search the internet first.
