Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SSL Certificates on SQL Server 2005 for Reporting Services

By David Russell,

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 >nul
cls.
echo.
echo this script restarts the reporting services
echo.
net stop "SQL Server Reporting Services (MSSQLSERVER)"
net start "SQL Server Reporting Services (MSSQLSERVER)"
echo.
pause
echo.
:exit
exit

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

Total article views: 13128 | Views in the last 30 days: 20
 
Related Articles
FORUM

Certificate services - work with SQL Server 2005

Certificate services - work with SQL Server 2005

BLOG

Setup SSL Access to SSRS 2012 with Active Directory Certificate Services

The objective was to configure SSL access to a SQL 2012 Reporting Services server in Native Mode. Th...

BLOG

SQL Server 2012: New Certification Info

As a follow-up to my posts SQL Server “Denali”: New Certifications and SQL Server 2012: New Certific...

FORUM

Reporting services

Reporting services

FORUM

Regarding SQL Server certification.

Regarding SQL Server certification.

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones