Configuring Reporting Services for SharePoint 3.0 Integration

  • Article

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.

For more information about different server topologies, see Deployment Configurations for Reporting Services in SharePoint Integrated Mode.

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.

About Scale-out Deployment and SharePoint Integrated Mode

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 that 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. Because the services must connect to the SharePoint databases, the accounts that you can use to run the services will have different requirements depending on whether remote connections are used and whether the proxy endpoint connection uses Windows integrated security or Trusted Account mode:

  • A standalone server deployment that has the report server and the SharePoint product or technology installed on a single standalone server can use the default service account settings. By default, the Web service runs under the built-in account Network Service. This account has sufficient permissions to support a local database connection.
  • A distributed server deployment that has a SharePoint Web application on one computer and the report server instance and the SharePoint databases on a different computer will not work if the Report Server services use a built-in machine account such as Network Services or Local System. You must use domain user accounts for both the Report Server Web service and Windows service in this scenario, regardless of how the proxy endpoint is configured.
    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 domain user accounts ensures that the report server will not be denied database access because they are running under a prohibited account.
  • A distributed server deployment that has the report server and the SharePoint product or technology installed on different computers cannot use the default account for the Report Server Web service without additional configuration. The recommended approach is to configure the Report Server Web service to run as a domain user account. However, if you must use Network Service, you must add NT_AUTHORITY\NetworkService to the WSS_WPG Windows group on the report server computer. For more information, see Troubleshooting Configuration Errors.

Service account recommendations for Windows SharePoint Services and Office SharePoint Server state that for a server farm deployment you should run the application pool process as a domain user account, and not as Network Service. If the SharePoint Web application runs as Network Service and the report server is on a remote computer, you will encounter errors when accessing reports and report server features from a SharePoint site if the URL proxy endpoint connects in Trusted Account mode. For more information about service account configuration, see Plan for administrative and service accounts in the Windows SharePoint Services product documentation. For more information about server errors that occur when you deploy this configuration, see Troubleshooting Configuration Errors.

For more information about the service account connection to a SharePoint technology instance, see Security Overview for Reporting Services in SharePoint Integration Mode.

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.

About Multiple Instances and SharePoint Integrated Mode

If you are installing multiple instances of Reporting Services on a single computer, but only configuring one of those instances for SharePoint integrated mode, be sure to do the following:

  • Apply SP2 to both report server instances. SP2 introduces an updated version of the Reporting Services Windows Management Instrumentation (WMI) provider. You cannot have pre-SP2 and SP2 versions on the same computer. Therefore, it is necessary that all report server instances run at the SP2 level, even if you do not require SP2 features for all of the instances.
  • If you run SP2 Setup for each report server instance at different times, restart the WMI service after you install SP2 the second time.

Overview of Report Server Integration on a SharePoint Server

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. Click the following links to read the instructions for configuring server integration in specific deployment scenarios:

See Also

Tasks

How to: Configure the Report Server Integration Feature in SharePoint Central Administration
How to: Create a Report Server Database for SharePoint Integrated Mode (Reporting Services Configuration)
How to: Switch Server Modes (Reporting Services Configuration)
How to: Configure Service Accounts (Reporting Services Configuration)

Concepts

Backing Up and Restoring Encryption Keys
RSReportDesigner Configuration File
Deployment Modes for Reporting Services
Deployment Configurations for Reporting Services in SharePoint Integrated Mode
Managing Permissions and Security for Report Server Items on a SharePoint Site
Security Overview for Reporting Services in SharePoint Integration Mode
Deploying Reports, Models, and Shared Data Sources to a SharePoint Site

Help and Information

Getting SQL Server 2005 Assistance