• Article

Configuring Reporting Services for SharePoint 2010 Integration

Updated: September 2011

You can configure a deployment of SQL Server Reporting Services to work with a deployment of Microsoft SharePoint Foundation 2010 or Microsoft SharePoint Server 2010. You must perform configuration steps on both a report server and a deployment of the SharePoint product 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. It contains the following sections:

  • Overview of Configuration Steps on a Report Server

  • Overview of Configuration Steps on a SharePoint Server

  • Service Account Configuration

  • How to Configure Server Integration

For more information about different server topologies, see Deployment Topologies for Reporting Services in SharePoint Integrated Mode in SQL Server Books Online. For a comprehensive list of topics about SharePoint integration in SQL Server Books Online, see How Do I Learn About SharePoint Integrated Mode.

Overview of Configuration Steps on a Report Server

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:

  1. Review and understand the security model needed for your environment. For more information regarding Reporting Services security, see Security Overview for Reporting Services in SharePoint Integrated Mode. For more information on using Kerberos SharePoint authentication, see Configure Kerberos authentication (SharePoint Server 2010).

  2. Create a report server database in SharePoint integrated mode. This 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.

  3. For deployments that include a SharePoint server farm, install a SharePoint Web front-end on the report server computer. The Web front-end joins the report server computer to a SharePoint farm. Installing the Web front-end on the report server computer is only necessary if you deploy the report server and the SharePoint technology instance on separate computers.

  4. For deployments that include placing the SharePoint databases on the same computer as the report server, configure the Report Server service to run under a domain account. This step is only necessary if the report server and application databases are one computer, and the SharePoint Web application is on another computer.

Note

SharePoint 2010 products and technologies in a farm configuration require domain accounts for service configurations like Reporting Services SharePoint integration.

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 in SQL Server Books Online.

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 SharePoint Foundation 2010 or SharePoint Server 2010 instance never connects to or retrieves data from the report server database.

About Report Server Scale-out Deployment and SharePoint Mode

If you are integrating a report server scale-out deployment with a SharePoint server farm, 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. Furthermore, you must create a single point of entry to the scale-out deployment (that is, a URL that resolves to a virtual IP for an NLB cluster on which the report server instances are installed).

About Multiple Report Server Instances and SharePoint 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:

  • Ensure that all instances of Reporting Services are the same version. SQL Server 2008 Reporting Services introduces an updated version of the Reporting Services Windows Management Instrumentation (WMI) provider. You cannot have SQL Server 2008 Reporting Services and pre-SQL Server 2008 Reporting Services versions on the same computer. Therefore, it is necessary that all report server instances run SQL Server 2008 Reporting Services or later, even if you do not require SQL Server 2008 Reporting Services features for all of the instances.

  • If you run SQL Server 2008 R2 Setup for each report server instance at different times, restart the WMI service after you install the second instance of SQL Server 2008 Reporting Services.

Overview of Configuration Steps on a SharePoint Server

To configure integration on the server running the SharePoint product or technology, you must do the following:

  1. Download and install the Reporting Services Add-in for SharePoint Technologies. The Add-in is also part of the SharePoint 2010 pre-requisite installer. If the add-in was successfully installed as part of the SharePoint pre-requisite installation then you do not need to install the add-in separately.

    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.

  2. Activate the report server feature if you did not install the add-in on a root site collection.

  3. Use SharePoint Central Administration to configure report server integration. Configuration settings include specifying a single report server URL, setting an authentication type on requests directed to the report server, granting database access permissions to the Report Server service account.

  4. Optionally, in SharePoint Central Administration, you can set report server defaults that enable logging, ad hoc reporting, and limits for report history and time-out values.

SharePoint products 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.

Tip

SharePoint developer tools only support a SharePoint standalone configuration. For more information, see the SharePoint documentation: Requirements for Developing SharePoint Solutions.

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.

Service Account Configuration

A report server is implemented as a single Windows service that runs under a built-in account or a local or domain Windows user account. In SharePoint integrated mode, the service must connect to the SharePoint content databases with write and execute permissions. Because the service connects to the SharePoint content databases, the account that you use to run the service will have different requirements depending on whether remote connections are used and whether the report server URL proxy endpoint connection uses Windows integrated security or Trusted Account mode.

The following table provides service account recommendations for different deployment scenarios.

Deployment scenario

Services

SharePoint databases

Account recommendation for Report Server service

Single server

All services are installed on the same computer

On same computer

Any (NetworkService, domain user, local user, LocalSystem).

Because the applications connect using a local database connection, you can configure the service to run under any account type.

Distributed deployment on multiple computers

SharePoint Web application and the report server installed on separate computers

On a third computer

-- Or --

SharePoint server computer

Domain user account

-- Or --

NetworkService, but only if you add NT_AUTHORITY\NetworkService to the WSS_WPG Windows group on the report server computer. For more information, see Troubleshooting Configuration Problems in SQL Server Books Online.

Distributed deployment on multiple computers

SharePoint Web application and the report server installed on separate computers

On report server computer

Domain user account is required, regardless of how the report server URL 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 service runs under a built-in account, it will be denied access accordingly. Configuring the service to run as domain user account ensures that the report server will not be denied database access because it is running under a prohibited account.

Note

The report server database is not included in the discussion of service account requirements for database connections. This is because only the report server connects to the report server database.

About SharePoint Service Account Configurations

Service account recommendations for SharePoint Foundation 2010 and SharePoint Server 2010 state that for a server farm deployment you should run the application pool process as a domain user account, and not as Network Service. Although a domain user account is recommended, it is not required. However, it is important to realize that if you configure a SharePoint Web application to run 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 report server URL proxy endpoint connects in Trusted Account mode. For more information about server errors that occur when you deploy this configuration, see Troubleshooting Configuration Problems in SQL Server Books Online.

For more information about the service account connection to a SharePoint technology instance, see Security Overview for Reporting Services in SharePoint Integrated Mode in SQL Server Books Online. To read about the service account recommendations for a SharePoint deployment, see Plan for administrative and service accounts in the SharePoint product documentation.

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:

Change History

Date

Change

September 2011

Added notes regarding SharePoint developer tools only support a SharePoint standalone configuration.