SQL Server 2005 Reporting Services Configuration Madness

Well, after almost exactly 6 hours, I’ve succeeded at installing SQL Server 2005 Reporting Services on a server with more than one website.

We’re running Reporting Services on separate web servers. So, after the install of reporting services, you run their little configuration tool. This of course, accomplishes very little :). See, apparently Reporting Services wasn’t designed to work on a server running, *gasp*, more than one application.

If you have a decent IIS install, the default website isn’t there and thus requests to http://localhost/ aren’t gonna work. Reporting Services doesn’t take this into consideration, and happily tries to request http://localhost/ReportServer/ even after you’ve specified this in the config tool. If this is your issue, you’ll get a “HTTP Error 400: Bad Request“ when trying to access the ReportManager (/Reports/) website. You’ll need to edit the config files in Program Files\…..\ReportManager and ReportServer. rsreportserver.config needs to point to http://the.reporting.host.name/ReportServer in the UrlRoot element.

In RSWebApplication.config, ReportServerUrl will need to have the same value. The ReportServerVirtualDirectory element must be deleted. You will get a “The configuration file contains an element that is not valid. The ReportServerUrl element is not a configuration file element. “ message. This is because the config reading code apparently doesn’t fail gracefully. What it’s trying to say is “the ReportServerUrl and ReportServerVirtualDirectory elements are mutually exclusive”. I’m still unsure why there should be anything besides a URL… Around here, you might notice a bunch of DCOM errors in your Event Log (or before this point). To fix these, you’ll need to go into dcomcnfg and edit the COM security for My Computer. Give the account you’re using (like Network Service or “MyReportingServicesAccount“) permissions for local activation and local launch. You need to reboot for these changes to take effect (I think). But don’t reboot just yet… Finally, you end up with a 401 Unauthorized when accessing the Reports site. You might have also noticed you are also unable to authenticate when browsing the Reports or ReportServer sites from your the local server. Why? “Windows XP SP2 and Windows Server 2003 SP1 include a loopback check security feature that is designed to help prevent reflection attacks on your computer. Therefore, authentication fails if the FQDN or the custom host header that you use does not match the local computer name.” So I’m guessing NTLM susceptible to this type of attack, and Microsoft is saving us from it. Well, it also hoses us in this case because from what I can tell, ReportManager (the thing in the Reports vdir — why it wasn’t called ReportManager by default…) needs to connect to ReportServer. It sends a request, which is denied because of the loopback protection above. A quick registry edit fixes this: http://support.microsoft.com/default.aspx?scid=kb;en-us;896861 After that… you might have a working SQL Reporting Services 2005 install! (Next up: Getting it to work with SSL…) Really, apart from the horrible setup/configuration, it’s a very very fine product. I’m actually pretty impressed. The report I wanted to setup (and the subscription so it’s mailed out) only took about 10 minutes (first time I’ve ever used RS)! I’m just at a loss why Microsoft makes it so hard to setup. This configuration can’t be that unusual. And, even stranger, most (if not all) of this configuration issues could take care of these problems. In other words, their little configuration app should automatically fix this stuff (or at least give explicit instructions on how to do so). Or maybe I just didn’t RTFM that well… but this is a Microsoft product… you’re supposed to just shove the DVD in the drive and click next, right?

P.S., if you’re getting a “Object Reference not set to an Instance of an Object“ when you add a new subscription, ensuring everything else is 100% working should make it go away…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s