Browsing Posts in Reporting Services

You know you’re living life on the bleeding edge when you search the web for an answer to your problem and your own blog appears as one of the most promising results!?! (Currently third from the top on this search.) Reporting Services on Windows Vista just doesn’t work out-of-the-box, even with SQL Server 2005 SP2 installed. (N.B. SP2 is beta/CTP right now.) So I decided to do a bit of digging. As is noted here, local administrators are no longer SQL Server admins by default. (Local administrators used to get this because they were a member of Administrators group and Administrators was added to the SQL Server sysadmin role by default. Because of UAC – which is an excellent security technology no matter what its detractors might say – local administrators do not have the Administrators SID in their security token. (In order to have the administrators token, you must elevate using “right-click, Run as administrator” and consent via the UAC prompt.) So how do you grant your user (administrator or not – and I still encourage not) access to the report server?

  1. Run IE as an administrator. (Right-click IE, Run as administrator.)
  2. Browse to your Report Manager directory, usually http://localhost/reports.
  3. Click on Site Settings (upper right).
  4. Under the Security heading, click “Configure site-wide security”.
  5. “New Role Assignment” and add your user to the System Administrators role.
  6. Click “Home”, “Properties”, and under security “New Role Assignment”, add your user to “Content Manager”.

Now the funny part… Try running a non-elevated IE and browse to http://localhost/Reports. This is the result:

Report Manager - IE Protected Mode

Now let’s try it with FireFox:

Report Manager - Firefox

That looks a lot better. Turns out that IE Protected Mode is getting in the way and preventing Report Manager from rendering properly. (IE7 runs in Protected Mode by default, which is a good thing.) We can disable protected mode just for this site by adding it to our trusted sites list in IE. (Tools… Internet Options… Security tab… Select “Trusted sites”… Click “Sites”… and add http://localhost to the list.) Also make sure that “Enable Protected Mode” is disabled for Trusted Sites. (It should be enabled for Internet/Local intranet/Restricted sites.)

Report Manager - IE Trusted Sites

Note on the bottom bar “Trusted sites | Protected Mode: Off”. That looks a lot better under IE7!  I haven’t investigated why Report Manager doesn’t like Protected Mode, but it should get many of you up and running with Reporting Services on Vista.

When you try to install SQL Server 2005 Reporting Services on Windows Vista, the installer apparently fails to recognize that IIS7 is installed (assuming that you have installed it). The error message is misleading and is due to the fact that IIS7 components required by Reporting Services are not installed. IIS7 has been much more componentized than IIS6 and there is a magical list of IIS7 features that must be installed in order for Reporting Services to install successfully. The required IIS7 features are documented in KB article 920201.

Component Folder
Static Content Common HTTP Features
Default Document Common HTTP Features
HTTP Redirection Common HTTP Features
Directory Browsing Common HTTP Features
ASP.Net Application Development
ISAPI Extension Application Development
ISAPI Filters Application Development
Windows Authentication Security
IIS Metabase Management Tools
IIS 6 WMI Management Tools

Once you have SQL Server installed, you will need to install SQL Server 2005 SP2 November 2006 CTP or later as it addresses some Vista-compatibility issues. You can find the latest SQL Server 2005 service pack information here. You should now have a working Reporting Services install on Vista.

I was asked recently by a colleague whether you could programmatically emit the name/value pairs of parameters in a Reporting Services report without hard-coding them. Seems like a logical thing to do. So I started digging deep into the guts of the Reporting Services object model and was sorely disappointed. I really like Reporting Services, but the object model accessible from within a report just bites. Let me explain.


When you access Parameters in a report from the Report Properties… Code… (or a custom assembly), you are actually dealing with a Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Parameters object. (The class is located in an assembly Microsoft.ReportingServices.Processing.dll, which is installed by default in C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\bin\.) This object exposes a string-based indexer and that’s it!


public class Parameters {
   public Parameter this[string key] {
      get {
         // code
      }
   }
}


You have to know the key (a.k.a. name of the parameter) to retrieve the parameter to get its value. There is no collection support. (i.e. No Count property and integer-based indexer or IEnumerable implementation.) Parameters is derived from System.Object rather than System.Collections.CollectionBase or something equally useful. You cannot use “foreach” or “for” to enumerate through the parameters.


So from within a report, you cannot obtain a parameter list programmatically as far as I can tell. The same applies to ReportItems and Fields. You’re really limited to creating little helper functions rather than being able to programmatically query the state of a report. How disappointing!

I spent many hours over the past few days trying to get Reporting Services up and running on my new box. I had set up Reporting Services many times before without difficulty, but for some reason, it just didn’t want to cooperate this time. The setup succeeded, but when I tried to access Report Manager, Report Manager reported 404s – that it could not find the root folder. (The Report Manager default page appeared. So it wasn’t an ASP.NET issue.) When I tried to deploy reports to the server, Visual Studio .NET 2003 reported that the web service did not exist. Strangely when I asked for the WSDL using a browser pointed at http://localhost/ReportServer/ReportingService.asmx?wsdl, I received back the expected WSDL. Here’s what I found in c:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\LogFiles\ReportServerWebApp__03_28_2005_11_06_50.log:

 

– various bits of startup information

aspnet_wp!ui!ce4!3/28/2005-11:06:52:: e ERROR: The request failed with HTTP status 404: Object Not Found.
aspnet_wp!ui!ce4!3/28/2005-11:06:52:: e ERROR: HTTP status code –> 500
——-Details——–
System.Net.WebException: The request failed with HTTP status 404: Object Not Found.
– remainder of stack trace omitted for brevity

 

So I started poking around the Reporting Service installation troubleshooting guide as well as Google without success. So I put my Reporting Services woes on the backburner for awhile and went on to other tasks. While working on one of these unrelated tasks, I fired up SQL Profiler to capture a trace for an application I was working on. I noticed that SQL Server was reporting the wrong hostname. The folks who had prepped my new box had built it from a Ghost image. They had changed the hostname in the OS, but had neglected to update the hostname in SQL Server’s system tables. I changed the hostname in the system tables to match the hostname in the OS and suddenly Reporting Services started working!

 

The moral of this story: When SQL Server is setup, it records its hostname in the system tables. If you have any Ghost images including SQL Server, you must change SQL Server’s hostname after changing the OS’s hostname. You will experience all kinds of woe if there is a name mismatch between what SQL Server thinks the hostname is and the actual hostname.

 

Steps to Resolve the Problem

First verify that this is in fact your problem. Launch SQL Query Analyzer and run the following script:


 

– Start script

sp_helpserver

GO

– End script

 

Note the hostname that SQL Server reports. If this doesn’t match your computer’s hostname, you need to change it. (I’ll assume that you know your computer’s hostname or how to find it in My Computer… Properties.) If the hostnames don’t match, here’s a script to correct the problem. You’ll have to substitute appropriate values for OLDNAME and NEWNAME in the script below. N.B. The parameter ‘local’ in sp_addserver is important as it tells SQL Server that this is its hostname rather than the hostname of a linked server.

 

– Start script

sp_dropserver ‘OLDNAME’

GO

sp_addserver ‘NEWNAME’, ‘local’

GO

sp_helpserver

GO

– End script

 

sp_helpserver should now report the correct hostname. Since SQL Server only reads its hostname information at startup, you’ll have to restart SQL Server for these changes to take effect. Once you’ve restarted SQL Server, Report Manager should be working again.

After installing the ASP.NET ValidatePath Module, Reporting Services will give you the following error:


Server Error in ‘/ReportServer’ Application.

 

Security Exception

 

Description: The application attempted to perform an operation not allowed by the security policy. To grant this application the required permission please contact your system administrator or change the application’s trust level in the configuration file.

 

Exception Details: System.Security.SecurityException: Request for the permission of type System.Web.AspNetHostingPermission, System, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 failed.

Microsoft has posted KB article #887787 with revised Reporting Services .config files that fixes the issue.