Thursday, October 18, 2007

SQL Server 2005 Reporting Services on Vista

I've been in a very technical mood as of late.  There's something about starting a new project, putting together a new site, and doing things I've never done before.

One of those things was to set up SQL Server 2005 Reporting Services so that we can easily integrate graphs and tables and drill-through reports.  It doesn't hurt that it comes free with SQL Server either.  So I set to installing SQL Server Reporting Services on my Vista laptop and begin developing reports.  Here's what I did and bits of advice.

Set Up

I previously had Microsoft Internet Information Server (IIS) installed (the hoops I jumped through for installing that are for another time), Visual Studio 2005, SQL Server 2005 Express and SQL Server Express Enterprise Manager.

I decided to download SQL Server Express Advanced, which includes a stripped down Reporting Services engine.  I went through the install and . . . Report server wasn't an option for the install.  This was not a good sign for the rest of the process.

It turns out I was missing one component in IIS.  So, after a quick Control Panel -> Programs -> Turn Windows Features on or off.  Then expanding Internet Information Services -> World Wide Web Services -> Application Development Features and checking ISAPI Extensions (okay, not so quick) I was well on my way.

Windows Features

I must admit, in the middle of figuring this out I uninstalled SQL Express and installed SQL Server 2005 Standard.  I thought that was the problem, and while it wasn't, I do like having the ability to easily import data (a feature not available in the Express version of Enterprise Manager).

I finally came across this knowledge base article (KB 934164), which helped immensely.  I won't go into all the steps here, since the article details them very well.  I do recommend grabbing this article before you go through the process.  But there were some gotchas, one in particular required a Microsoft Support call.

No matter what, once you install SQL Server, or later add Reporting Services, you have to install SQL Server 2005 Service Pack 2.  This knowledge base article (KB 913089) explains how to get it, though on my machine Microsoft Update automatically found and installed it.

Configuration

First gotcha once everything is installed was related to the Default Application Pool.  While under the Reporting Server Configuration screen in the Web Service Identity tab, the Classic .NET AppPool needs to be selected.  The default application pool requires web.config the be reformatted and, from what I can tell, breaks report server.

Other than that one thing though, installation seemed pretty simple.

Now it was time to run the application, right?

The first step there is to open Internet Explorer as an Administrator.  Any Vista programmer knows the drill.  Right-Click Internet Explorer and choose Run as Administrator.  Then we go to http://localhost/reports.

Turns out though, that that doesn't work.  Even though I was running IE with administrator privileges, the site didn't recognize the elevated access.  The ever important "Properties" tab wasn't showing up. This is where the Microsoft support call came in.

It turns out, in my case anyway, that you need to turn off that annoying "confirm" box you get every time you run something as administrator.  So, back to Control Panel -> User Accounts -> User Accounts (it's not a misprint, to get your own user settings, click it twice) I was able to Turn User Account Control off and reboot.

UAC

Once I had the user account control (UAC) off I opened up Internet Explorer.  I went back to http://localhost/reports and viola, I saw the properties tab.

Following the directions in the above knowledge base article, I diligently added my own user ID (with computer name, so it was computername\userid) to the Content Manager group.

With that done I turned UAC back on and rebooted.  While that confirm box is annoying, I still recommend it.  I've worked on too many XP and earlier computers where teens or adults install stuff without even realizing it.

Finally, I had access to the report manager and was ready to rock.  When all is working you should see this.

ReportsSite

Sadly, I now have an problem opening the ReportService2005.asmx file.  Every time I try it wants to download the generated XML file instead of displaying the friendly screen.  Oddly, I see the XML inline in Firefox and my own test Web Service works.  But I suspect that is more particular to my machine, and I'm awaiting a call back from Microsoft on it.

I'm not sure whether this really helps anyone else.  But after days of struggling with it I finally decided to get my experience and tips down someplace so I'll have it when I need to install it all again in the future.

Peace,
+Tom

4 comments:

Anonymous said...

Thanks a million time, for the UAC deactivation tip. I have spent hours on googling to find a solution to my empty report manager.

Judging by the number of people looking for a solution in forums, the word just need to be spread.

durkinch said...

Thanks Tom! I too have spent many hours looking for the solution to this one. Even the MSDN entry on how to set up SQL Reports under Vista doesn't mention anything about disabling the UAC prompts.

Andrew said...

Thanks Tom - excellent information to set this up on Vista.

Anonymous said...

Great Job! Thanks very much. I just hope that other frustrated souls find this solution in goood time.