• Article

Integrating SQL Server 2008 SP2 Report Servers with SharePoint 2010

SQL Server 2008 Service Pack 2 (SP2) updates the SharePoint integration features of SQL Server Reporting Services report servers. SQL Server 2008 SP2 report servers can be integrated with SharePoint 2010 products as well as SharePoint 2007 products. The SharePoint 2010 features supported by the SQL Server 2008 SP2 Reporting Services integration are the same features supported when integrated with SharePoint 2007. Feature support is also dependent on SharePoint 2010 backward compatibility support. Consult your SharePoint documentation for backward compatibility information.

Updated SQL Server 2008 SP2 Report Server Will Integrate with SharePoint 2010 Products

SharePoint 2010 support was added in the SQL Server 2008 Cumulative Update #8 (CU8). This SharePoint 2010 support is also part of SQL Server 2008 SP2.

Important

SharePoint 2010 Claims based authentication is not supported by SQL Server 2008 or SQL Server 2008 SP2 report servers. Use SQL Server 2008 R2 Reporting Services if you need to use a Claims enabled SharePoint 2010 Web application. See the known issues section for more information.

Considerations for Upgrading SharePoint

If you upgrade to SharePoint 2010 and you do not also upgrade your SQL Server instance to SQL Server 2008 R2, the following are recommended upgrade steps.

Starting environment: SharePoint 2007, SQL Server 2008 Reporting Services Add-in for SharePoint 2007 products, and SQL Server 2008 report servers

  1. Upgrade all report servers that are configured for SharePoint mode to SQL Server 2008 SP1 CU8 (or higher) or SQL Server 2008 SP2.

  2. Upgrade all the Web Front-end (WFE) servers to the SQL Server 2008 R2 Reporting Services add-in for SharePoint 2010 products. You can install the new add-in by installing the SharePoint 2010 prerequisite installer. The SharePoint 2010 prerequisite installer will install the SQL Server 2008 R2 Reporting Services add-in for SharePoint 2010 products. The installation of the Reporting Services add-in for SharePoint will upgrade and remove the previous version of the Reporting Services add-in.

  3. Upgrade SharePoint on all SharePoint WFE and report server

Ending environment: SharePoint 2010, SQL Server 2008 R2 Reporting Services Add-in, SQL Server 2008 SP1 CU#8 report servers or SQL Server 2008 SP2 report servers.

Change Reporting Services service account to a domain account

SharePoint 2010 products require domain accounts and do not support the NetworkService account when deployed in a farm environment. If your Reporting Services service account is not configured for a domain account, the integration step will fail in SharePoint Central Administration. The issue does not apply to stand-alone installations of SharePoint 2010 products, only in a farm deployment.

How to integrate SharePoint 2010 with SQL Server 2008 SP2 report servers

Following the installation of the Reporting Services add-in for SharePoint 2010 products, you need to integrate the report server with the SharePoint server. Integrating a SQL Server 2008 SP2 report server with SharePoint 2010 products requires two steps:

  1. Configure and activate the Reporting Services integration:

    1. Open SharePoint Central Administration.

    2. Click General Application Settings.

    3. Click Reporting Services Integration.

    4. Type the appropriate values in the Report Server Web Service URL, Authentication Mode, and Credentials settings.

    For more about how to activate the Reporting Services integration, see How to: Configure Report Server Integration in SharePoint Central Administration.

  2. Establish access from the SharePoint environment to the report server databases. To add the report server to integration and grant database access:

    1. Open SharePoint Central Administration.

    2. Click General Application Settings.

    3. Click Add a Report Server to the integration.

    4. Type the appropriate values and click OK.

Known issues with SharePoint 2010 integration

This section documents the known issues when using SQL Server 2008 SP2 Reporting Services SharePoint integration.

Report Builder 1.0 and 2.0 will not start when SQL Server 2008 SP2 server is integrated with SharePoint 2010

The Reporting Services add-in for SharePoint 2010 configures the SharePoint environment to use Report Builder 3.0. SQL Server 2008 and SQL Server 2008 SP2 report servers are not compatible with Report Builder 3.0. If you integrate a SQL Server 2008 SP2 report server with SharePoint 2010 the click-once versions of Report Builder 1.0 and 2.0 fail to start. To correct the issue, configure Custom Report Builder Launch URL to the appropriate version of Report Builder.

To change the Custom Report Builder Launch URL

  1. Open SharePoint Central Administration.

  2. Click General Application Settings.

  3. Click Set server defaults in the Reporting Services section.

  4. For Report Builder 2.0, type /_vti_bin/ReportBuilder/ReportBuilder_2_0_0_0.application in the Custom Report Builder Launch URL text box. For Report Builder 1.0, type /_vti_bin/ReportBuilder/ReportBuilder.application.

  5. Click OK.

You see an error message regarding an ‘unknown’ value when you try to edit report parameters

When you try to update the properties of a report parameter from the Manage Parameters page of a SharePoint document library, you see an error message similar to the following: Requested value ‘unknown’ was not found. This is a known issue when working with SharePoint 2010 products that are integrated with SQL Server 2008 SP2 report servers.

SharePoint 2010 Claims based authentication is not supported

Claims based authentication is a new feature in SharePoint 2010 products; however Claims authentication is not supported by SQL Server 2008 SP2 report servers. Integration of SQL Server 2008 SP2 report servers with SharePoint 2010 supports Windows authentication only. This section documents known issues you may encounter with SharePoint 2010 sites that are configured for Claims based authentication.

  • Clickthrough reports will not work from SharePoint sites that are configured for Claims based authentication. When you try to click through a report you will see an error message similar to the following:

    “The permissions granted to user 'REDMOND\USERNAME’ are insufficient for performing this operation. (rsAccessDenied)”.

  • You cannot directly access your reportserver. When you try to browse to http://<RS server>/ReportServer you will see an error message that resembles the following:“The specified user <Domain>\<User Name> could not be found."To work around this issue, use the appropriate _vti_bin form of the URL instead of browsing directly to the report server. For example, use URL http://<SharePoint_site>/_vti_bin/ReportServer.

  • Links that are included in e-mail that was created by a subscription will fail. Subscriptions sent from a SharePoint 2010 site configured for Claims authentication will include links that cannot properly authenticate back to the Claims enabled site.