Connecting Sharepoint to SQL 2005 Report Server

It seemed simple: Export OLAP reports from SQL 2005 Reporting Services into Sharepoint. I like Sharepoint because it solves a ton of problems in organizations. I'm still surprised at how many Microsoft shops don't use Sharepoint because it's free and it integrates into Active Directory. (Sharepoint Portal Server, a different product, costs money, scales more and is personalizable.) All you need for Sharepoint is IIS and SQL or the MSDE; and FrontPage 2003 if you want to edit graphics. Microsoft has a lot of Sharepoint resources available for download, but they're not well organized.

The details slowed me down a few hours. There are several different ways of configuring security contexts, and you will have to keep your accounts and passwords straight. I have yet to find a step-by-step on Technet, but I'm still looking. I did see a page showing cool OLAP reports in Sharepoint on Technet, but no link to help me set it up.

The biggest problem that I've seen many other folks have is the 404 Bad Request error in the /Reports ReportManager Virtual Directory. /ReportServer worked the first time, but without the ReportManager Virtual Directory, it's not so useful. At first I thought this was a DCOM security issue because of the event log entries I got. (Ten of these on the first request for http://myreportserver/reports after restarting IIS and then no more until restarting IIS.)

The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID {BA126AD1-2166-11D1-B1D0-00805FC1270E} to the user NT AUTHORITY\NETWORK SERVICE SID (S-1-5-20). This security permission can be modified using the Component Services administrative tool.

The trouble with that message is that there's no DCOM component in Component Services that corresponds to the CLSID. This didn't stop me from searching the registry for a while, finding that the CLSID is involved with about a dozen basic network services, none of which are in the Component Services MMC.

I gave up searching the Registry and I added NT Authority\Network Service to the DCOM user group on the local machine and restarted IIS. No joy. I was able to clear the event log of that error this way, but I still got the same error requesting http://myreportserver/Reports, just with no event log entries. I rechecked all the settings in SQL Server's Report Configuration Tool, which is very useful, but still didn't solve the problem.

I googled the source code on the error page:

System.Net.WebException: The request failed with HTTP status 400: Bad Request.

and found a site at MIT concerning a totally unrelated applicaton that threw the same error. I had one other Virtual Web on the machine, so I deleted it and reset my Default Web Site set to All Unassigned IP addresses and restarted IIS. Bingo. I can manage reports over the Web -- it just takes a while to start up the first time you request http://myresportserver/Reports. I can access it from http://localhost/Reports on that box now; before localhost requests failed, and I didn't know why.

I still have to set the right permissions for everything. I also need to choose whether to share a data connection or use the web visitor's security context. Just listing all the security contexts makes me dizzy: The Sharepoint App Pool, the Report Server App Pool, SQL Report Server Data Sources, the DCOM permissions mentioned above, and finally, your users' accounts in Sharepoint and Reports.

Sharepoint doesn't hold the Report -- it just passes your request on to the Report Server. Thus, you'll need to set permissions for the Sharepoint and the SQL Report Server. If you have Sharepoint permissions but not Report Server permissions, the Report Explorer web part will be blank.

Steps that worked for me:
1. Start with a good SQL 2005 install with all necessary components -- like Reporting Services.
2. Install IIS and ASP.net 2 if they're not installed already. I installed SQL 2005 Service Pack 1 after this step. (Make sure you have only a default web site on IIS to avoid my issues.)
3. Use the SQL 2005 Report Configuration Manager. This is when you'll need to decide which security schema you're going to use before you can complete this. The Configuration Manager saves a lot of time because you won't have to touch IIS Manager. (The whole scripting IIS configurations in XML thing is going to make my IIS skills obsolete before long.)
4. Create a simple report. SQL Books Online has a tutorial using the Adventure Works database.
5. Verify that http://yourreportserver/Reports and http://yourreportserver/ReportServer work.

Now move to your Sharepoint box running WSS.

6. Use stsadm.exe to install the web part. You will find the report explorer and report viewer web parts on your SQL box: (Search the Report Services library for Sharepoint for more details.)
C:\Program Files\Microsoft SQL Server\90\Tools\Reporting Services\SharePoint\RSWebParts.cab
7. Open your SharePoint site and add the Report Explorer Web Part from the Virtual Server Gallery.
8. Point the Report Explorer at http://yourreportserver/Reports and leave the start path blank for now.
9. You should be able to see your SQL Reports on your Sharepoint site.

My example runs on two boxes: SQL 2005 and Reporting Services/IIS on one box, (along with the Exchange 12 Beta), and my Sharepoint on another box. Sharepoint doesn't seem to run on the same box as the Exchange 12 Beta.