Events
Microsoft 365 Community Conference
May 6, 2 PM - May 9, 12 AM
Skill up for the era of AI at the ultimate community-led Microsoft 365 event, May 6-8 in Las Vegas.
Learn moreConfigure SQL Server Reporting Services in SharePoint Server for Kerberos authentication
Original KB number: 2723587
This article describes how to configure SQL Server Reporting Services in Microsoft SharePoint Server for Kerberos authentication.
As a best practice Reporting Services should run under its own domain identity. To configure the Reporting Services Service Application, an Active Directory account must be created and registered as a managed account in SharePoint Server. In this example, the following account is created and registered.
| Name | Identity |
|---|---|
| SharePoint Server Service | IIS App Pool Identity |
| SQL Server Reporting Services | vmlab\svcRS2012 |
Note
You can optionally reuse a single domain account for multiple services. This configuration is not covered in the following sections.
The Active Directory Users and Computers MMC snap-in is typically used to configure Kerberos delegation. To configure the delegation settings within the snap-in, the Active Directory object being configured must have a service principal name applied. Otherwise, the delegation tab for the object will not be visible in the object's properties dialog. Although Reporting Services doesn't require an SPN to function, we will configure one for this purpose. If the service account already has an SPN applied (in the case of sharing accounts across services), this step isn't required.
On the command line, run the following command:
SETSPN -S SP/PPS vmlab\svcRS2012
Note
The SPN isn't a valid SPN. It's applied to the specified service account to reveal the delegation options in the AD users and computers add-in. There are other supported ways of specifying the delegation settings (specifically the msDS-AllowedToDelegateTo AD attribute) but this topic will not be covered in this document.
Configure Kerberos constrained delegation from the Reporting Services service account to the SSAS Service and optionally for SQL Server service
To allow Reporting Services to delegate the client's identity, Kerberos constrained delegation must be configured. You must also configure constrained delegation with protocol transition for the conversion of claims token to Windows token via the WIF C2WTS.
Each server running Reporting Services must be trusted to delegate credentials to each back-end service with which Reporting Services will authenticate. In addition, the Reporting Services service account must also be configured to allow delegation to the same back-end services. Notice also that HTTP/Portal and HTTP/Portal.vmlab.local are configured to delegate in order to include a SharePoint list as an optional data source for your Reporting Services reports.
In our example, the following delegation paths are defined:
| Principal type | Principal name |
|---|---|
| User | Vmlab\svcC2WTS |
| User | vmlab\svcRS2012 |
To configure constrained delegation, follow these steps:
Open the Active Directory Object's properties in Active Directory Users and Computers.
Navigate to the Delegation tab.
Select Trust this computer for delegation to specified services only.
Select Use any authentication protocol.
Click the Add button to select the service principal.
Select Users and Computers.
Select the service account running the service you wish to delegate to (SQL Server, SQL Server Analysis Services, or both).
Note
The service account selected must have an SPN applied to it. In our example, the SPN for this account was configured in a previous scenario.
Click OK.
Select the SPNs you would like to delegate to, and then click OK.
You should now see the selected SPNS in the services to which this account can presented delegated credentials list.
Repeat these steps for each delegation path defined in the beginning of this section.
Before creating a Reporting Services service application, start the Reporting Services service on the designated Farm servers. To learn more about Reporting Services configuration, see Install Reporting Services 2016 in SharePoint mode.
Open Central Administration.
Under services, select Manage services on server.
In the server selection box in the upper right hand corner, select the server(s) running Reporting Services.
Start the SQL Server Reporting Services service.
Next configure a new Reporting Services service application and application proxy to allow web applications to consume Reporting Services:
Open Central Administration.
Select Manage Service Applications under Application Management.
Select New, and then click SQL Server Reporting Services Service Application.
Configure the new service application. Be sure to select the correct service account or create a new managed account if you didn't perform this step previously.
You can create and register a new service account for an existing application pool dedicated for Reporting Services before this step or when you create the new Reporting Services service. To associate the service account with an existing application pool dedicated to Reporting Services or verify an existing account, do the following.
Navigate to SharePoint Central Administration. Find Configure managed accounts in the Security section.
Select the drop-down box and select the application pool.
Select the Active Directory account.
A required step in configuring SharePoint Server 2010, Office Web Applications is allowing the web application's service account access to the content databases for a given web application. In this example, we will grant the Reporting Services account access to the portal web application's content database by using Windows PowerShell.
Run the following command from the SharePoint 2010 Management Shell:
$w = Get-SPWebApplication -Identity http://portal
$w.GrantAccessToProcessIdentity("vmlab\svcRS2012")