Identity delegation for SQL Server Reporting Services (SharePoint Server 2010)

Applies to: SharePoint Server 2010

In this scenario you configure a pair of load-balanced SQL Server Reporting Services (SSRS) servers in a scaled-out configuration running in SharePoint integrated mode. The servers are configured to accept Kerberos authentication and they delegate authentication to a back-end SQL Server cluster.

In this scenario, the SharePoint Server farm and Reporting Services data source are both in the same domain; therefore in this scenario we configure Kerberos constrained delegation to allow identity delegation to the back-end data source. If you are required to authenticate with data sources in other domains within the same forest, you have to configure basic (unconstrained) Kerberos delegation. Remember that Reporting Services does not leverage the C2WTS and therefore can use basic delegation.

Scenario dependencies

• Scenario 1: Core Configuration

• Scenario 2: Kerberos Authentication for SQL OLTP

• (Optional) Scenario 3: Kerberos Authentication for SQL Analysis Services

Configuration checklist

Scenario environment details

In this scenario, the Internet Information Services (IIS) application pool service accounts are configured to delegate to the SQL Server Reporting Services (SSRS) service. The SSRS service account is configured to delegate credentials to the SQL Server service. Note that SQL Server Reporting Services in SharePoint integrated mode does not leverage intra-farm Claims authentication and requires Kerberos authentication for delegated authentication. For more information, see Claims Authentication and Reporting Services.

Cross-domain Kerberos delegation

In this example, the data source that SSRS connects to resides in the same domain as the SSRS servers. In some situations you may want to access data sources outside of the domain that SSRS resides in. To authenticate with delegation cross domain, you have to configure basic (unconstrained) delegation on the SSRS service account. Remember that this is possible because the SSRS service does not rely on the Claims to Windows Token Service (C2WTS), therefore does not require protocol transition through Kerberos constrained delegation. Also note that cross-forest delegation is not possible, even with basic delegation.

Step-by-step configuration instructions

Configure DNS

Configure DNS for the SSRS NLB server group in your environment. In this example we have two SSRS servers, VMSSRS01 and VMSSRS02, which are load-balanced and resolve to the same NLB VIP (192.168.24.180/24). The VIP will be mapped to the host FarmReports and will have the URL http://FarmReports .

For general information about how to configure DNS, see Managing DNS Records.

Configure a new DNS A Record for the SSRS host. In this example we have a host FarmReports configured to resolve to the load balanced VIP.

Active Directory directory service

Create SSRS service account

As a best practice, SQL Server Reporting Services should run under its own domain identity. In this example, the following accounts were created:

Configure Service Principal Names

For SSRS to connect and authenticate with external data sources using Kerberos authentication, the Report Server Web Service and Report Manager service accounts and the service account for the external data source must have service principal names configured. Refer to scenarios 1 and 2 (Core configuration and Kerberos authentication for SQL OLTP) in this series of articles to configure and validate the necessary SPNS on the SharePoint Server web applications and SQL Server service accounts. For the SSRS servers, the following SPNs were defined:

In this example the following commands were executed:

SetSPN -S HTTP/FarmReports vmlab\svcSQLRS

SetSPN -S HTTP/FarmReports.vmlab.local vmlab\svcSQLRS

Configure delegation

Kerberos delegation must be configured for SSRS to delegate the client's identity to back-end data source. In this example, SSRS queries data from a SQL Server transactional database by using the client's identity, therefore Kerberos delegation is required. Kerberos constrained delegation (KCD) is not a requirement in this scenario (because protocol transition is not needed), but KCD is configured as a best practice.

The SSRS service account that is running the SSRS services must be trusted to delegate credentials to each back-end service. In our example, the following delegation paths are needed:

Optionally, if you wish to report against Analysis Services data sources, configure the following delegation paths:

To configure constrained delegation

1. Open the Active Directory Object's properties in Active Directory Users and Computers.

2. Navigate to the Delegation tab.

3. Select Trust this user for delegation to specified services only.

   Note

   For the SSRS service account, if you need to authenticate with data sources within the same forest but outside of the domain that the SSRS server resides in, configure basic delegation instead of constrained delegation. You can do this by selecting Trust this computer for delegation to any service. Remember that cross-forest Kerberos delegation is not possible.

4. Optionally select Use any authentication protocol. This enables protocol transition.

5. Click the Add button to select the service principal that can be delegate to.

6. Select User and Computers.

7. Select the service account that is running the service you want to delegate to. In this example, it is the service account for the SQL Server Reporting Service.

   Note

   The service account selected must have an SPN applied to it. In our example, the SPN for this account (HTTP/FarmReports.vmlab.local) was configured earlier in the scenario.

8. Click OK. You are then asked to select the SPNs you want to delegate to on the following page.

9. Select the service or Select All and click OK.

   You should now see the selected SPNs in the services to which this account can present delegated credentials list:

10. Repeat these steps for each delegation path identified earlier in this section. You have to configure delegation from the SQL Server Reporting Services service account to one or more back-end data sources (SQL OLTP or SQL AS in our scenarios).

    Note

    For the SSRS service account, if you need to authenticate with data sources within the same forest but outside of the domain the SSRS server resides in, configure basic delegation instead of constrained delegation. To do so, select Trust this computer for delegation to any service. Remember that cross-forest Kerberos delegation is not possible.

Verify MSSQLSVC SPN for the service account running the service on SQL Server (performed in Scenario 2)

Verify that the SPN for the Analysis Services service account (vmlab\svcSQL) exists by using the following SetSPN command:

SetSPN -L vmlab\svcSQL

You should see the following:

MSSQLSVC/MySqlCluster MSSQLSVC/MySqlCluster.vmlab.local:1433

Verify MSOLAPSvc.3 SPN for the Service Account running the SSAS service on the SQL Server Analysis Services server (performed in Scenario 3)

Verifythat the SPN for the SQL Server service account (vmlab\svcSQLAS) exists by using the following SetSPN command:

SetSPN -L vmlab\svcSQLAS

You should see the following:

MSOLAPSvc.3/MySqlCluster MSOLAPSvc.3/MySqlCluster.vmlab.local

SQL Server Reporting Services

Install SharePoint Server 2010

SQL Server Reporting Services requires SharePoint Server 2010 to be installed on each SSRS server to run SSRS in SharePoint integrated mode. Install SharePoint Server 2010 on each reporting server and join each server to the SharePoint Server farm.

Install and configure SSRS in load-balanced, scaled out mode

Detailed step by step instructions on how to configure SQL Server Reporting Services in a load-balanced, scaled-out configuration is beyond the scope of this document. For detailed instructions on how to install SSRS, see Deployment Topologies for Reporting Services in SharePoint Integrated Mode. Once SSRS is installed, be sure to complete the additional SSRS configuration steps outlined below to complete the install.

Modify Web.config on the SSRS Servers

The following changes have to be made to the web.config files on each SSRS server. The web.config file can be found in the Program Files directory where SSRS is installed:

Add the <machineKey> element

SSRS servers in a load-balanced configuration need the same machine key set across all servers. The machine key element should be added as a child of the <system.web> element in web.config. Below is an example machine key:

<machineKey
validationKey="54AEBD3BC893726E9B84D30F4970CB58F2086C2DAEE2F8D34A65A0632F4676DDBBC38779F2972C6596931E
13BD07A772BD4B9395BE38A43E461079E45D594E53"
decryptionKey=""
validation="SHA1"
decryption="AES"
/>

Modify ReportingServer.config

The following changes have to be made to the ReportingServer.config files on each SSRS server. The ReportingServer.config file can be found in the program files directory where SSRS is installed:

Enable Kerberos authentication

To enable Kerberos authentication, set the authentication type to "RSWindowsNegotiate". Change the <AuthenticationTypes/> element and add <RSWindowsNegotiate/>:

<AuthenticationTypes>
    <RSWindowsNegotiate/>
</AuthenticationTypes>

Modify the URL root

Add the URL for the report server to the <UrlRoot> tag found in the <service> tag of ReportingServer.Config

<UrlRoot>http://FarmReports/reportserver</UrlRoot>

Configure BackConnectionHostNames in the registry

To allow SQL Server Reporting Services to authenticate with each other on a single computer, NTLM loopback detection needs to be addressed. Instead of disabling loopback detection, a better practice is to configure the BackConnectionHostNames value in the registry of each SSRS server. For more information about BackConnectionHostNames, see You receive an error message when you use SQL Server 2008 Reporting Services:.

In our example, we configure the following values for BackConnectionHostNames:

• FarmReports

• FarmReports.vmlab.local

Once the BackConnectionHostNames values are set, reboot the SSRS server.

Configure SharePoint Server

In Central Administration, you find the farm configuration options for SSRS. Note that in SharePoint Server 2010 you do not need to install a separate SSRS component installation for SSRS administration and Web Parts. To access the SSRS farm options, navigate to Central Administration and then see Reporting Services in the General Application Settings section.

Grant the Reporting Services service account permissions on the web application content database

A required step in configuring SQL Server Reporting Services in SharePoint integrated mode is allowing the Reporting Services service account access to the content databases for web applications hosting reports. In this example, we grant the Reporting Services account access to the "portal" web application's content database through Windows PowerShell.

Run the following command from the SharePoint 2010 Management Shell:

$w = Get-SPWebApplication -Identity http://portal

$w.GrantAccessToProcessIdentity("vmlab\svcSQLRS")

Configure Reporting Services Integration

In the Reporting Service Integration dialog box, specify the load-balanced URL of the report server. Also, select the Activate feature in all exiting collections option to automatically activate the Reporting Services feature in your site collections.

Add each report server to the integration

In the Add a report server to the integration dialog box, specify each of the nodes of the Reporting Services NLB group. You have to open this dialog box for each server that you are adding to the integration; there is no way to add multiple servers in a single operation.

Set server defaults

At this point SSRS integration should be configured. To validate the configuration, open the Server Defaults page. No changes are required for the example in this document.

Verify configuration

Create a document library for reports

Create a document library to host SSRS reports in your SharePoint site. In this example, we assume the existence of a document library called "reports" at http://portal/reports.

Validate site collection settings for Reporting Services

In the browser, navigate to the Site Settings of the site that is hosting the document library for SSRS reports. In Site Settings you should see a new category called Reporting Services.

If you do not see the Reporting Services feature in the site collections features list, you may need to activate it from Central Administration. For more information, see How to: Activate the Report Server Feature in SharePoint Central Administration (https://go.microsoft.com/fwlink/p/?LinkId=196878).

Click the Reporting Services site settings link to ensure the settings are accessible.

Create and publish a test report in SQL Server Business Intelligence Development Studio

After you configure SSRS and the integration with SharePoint Server, you create a test report to ensure identity delegation is working correctly.

1. Open SQL Server Business Intelligence Development Studio. Click File, point to New, and then click Project.

2. Select Report Server Project Wizard and enter a project name.

3. Next configure a new data source. Choose the type Microsoft SQL Server and click the Edit button.

4. In Connection Properties enter the information to connect to the demo SQL Server cluster created in scenario 2.

5. Open query designer, right-click the query window and select Add table.

6. Choose the Sales table (created in scenario 2) and select All Columns.

7. Select a tabular report type.

8. In our example we group by region; you can skip this step if you want to.

9. Once the project is created, open the project properties on the Project menu.

10. Configure the following project properties:

    1. TargetDatasetFolder — Set to the test report folder created earlier

    2. TargetReportFolder — Set to the test report folder created earlier

    3. TargetReportPartFolder — Set the to test report folder created earlier

    4. TargetServerURL — Set to the web application URL that is hosting the report

11. Deploy the report to the SharePoint library. On the build menu select Deploy <project name>.

12. If it is successful, you will see the deployment succeeded message in the Output window.

View the test report in Internet Explorer

Open the report document library created in previous steps of this scenario in the browser. You should see the report file you just published. If you do not see the report, you may need to activate the Reporting Services features in your site collection. For more information, see How to: Activate the Report Server Feature in SharePoint Central Administration (https://go.microsoft.com/fwlink/p/?LinkID=196878).

Click the report and it will render in the browser.

To further verify delegation and the data connection, changed the source data in SQL Server Management Studio and refresh the SSRS report data connection in the browser. You should see the data changes reflected in the report.

SSL configuration for Reporting Services

In some environments it may be required to protect communications between front-end Web and SSRS servers with SSL. A detailed walkthrough of how to configure SSL for Reporting Services is out of scope for this paper, but at a high level these are the steps you have to take:

1. Configure each reporting server for SSL. See Configuring a Report Server for Secure Sockets Layer (SSL) Connections (https://go.microsoft.com/fwlink/p/?LinkId=196881).

2. Update ReportingServer.config. Change the <UrlRoot> to the new https:// URL.

3. Restart the SQL Server Reporting Services service.

4. In Central Administration, change the Reporting Services integration settings and change the Report Server Web Service URL to the new https:// URL.

5. Restart IIS on each instance of SharePoint Server that is running the web application service.

You do not need to change any of the SPNs created when configuring Reporting Services with HTTP in the previous steps. The SPN for an HTTP service over SSL remains HTTP/<service>. You can see this by using NetMon to view the front-end web server that is communicating with the Reporting Services Server.