Ezequiel Bella’s Blog

Integrating SQL Server 2008 Reporting Services with SharePoint 2007

This walkthrough assumes that you have previously installed MOSS 2007 (with Reporting Services Integration feature on) + SQL Server 2008 on Windows Server 2008. If you are using another Windows version, there might be steps that will slightly differ while configuring them, so try to adjust them to your environment.

Install the Microsoft SQL Server 2008 Reporting Services Add-in for Microsoft SharePoint Technologies (here).

This add-in basically provides a Report Viewer Web Part that provides report viewing capability, export to other rendering formats, page navigation, search, print, and zoom.

Configure the Report Server via the Reporting Services Configuration Manager tool.

1. Service Account tab: In this scenario we will use Network Service as the service account.

   

2. Web Service URL tab: Use the default ReportServer Virtual Directory (is already created). This is the URL of the Report Server that will be hosted in IIS. If you want to change the name, edit the Virtual Directory text and click Apply. This will remove the existing server, and do the necessary stuff to publish the new one.

   

3. Database tab: Use the default ReportServer database (is already created). Have in mind that this database was created in Sharepoint Integrated Mode, thus cannot be accessed by the Report Server unless the database has been granted rights to access to the Sharepoint server farm (you will configure this later.)

   

4. Report Manager URL tab: This tab is intended to configure the Report Server Control Panel. In our case, we won’t use this feature because is not supported while operating in SharePoint Integration mode.

For the other tabs, let’s use the default configuration.

Configure Report Server in SharePoint Central Admin Tool.

This step will finally integrate Reporting Services with SharePoint.

1. Open the Central Administration Site. To do this, click Start | All Programs | Microsoft Office Server …

   

2. Enable Report Server Integration Feature from SharePoint.

   

3. Give Report Server permission to access the database in the SharePoint farm. To do this, go back to Application Management page, locate the Reporting Services section, and click the Grant database access link. Choose the appropriate db server name, choose Default Instance and click OK. You will be prompted for a user account and a password, use a System Administrator account.

   

4. Register the Report Server URL so as to integrate it with SharePoint. In the Reporting Services section click Manage integration settings, and set the following values:
   

   a. Report Server Web Service: Is the URL were the report server is hosted (http://<server-name>/ReportServer)

   b. Authenticated Mode: Trusted Account.

5. If you will consume a report that logs on to the database using Windows Authentication, you should set this value to Windows Authentication instead.

   

6. You can also configure the Reporting Services Server Defaults, but we will leave it like this for now. If you want to configure them see the references below for more detail.

You can verify the procedure by navigating to the ReportServer URL, and see that you are now able to browse the site correctly. This means that Report Server has the rights to access to the Report Server database of the SharePoint site farm, an operation that we were not able to perform before.

In the next post you will see how to deploy a report from Visual Studio into your SharePoint site, and then consume it using the Report Viewer Web Part that comes with the Microsoft SQL Server 2008 Reporting Services Add-in.

References:

• How to: Configure Report Server Integration in SharePoint Central Administration
• Microsoft SQL Server Reporting Services - Installation and Configuration Guide for SharePoint Integration Mode.docx