Configuring Reporting Services for SharePoint 3.0 Integration

SQL Server Setup

New: 12 December 2006

You can configure a deployment of SQL Server 2005 Reporting Services with Service Pack 2 (SP2) to work with a deployment of Microsoft Windows SharePoint Services 3.0 or Microsoft Office SharePoint Server 2007. You must perform configuration steps on both a report server and a deployment of the SharePoint product or technology before users can publish reporting documents to SharePoint libraries and get report viewing and management services on SharePoint sites. The configuration process establishes server connection information; therefore, it is important that you follow the steps in order.

This topic provides an overview of the configuration steps for each server, and then concludes with the tasks you must follow in the How to Configure Server Integration section. SQL Server Setup will not install a report server in SharePoint integrated mode by default. You must manually configure the report server for SharePoint integration mode after you have run Setup.

Overview of Report Server Configuration

To configure a report server for SharePoint integration, you must have an existing stand-alone report server or a scale-out deployment. You must perform the following tasks on the report server:

  • Apply SP2, if you have not done so already.
  • Install the Windows SharePoint Services Web front-end on the report server computer. This step is only necessary if you deploy the report server and the SharePoint technology instance on separate computers. Installing the Web front-end allows you to join the report server computer to a SharePoint farm.
  • Optionally, configure the Report Server Web service and Windows service to run under domain accounts. This step is only necessary for a distributed server deployment that puts the SharePoint databases on the same computer as the report server.
  • Create a report server database in SharePoint integrated mode. Doing so sets server properties on the report server and activates a new SOAP report server management endpoint that is used for integrated operations. Use the Reporting Services Configuration tool to create the database.

If you are integrating a scale-out deployment, all report server instances that are part of the deployment must run in SharePoint integrated mode. You cannot have a combination of server modes in the same scale-out deployment.

About Service Account Configuration

A report server is implemented as a Web service and as a Windows service which run under built-in accounts or Windows user accounts. In SharePoint integrated mode, both services connect to the SharePoint configuration and content databases with write and execute permissions.

In most cases, the default service account settings are valid for a connection to a SharePoint database. However, if the instance of the SharePoint Web application is on one computer, and the report server instance and the SharePoint databases are on a different computer, you cannot use built-in machine accounts such as Network Services or Local System for the Report Server service accounts.

When SharePoint databases run on a remote computer, the SharePoint Web application explicitly denies database access to the machine accounts of a remote computer. If the report server is on the same computer as the SharePoint databases, and if either of the Reporting Services service accounts run under a built-in account, they will be denied access accordingly. Configuring the services to run as Windows user accounts ensures that the report server will not be denied database access because they are running under a prohibited account. For more information about the service account connection to a SharePoint technology instance, see Security Overview for Reporting Services in SharePoint Integration Mode. For more information about configuring the service accounts, see How to: Configure Service Accounts (Reporting Services Configuration).

About the Report Server Database

A report server database provides internal storage for one or more report server instances. A report server database can support native mode operations or SharePoint integration mode, but not both. The contents in the database are mode-specific and are not interchangeable across server modes. If you create a report server database to support SharePoint integrated mode, you cannot automatically convert or migrate the database to run with a native mode report server instance later. A report server database is used in the following ways depending on the server mode:

  • When created for native mode operations, the report server database is the sole repository for persistent data used by the report server.
  • When created for SharePoint integrated mode, the report server database stores server properties, report execution snapshots, report history, subscription definitions, and schedules. It stores a secondary copy of reports, report models, shared data sources, and resources to improve processing performance on the server. Primary storage for report documents is in the SharePoint content databases. For more information, see Storing and Synchronizing Report Server Content With SharePoint Databases.

Creating a report server database for SharePoint integrated mode is an important first step in configuring the report server for SharePoint integration. You can use the Reporting Services Configuration tool to create the report server database and configure the connection to the report server. Only the report server connects to the report server database. The Windows SharePoint Services or Office SharePoint Server instance never connects to or retrieves data from the report server database.

Configuring SharePoint Deployments for Report Server Integration

To configure integration on the server running the SharePoint product or technology, you must download and install the Reporting Services Add-in for SharePoint Technologies. The add-in installs program files and application pages, including pages that you open in Central Administration to set the report server URL and other integration settings. You can specify report server integration settings for a SharePoint farm or a stand-alone SharePoint Web application.

Note:
Only one report server can be added to a SharePoint farm. The report server can be a stand-alone report server installation, or it can be a scale-out deployment that is accessed through a single URL.

SharePoint products and technologies can be deployed as a farm or as a stand-alone server. A farm contains one or more virtual servers, where each virtual server is a SharePoint Web application. A report server integrates at the farm level. From the perspective of storage integration, there can only be one report server database for a farm. You can use SharePoint Central Administration to manage integration settings for all the servers in the farm.

Each SharePoint Web application in a farm can be configured to use different authentication providers. This does not affect report server integration in any way. The report server deployment will handle requests for each server in the farm regardless of the authentication provider it uses.

Each Web application has a default top-level site. You can set permissions differently on each top-level site to vary the availability of report server items and operations across all the servers in a farm.

How to Configure Server Integration

Configuring the servers for integrated operations requires multiple tools and steps. The following list has all the tasks you must perform. These tasks include links to detailed instructions that you can use to complete a specific step. For an overview of how server integration works, see Reporting Services and SharePoint Technology Integration.

Installation Order

Before you can configure integration, you must install Reporting Services and the SharePoint product or technology you want to use. You can install Reporting Services and the SharePoint product or technology in any order. However, the installation order and the deployment topology will determine whether you need to perform additional configuration steps in Internet Information Services (IIS) and when you can verify a successful installation.

For side-by-side deployments:

  • If you install Reporting Services on a computer that already has a SharePoint technology instance, you will not be able to verify the Reporting Services installation until you have configured all integration settings.
  • If you install a SharePoint technology instance on a computer that already has Reporting Services, the Report Server Web service will stop working until you edit the port assignment for the default Web site as described in the next section.

For distributed server deployments where the report server, the SharePoint Web application, and the server databases on different computers, you can use default settings that include the default Web site and port assignments. If you install the SharePoint Web application and the report server on different computers, but you put the SharePoint databases on the report server computer, you will run into connection errors unless you follow the guidelines about service account configuration described in the previous section. For instructions, see How to: Configure Service Accounts (Reporting Services Configuration).

Edit the Port Assignment for the Default Web Site

Installing the report server and the instance of the SharePoint product or technology side-by-side on the same computer requires that each Web application is assigned to different ports. If you chose the Basic Installation option or used default settings when installing the SharePoint product or technology, the SharePoint Products and Technology Configuration Wizard stopped the default Web site and assigned port 80 to the top-level site when configuring the SharePoint Web application. If you want to use the default Web site for a report server, you must choose a different port for the default Web site and then start the Web site.

  1. Start IIS Manager.
  2. Right-click Properties on the Default Web Site.
  3. Specify a different port. By default, a SharePoint Web application uses port 80. To run the report server on the same computer under the default Web site, select a different unused port (for example, 8080).
  4. Start the Default Web site. By default, this site is stopped when you create and configure the site collection for the SharePoint Web application. You must restart this site to access the report server.
  5. If you already had a working installation of Reporting Services on the computer before you installed Windows SharePoint Services, verify that you can still access the report server by typing a URL that includes the port (for example, http://example-server-name:8080/reportserver). Although integration is not yet configured, the report server should still be operational as a stand-alone server.

Install Prerequisites on the Report Server

  1. Apply SP2 to the report server.
  2. If the report server and the SharePoint Web application are installed on separate computers, install and configure the Windows SharePoint Services object model on the report server computer. For instructions, see How to: Install the Windows SharePoint Services Object Model on a Report Server Computer.

Configure a Report Server for SharePoint Integration

  1. Start the Reporting Services Configuration tool and connect to the report server instance that you want to configure for SharePoint integration.
  2. For a new installation only, on the Report Server Virtual Directory page, select the default Web site and enter a name for the virtual directory.
    Skip the page for configuring the Report Manager virtual directory. You cannot use Report Manager with a report server that is configured for SharePoint integrated mode. After the report server is configured, the Report Manager Virtual Directory page will be disabled.
  3. On the Database Setup page, create a new report server database in SharePoint integrated mode by selecting the Integrate with SharePoint Products and Technologies option when you create the database. For instructions, see How to: Create a Report Server Database for SharePoint Integrated Mode (Reporting Services Configuration).
  4. Back up the encryption key.
  5. If you are working with a new installation of Reporting Services, you should also configure the unattended report processing account if you want to support unattended report execution. Optionally, configure the report server for e-mail delivery. For more information, see Configuring an Account for Unattended Report Processing and Configuring a Report Server for E-Mail Delivery.
  6. Close the Reporting Services Configuration tool.
  7. If you used the default Web site configured for a specific port, edit the RSReportServer.config file to add the port to the UrlRoot configuration setting (for example, http://example-server-name:8080/reportserver). Reporting Services uses the UrlRoot configuration setting to construct links in e-mail messages that resolve to reports that are processed on a report server.

After you configure the report server, the next step is to install the Reporting Services Add-in and configure integration settings for the SharePoint Web application.

Install the Reporting Services Add-in and Configure Integration on the SharePoint Web Application

You must be a SharePoint farm administrator to install the Reporting Services Add-in.

  1. Download and run the Setup program for the Reporting Services Add-in. For more information, see Installing the Reporting Services Add-in.
  2. In Administrator Tools, click SharePoint 3.0 Central Administration.
    Important:
    To complete the steps below, you must be a member of the SharePoint Farm Administrator group and a Site Collection administrator.

  3. Click Application Management. You should see a new section named Reporting Services. This section has links to pages used to specify integration settings. At a minimum, you must set the report server URL, select an authentication mode, and grant report server access to the SharePoint databases. For instructions, see How to: Set Report Server Integration in SharePoint Central Administration.
  4. Close Central Administration.

Verify the installation

Verify integration by starting the Reporting Services Configuration tool and opening the SharePoint Integration page to view server status. You should see a link that you can click to start SharePoint Central Administration.

You can also verify an installation by opening a browser window to the report server. The URL should be similar to http://example-server-name:8081/reportserver. This URL is to the proxy server endpoint. After you add report server items to a SharePoint library, you can open a browser window to this URL to view items on the site.

Finally, you should be able to open the SharePoint Web application and upload reports and other documents to a SharePoint library.

Add Report Server Items

  1. Open the SharePoint Web application. By default, the URL is http://<your-server-name>.
  2. Set permissions to grant user access to the SharePoint site. Security is required, but if you want to inherit existing permissions, you do not have to set permissions on specific items. For instructions, see Managing Permissions and Security for Report Server Items on a SharePoint Site.
  3. Add Reporting Services content types if you want to create new shared data sources, report models, and Report Builder reports. For instructions, see How to: Add Report Server Content Types to a Library (Reporting Services in SharePoint Integrated Mode).
  4. Create shared data sources to use with reports and models. For instructions, see How to: Create and Manage Shared Data Sources (Reporting Services in SharePoint Integrated Mode).
  5. Create shared schedules to use with subscriptions or unattended report processing. For instructions, see How to: Create and Manage Shared Schedules (Reporting Services in SharePoint Integrated Mode).
  6. Publish report definition files (.rdl), data source files (.rds), and report model files (.smdl) to a SharePoint library. You can also use the Upload command in a SharePoint library to upload .rdl and .smdl files, but not .rds files. Depending on your permissions, you can also create reports in Report Builder and save them to a library. If the reports include references to shared data sources or external files, you must update the references. For more information, see Deploying Reports, Models, and Shared Data Sources to a SharePoint Site and How to: Upload Documents to a SharePoint Library (Reporting Services in SharePoint Integrated Mode).

When you upload reports, make sure that the data source properties for the report are correct. The data source must have a valid connection string to an external data source. The credential type must be valid for your network topology. Specifically, if Kerberos authentication is not enabled for your domain, you cannot use the Windows integrated security credential type. Instead, you should specify stored credentials. After you verify the data source properties are correct, you can click the report name to open the report. The report will open in the Report Viewer Web Part automatically.

If the report does not open, check permissions on the report and the data source properties that define a connection to the external data source that provides data to the report. On a site or farm, SharePoint permissions are used to control access to reports, report models, shared data sources, and the Report Builder tool used to create ad hoc reports. If these items are not visible to users, you should verify that permissions are set correctly.

A report server that runs in SharePoint integrated mode cannot be managed through Report Manager or SQL Server Management Studio. For a complete list of supported and unsupported features, see Features Supported by Reporting Services in SharePoint Integration Mode.

See Also