A Series of Unfortunate Reporting Services Events

Specifically, this post is about my difficulties with setting up SQL Server Reporting Services 2008 R2.

It had been about 4 years or so since I last touched a version of Reporting Services. In many of our projects, the reporting component is handled by someone else. However, in a recent project we had the task of setting up a system and writing the first report. (I’d like to talk about the stack for the project later. We used Silverlight 4, IdeaBlade’s DevForce and MVC2.)

Install Failing Before It Starts

We had a single box that was both our staging environment and our build server. So I decided to setup Reporting Services (RS) there.

My first problem was that setup.exe for the install media just failed. I’d briefly see a command prompt flash and then… nothing. Scouring the web offered little insight, except a vague reference saying that the install didn’t like newer versions of .NET. So I uninstalled .NET 4.0, but the problem persisted. I reinstalled .NET 4.0 via Windows Update and then (somehow) I got the install to run. I’m really not entirely sure what fixed it. I have the sensation of looking away for a moment, forgetting about it, and then realizing that “hey, the setup has started.” Go figure.

Code Access Security

After configuring the new install using the Reporting Services Configuration Manager, I attempted to access the Report Manager and I received a 500 error. At this point I began to chronicle my troubles.

To determine the cause, I had to examine the logs file. There were located here:

C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\LogFiles

I found this entry:

Okay, well, that seemed clear enough.  I had .NET 4.0 installed and so I need to use the legacy CAS. The NetFx40_LegacySecurityPolicy is set in a config file.

If you are new to RS like myself, then you might not know that there are three applications. Two web applications and a service. I realized that this error was raised when the service attempted to start. I did not need to access the Report Manager web app for the error to appear in the log. The service executable is located inside the folder for one of the web applications. (This was not immediately obvious to me.) Thus, the config file to edit is

\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\bin\ReportingServicesService.exe.config

I set modified the config and the above error no longer appeared when the service started.

Another 500 Error

After resolving that, I was seeing a new error in the log after attempting to access either of the web applications:

I fiddle with things for a bit, but I didn’t figure this one out. Instead, we spun up a fresh box to dedicated to RS and we were careful not to install anything else. (As in, no newer version of the .NET framework.)

After running the config utility on the fresh install, I was able to access the web services without any 500 errors.

Lesson: dedicate a box to Reporting Services and don’t put anything else on it.

Indefinite and Definite Articles

My troubles were not over. When I attempted to access the Reporting Manager url I was prompted to login. I used a custom account that I had created specifically for RS. This account was a local administrator and it was the same custom account that used in the config utility. However, I was denied accessed.

I was befuddled. The account had admin rights to everything, including the SQL Server where the RS database was located.

Well, it turns out that the only account that can access a fresh install of RS is the local administrator. This is stated in the documentation, but I read it as a local administrator. Notice the difference: “the” vs. “a”.

After successfully logging in with the local administrator, I was able to grant rights to my other account.

The rest, well the rest was a lot easier…