To put it bluntly, configuring SQL Server Reporting Services 2005 or 2008 to run in SharePoint integrated mode with Microsoft Office SharePoint Server 2007 is a pain in the behind. The setup of the numerous moving pieces seems to get confused between two sets of configurations, two sets of security, and two sets of databases. (I would get confused with all those pieces too! ;) ) Let's take a look at some highlights of the installation steps and a few warnings that may help you in your environment.
It makes sense that you would need Reporting Services (2005 with SP2 or 2008) and SharePoint (WSS 3.0 or MOSS 2007) in some fashion on your servers. The secret ingredient to tie everything together is actually an extra add-in that must be installed on your SharePoint server. This is a free download that can be found here (2005) or here (2008).
Once you have installed all components, you must complete a series of screens in the SharePoint Central Administration tool. The screens tell SharePoint where the report server resides and sets up the security so that the two pieces can talk to each other. You need to be sure that you have a user account that has access to both server/databases to facilitate this.
Here are a few road bumps that I have run into in the past:
- When you configure your SSRS databases, ensure that you have selected the option to create the database in SharePoint Integrated mode. Each mode creates its own type of database and never the twain shall meet.
- Once you get your environment set up, make sure Reporting Services reports are stored in the Default zone. Otherwise, the reports will not render.
- Don't mix your SharePoint and Reporting Services (2005) applications in IIS. SharePoint should have sole ownership of its virtual, and SSRS's setup should not be disturbed.
Do as I say, not as I do
To install and configure your environment, I highly recommend that you follow the steps in the EXACT order as written in the Microsoft SQL Server Reporting Services (SSRS) Installation/Configuration Guide for SharePoint Integration Mode whitepaper. While this document is written for SSRS 2005, it very closely aligns with the 2008 method as well. Good luck with your setup!