• Article

How to: Install and Configure SharePoint Integration on Multiple Servers

Updated: September 2011

This topic provides step-by-step instructions for installing and configuring Reporting Services (SSRS) in SharePoint integrated mode in a two server deployment topology.

Important

The topic frequently references a single server topic for details as many of the steps are the same for one or two server deployments. See How to: Install and Configure SharePoint Integration on a Stand-alone Server

Tip

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

Hosting the application databases on a third server that is used exclusively by the Database Engine is a common technique for improving performance if you expect a relatively large number of users. For more information on using more than two servers, see Deployment Topologies for Reporting Services in SharePoint Integrated Mode

Prerequisites

Before you begin installing components, be sure to do the following:

Installation overview for a two server Deployment

There is more than one possible design for a two server deployment. You should decide which deployment is appropriate for your application and network usage

Two server deployment, example 1

One possible deployment design is to use one computer for all of the application components and the second computer only for the database components, as illustrated in the following diagram.

Note

This deployment requires a SQL Server installation on both computer 1 and computer 2. The same edition of SQL Server needs to be installed on the two computers.

Two-computer deployment

Two server deployment, example 2

Another deployment design is to use the first computer for only the SharePoint application components and install the SSRS server on the second computer along with the database components. This design is illustrated in the following diagram and is the design described in this article.

Note

This deployment requires a SharePoint product installation on both computer 1 and computer 2. The same edition of the SharePoint product needs to be installed on the two computers.

Two-computer deployment, example 2

The following is a summary of the components you install on the two computers:

  • First Computer: This server will be the primary server in the farm and SharePoint Central Administration will be installed on this server. Users will access report server content from the site collection on this computer.

    • An instance of a SharePoint product or technology

    • The Reporting Services Add-in for SharePoint products.

  • Second computer: This server will be used to host the report server along with the report server database and SharePoint application databases.

    • SQL Server 2008 R2 Reporting Services.

    • The SQL Server Database Engine.

    • SharePoint web server front-end components so the SharePoint object model can be used by the report server.

Account Requirements for a Multi-server Deployment

  • You must have a domain user account to connect the instance of the SharePoint product or technology to the SharePoint_config database. Server farms require that you use domain accounts for services and database connections. If possible, create a new account that can be used for just this application. Avoid using your own credentials or those of any user who will be accessing a SharePoint site as a regular user (you will get access denied errors).

  • You must create a SQL Server database login for the domain account. The login needs **DBCreator **permissions on the server.

  • You must configure the SharePoint application pool process account to run as a domain user.

  • You must configure the Report Server service to run as a domain user account.

  • Administrator credentials are required to run all Setup programs, to grant permissions to be the primary site collection administration, and to grant database access when you configure report server integration.

Installation Order

The following is the recommended installation and configuration order:

  1. **Computer 2:**Install SQL Server Reporting Services and SQL Server Database Engine.

  2. **Computer 2:**Install a minimal installation of a SharePoint 2010 product so the SharePoint object model is on the computer and available for the report server.

  3. **Computer 1:**Run the SharePoint 2010 Products Preparation tool. The Preparation tool will install the Reporting Services Add-in for SharePoint 2010 Products.

  4. **Computer 1:**Install SharePoint Server 2010 or SharePoint Foundation 2010.

  5. **Computer 1:**Run the SharePoint Products and Technologies Configuration Wizard and select the database server.

  6. **Computer 1:**Configure the site collection features in Central Administration and open the SharePoint site to verify you have a valid installation.

  7. **Computer 1:**Configure the report server integration features in Central Administration.

Computer 2: To install Reporting Services and the Database Engine

Computer 2: To install a SharePoint web server font-end on the SSRS server

Computer 1: To Run the SharePoint 2010 Products Preparation tool

Computer 1: To install a SharePoint 2010 product

Computer 1: To run the SharePoint Products Configuration Wizard

  • Complete the steps in the section ‘To run the SharePoint Products Configuration Wizard’ in the article How to: Install and Configure SharePoint Integration on a Stand-alone Server, with the following considerations.

    • On the Specify Configuration Database Settings page, enter the database server name. If you installed the Database Engine as a default instance on computer2, the database server name is the computer name of computer 2.

    • On the Configure SharePoint Central Administration page, click Next to accept all the defaults. If you know that impersonation and delegation features are enabled for Windows Authentication in your domain, you can select Negotiate (Kerberos) instead. Otherwise, select the default value and click Next.

    Top of installation order

Computer 1: To complete the Farm Configuration Wizard

Computer 1: To configure report server integration features in SharePoint Central Administration

You will get an error if the Reporting Services services are running under built-in accounts. If you already configured the services to use domain user accounts, restart the services to clear out old connection information (assuming that there are existing connections in the current session). For more information about service account configuration, see Configuring the Report Server Service Account.

Top of installation order

Optional: To download and install the Reporting Services Add-in

  • Note, the SQL 2008 R2 Reporting Services SharePoint 2010 Add-in is one of the products the SharePoint Products preparation tool (PreRequisiteInstaller.exe) will install. Therefore you should not need to download and install it separately. However the following information is provided in case you decide to vary the installation order or steps.

    The add-in rsSharePoint.msi can also be downloaded and installed separately. For more information, see Where to find the Reporting Services add-in for SharePoint Products and How to: Install or Uninstall the Reporting Services Add-in

    Important

    You must be a SharePoint Web farm administrator and Site Collection Administrator to install the Reporting Services Add-in.

    Top of installation order

Next Steps

After you install all the components and configure the settings for connecting server components, you must assign permissions, add content types, and add report server items to a SharePoint library.

To finish the deployment tasks for server integration

  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 more information, see Granting Permissions on Report Server Items on a SharePoint Site or How to: Set Permissions for Report Server Items on a SharePoint Site (Reporting Services in SharePoint Integrated Mode) in SQL Server Books Online.

  3. Add Reporting Services content types if you want to create new shared data sources, report models, and Report Builder reports. For more information, see How to: Add Report Server Content Types to a Library (Reporting Services in SharePoint Integrated Mode) in SQL Server Books Online.

  4. Create shared data sources to use with reports and models. For more information, see How to: Create and Manage Shared Data Sources (Reporting Services in SharePoint Integrated Mode) in SQL Server Books Online.

  5. Create shared schedules to use with subscriptions or unattended report processing. For more information, see How to: Create and Manage Shared Schedules (Reporting Services in SharePoint Integrated Mode) in SQL Server Books Online.

  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 Models and Shared Data Sources to a SharePoint Site and How to: Upload Documents to a SharePoint Library (Reporting Services in SharePoint Integrated Mode) in SQL Server Books Online.

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. 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. For a complete list of supported and unsupported features, see Features Supported by Reporting Services in SharePoint Integrated Mode in SQL Server Books Online.

Change History

Date

Change

September 2011

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

Many edits including updated references to SharePoint 2010 UI instead of the previous SharePoint UI, new diagrams, focusing the topic specifically on two server installs, and linking to the Single Server topic