Guides on setting up SharePoint integrated SQL Server Reporting Services

Overview

This is going to be a long post, in this post I am trying to describe how I setup Integrated SQL Server Reporting Services on a Standalone SharePoint Server. According to my understanding, these steps can be applied to SharePoint Services too.

Software Configuration

• SQL Server 2005
• SharePoint Server 2007
• Microsoft .NET Framework 3.0 Download

Reference

Guides to install and configure

1. How to: Configure SharePoint Integration on a Stand-alone Server
2. Configuring Reporting Services for SharePoint 3.0 Integration
3. How to: Create a Report Server Database for SharePoint Integrated Mode (Reporting Services Configuration)
4. How to: Configure the Report Server Integration Feature in SharePoint Central Administration

Information on MOSS integrated SQL Server Reporting Service

1. Microsoft SQL Server 2005 SP2 Reporting Services integration with WSS 3.0 and MOSS 2007
2. Installation and Configuration Guide for SQL Server Reporting Services - SharePoint Integration Mode (Word format)

Steps

Prepare the software package

1. SQL Server 2005 Service Pack 2 Download
2. SQL Server 2005 Reporting Services Add-in for Microsoft SharePoint Technologies Download

Install software package

1. Install .NET Framework 3.0 (My server originally has this installed)
2. Install SQL Server 2005 SP 2
3. Install SQL Server 2005 RS Add-in for SharePoint

Create a Report Server DB for SharePoint Integrated Mode (Check Reference 3 for details)

1. Go to "Report Services Configuration" in SQL Server 2005 "Configuration Tools"
2. Connect to the Instance you want to create the Report Server (Mine is "MSSQLSERVER")
3. Click "Database Setup" on the left pane, then select the Server you want to connect and click "Connect"
4. Click "New" button next to the Database Name.
5. In "SQL Server Connection" dialog box, enter the name for the new Database, make sure to select the "Create the report server database in SharePoint Integrated mode" check box.
6. Click "OK"
7. On the Database Setup page, specify the Credential you want to connect to the report server, Click "Apply"

Configure Report Server Integration Feature in SharePoint Central Administration (Check Reference 4 for details)

1. Go to "SharePoint 3.0 Central Administration"
2. In Central Administration, select "Application Management", then click "Manage integration settings" under "Reporting Services"
3. Specify the "Report Server URL" and "Authentication Mode", normally the Report Server URL will be something like "http://server-name:8080/reportserver" where port number should follow your configuration but not always "8080". I use "Trusted Account" for "Authentication Mode"
4. Click "OK" to go back to "Application Management", then select "Grant database access".
5. Specify the "Server Name" and "Instance" of the Report Server (In standalone case the server name should be the same as the SharePoint Server).
6. In the pop up windows, specify the "User Name" and "Password" to connect to report server. Enter the administrator account here. Then click "OK" twice to go back to "Application Management".
7. Select "Set server defaults", if you enter this page successfully, then it means that SharePoint connect to report server successfully. Actually I didn't change any settings here, and the report service works fine in SharePoint Site.