Todd's World: Setting up a reporting site in SQL Server 2008 Developer Edition

Saturday, March 21, 2009

Setting up a reporting site in SQL Server 2008 Developer Edition

Setting up a reporting site in SQL Server 2008 Developer Edition

I wanted to set up a load test reporting site, using the samples at http://www.codeplex.com/loadtestreports. On my host OS, I have IIS 7 and SQL Server 2008 Developer Edition, so it was a slightly different setup. If you’re going to develop any reports for TFS, it helps to have a practice site to work on, too.

At first, I was getting a little frustrated at the SSRS site, because it kept looking for a connection to https://mymachine/reports. I didn’t have a secure web site, and I didn’t have a certificate. I tried altering the reporting services configuration through the reporting services configuration manager, but things just kept getting worse.

After a little while, I thought that maybe I could set up a secure web site. Fortunately, Rob Bagby put a nice tutorial out on configuring a self-signed certificate at http://blogs.msdn.com/bags/archive/2009/01/23/self-signed-certificates-on-iis-7-the-easy-way-and-the-most-effective-way.aspx. It wasn’t helping me set up a secure site for SSRS, though. The instructions were long, and Rob’s advice to use host headers for a phony machine name was making it impossible to authenticate to SSRS.

I found if I didn’t use host headers, I could sign a certificate with my machine name and put it into the trusted root certification authority in just one command (yes, the entire IIS job done in one command):

c:\…\SelfSSL>selfssl.exe /N:CN=MYMACHINE /V:3000 /S:1 /T

Of course, replace “MYMACHINE” with the name of your machine. The really great thing is that by downloading that SelfSSL.exe file, I accomplished in one step the entire process of loading a certificate and setting up a secure web site. The last “/T” option even added the certificate to my trusted root certification authorities store. The “/S:1” targeted my default web site. Read Rob’s article to understand everything that’s going on—still, it’s nice to do it with one command line in an elevated prompt.

You can download SelfSSL.exe as part of the IIS 6.0 Resource Kit from http://www.microsoft.com/downloads/thankyou.aspx?familyId=56fc92ee-a71a-4c73-b628-ade629c89499&displayLang=en. Make sure you’ve set up IIS 6 management compatibility in the roles and features if you’re using Windows Server 2008. You can choose just to install the pieces of the resource kit you want—I just installed SelfSSL.exe.

After that, you just need to go to the Reporting Services Configuration Manager, and set the Web Service URL and the Report Manager URL to use the same certificate as IIS. Go to the advanced settings, and add the certificate to the HTTPS settings on the advanced button.

Here are the automatically configured settings for the default web site bindings (IIS Manager):

Here are the settings for the Report Manager (Reporting Services Configuration Manager):

And here are the settings for the Web Service URL (Reporting Services Configuration Manager:

With that, I could go out and create the data sources and upload the reports. I had to hand-edit the data source, and I had to go to the properties of each report I uploaded to modify the data source to use the new one that I’d created by hand:

You just go use the browse button on Properties->Data Sources to pick the data source you created. (I could have skipped that if I had used BIDS for this project).

Finally, keep this URL handy if the SSRS “Home” page is blank: How to: Configure a Report Server for Local Administration on Windows Vista and Windows Server 2008.

Happy Reporting!

Published Saturday, March 21, 2009 8:58 PM by dscruggs