SharePoint 2013 With SSRS 2012 And Constrained Delegation

I often get asked about how to configure the SharePoint 2013 BI stack (Excel Services, PerformancePoint and SSRS 2012 integrated mode). To do this configuration Kerberos with constrained delegation is required and often this is the most tricky and unclearly documented piece. In this post I hope to provide some insight into the steps to configure these pieces.

First, I will assume that SharePoint 2013 is installed and for the purposes of my test environment I have set a domain controller, a SQL 2012 server, and two SharePoint 2013 servers (Application and Web). 

 

So the first step I will suggest taking is to install SQL 2012 SP1 on the SQL server as well as the patch from KB 2793634. Without doing this step SharePoint will not show the SQL Reporting Services Service Application when you try to create it in Central Admin and it will not initialize the service in SharePoint. If you have already run into that step because you installed SSRS on SharePoint first, don’t worry; install SP1 for SQL 2012 and the patch on the SQL server and on your SharePoint servers (Yes, I did say your SharePoint servers – When you put SSRS in your farm they have SQL bits on them).

Once you have installed the patches I typically install SSRS on my SharePoint servers next. In the case of this small three server farm the Application server (SPApp2013) will become the server where I plan to run the SSRS engine but the Web server (SPWeb2013) will also get the SSRS add-in installed on it. If I was running more servers in the farm I would need to install the add-in there as well and if I was wanting to have a load balanced SSRS service application I would install the SSRS engine on another server as well. When installing SSRS engine from the SQL Server 2012 media (my preferred method) select the following features:

• Reporting Services – SharePoint
• Reporting Services Add-in for SharePoint Products
• Management Tools – Basic & Complete

These three are the for the application server. Again, for the web server you only need the middle bullet above (Reporting Services Add-in). Once SSRS is installed and patched on your servers you should see the option in Central Admin to setup the service application. If you don’t see this option open the SharePoint Management Shell and run the following:

• Install-SPRSService
• Install-SPRSServiceProxy
• get-spserviceinstance -all |where {$_.TypeName -like "SQL Server Reporting*"} | Start-SPServiceInstance

Then try to create the service application again. From there the service application creates like any other service application, however; because I haven’t mentioned it yet, another assumption I had is that each service application has its own application pool and own service account associated with it. Once it is a security best practice but will also be important for constrained delegation. In the case of this environment I used the following service accounts for service applications:

Service:	Service Account:
https://dragonlair	Campbushnell\svcSPWADragon
Claims To Windows Token Service	Campbushnell\svc_SPC2WTS
SSRS Service	Campbushnell\svcSQLSSRS
SQL Engine	Campbushnell\svcSQLEng
Excel Services	Campbushnell\svcSPExcel
Performance Point Service	Campbushnell\svcSPPerfPnt

I haven’t touched Claims to Windows Token Service yet, but it is coming, and yes, required. Once I have the service application up and running I like to go make sure my service is started on the application server. To do this go to System Settings à Manage Services on Server. If it isn’t started, start it on the application server. Next I need to go change the authentication method it will use from NTLM to Kerberos so we can use pass through authentication (otherwise known as the double hop issue). To do this I need to make changes to RSReportServer.config file.

• File is located in C:\Program Files\common files\Microsoft Shared\Web Server Extentions\15\Web Services\Reporting
• Change AuthenticationTypes from RSWindowsNTLM to RSWindowsNegotiate
• Save the file
• IIS Reset on all servers

So the last step for SSRS (and should be done for the Excel and PerformancePoint as well) is to give the service account permissions to the databases in the web applications. I also need to give permissions to other locations such as SSAS cubes or SQL databases you will be accessing, but that’s a bit out of scope for this discussion. Run the following to give the permissions:

• $w = Get-SPWebApplication -Identity https://dragonlair              
• $w.GrantAccessToProcessIdentity("campbushnell\svcSQLSSRS")

So now that SSRS is done I can move on to Claims to Windows Token Service (C2WTS). There are a couple things to do before getting started… Permissions and local security policy (so if I have a group policy in place that overrides what is changed it can cause issues – SharePoint is “special” so I typically try to set it up without a group policy and then enforce/lock down after). On SPApp2013 I add the C2WTS to the local administrators group where PerformancePoint is running and grant the account the following permissions in the local security policy:

• Act as part of the operating system
• Impersonate a client after authentication
• Log on as a service

Next I need to add the WPS_WPG Windows Group to the c2wtshost.exe.config file. The file is located \Program Files\Windows Identity Foundation\v3.5\c2wtshost.exe.config

Once I do that I go to the services running in Windows and set the Claims to Windows Token Service to Automatic. Don’t start it here because SharePoint will just stop it as SharePoint services are really controlled from timer jobs and the settings in the configuration database. Instead, I navigate to Manger Services on Server in Central Admin and start the service on SPApp2013. Once it is started an IIS reset or server reboot is required. 

Next I move on to the Service Principle Names (SPNs) and Delegations. Because I want to pass credentials through SharePoint/SSRS to the data source I need to make the web application Kerberos as well. Before I make the change in SharePoint I need to have the SPNs and Delegations in place or the web application will not allow access. Earlier I mapped out the Services I need to setup Kerberos for and the service accounts I will use. The syntax for the SPNs (2008 or greater domain controllers) is: SetSPN –S ServiceClass/Host:Port Domain\ServiceAccount. So for this environment I setup (don’t forget to also do FQDN):

• Setspn -S HTTP/dragonlair Campbushnell\svcSPWADragon
• Setspn -S HTTP/dragonlair.campbushnell.lcl Campbushnell\svcSPWADragon
• Setspn -S MSSQLSvc/SQL2012:1433 Campbushnell\svcSQLEng
• Setspn -S MSSQLSvc/SQL2012.campbushnell.lcl:1433 Campbushnell\svcSQLEng
• Setspn -S MSSQLSvc/SPSQL:1433 Campbushnell\svcSQLEng
• Setspn -S MSSQLSvc/SPSQL.campbushnell.lcl:1433 Campbushnell\svcSQLEng
• Setspn -S SP/C2WTS Campbushnell\svc_SPC2WTS
• Setspn -S SP/SSRS Campbushnell\svcSQLSSRS
• Setspn -S SP/PPS Campbushnell\svcSPPerfPnt
• Setspn -S SP/Excel Campbushnell\svcSPExcel

Once the SPNs are in place I can access the delegations tab for the service accounts. I generally like to map it out first so it looks like:

Account:	Service:	Account Running Service:
Campbushnell\svc_SPC2WTS	MSSQLSvc Service	campbushnell\svcSQLEng
Campbushnell\svcSQLSSRS	MSSQLSvc Service	campbushnell\svcSQLEng
Campbushnell\svcSPExcel	MSSQLSvc Service	campbushnell\svcSQLEng
Campbushnell\svcSPPerfPnt	MSSQLSvc Service	campbushnell\svcSQLEng

Notice there is a lack of OLAP or other database server in this example. In non-test environments often connecting to an OLAP cube or other database server is done. For those instances I just add the appropriate SPNs and add the applicable delegations. In Active Directory the delegations look as like the picture for all the service accounts listed in the table above. 

After Kerberos is setup from the Active Directory side I have to setup the web application to use Kerberos rather than NTLM. To do so I navigate to Manage Web Applications in Central Admin, select the web application then Authentication Providers from the ribbon. The dialog to choose the zone appears and I select default. Then scroll down to Claims Authentication Types and change the Integrated Windows Authentication to Negotiate (Kerberos). IIS Reset is always a good step on all SharePoint servers at this point. Then I test to ensure I can still get into my web application. If not, I did something incorrect with Kerberos and need to troubleshoot it. 

 

Once all of that is done I can start my testing to ensure SSRS can pass my credentials to the database server and bring a report back and render it to me as a user. The first step to that is ensuring I have read access to the database. I change that at the SQL Server database that I am trying to access in this case but that will be more defined in real world scenarios. After that I will start Report Builder (for testing purposes I do that on SPApp2013) to create a SSRS report and data source which I will upload into a document library in SharePoint. Below shows the location of report builder in case you are testing the same way. 

Once I create the report I save it to a SharePoint document library where the Report Server Integration feature is enabled. Typically I don’t open the report to test if Kerberos is passing credentials, but that is a way as long as you are sure the report works properly. I usually go into the data source (Manage Data Sources) for the report and click Test connection. If it comes back with a Test Successful message I am good and the report should work without an issue.   

Comments

• Anonymous
  January 01, 2003
  Hi Ryan,Is it WPS_WPG or WSS_WPG. I had detailed blog (http://rajeshagadi.blogspot.com/2013/07/configuring-security-for-ssrs-in.html) referencing your this post and I assumed it should be WPS_WPG. But I do not seem to get any reference for how this group gets created in the first place. Please advise.ThanksRajesh

• Anonymous
  July 04, 2013
  I notice that you don't mention Report Builder in this post ... does SP2013 and SQL2012 solve the ClickOnce installer issue that prevented using ReportBuilder from non-domain machines in SP2010?

• Anonymous
  September 23, 2013
  Thanks for documenting all this. One question, though, what exactly does "SPSQL" refer to? I assume it's the machine running SSRS (thus the SQL add-in) but am not 100% sure. Thanks!

• Anonymous
  November 08, 2013
  Joy, SPSQL refers to the SQL Alias running on the SQL Server rather than SSRS.  SSRS is actually running on the App01 server.

• Anonymous
  November 12, 2013
  Hi Ryan I have configured all my service applications (Excel, PerformancePoint etc) to run under one service application account. I see in your article above you have configured a SPN for Excel account and one for PerformancePoint. Can I set an SPN for the one service account I am using for all my service applications? What will be the problem if I do it this way? Thanks Yoshi

• Anonymous
  November 27, 2013
  Yoshi, I don't see a reason off hand why using a single service account like you are suggesting would harm anything from the SPN/Delegation standpoint.  I always run multiple accounts to increase security levels but there can be some advantages for performance using a single application pool rather than multiple.  

• Anonymous
  March 31, 2014
  This is works thanks

• Anonymous
  April 04, 2014
  What about InfoPath forms? Is it using HTTP or using a "SP/InfoPath"? We are hitting external data through InfoPath using web services. Do we need to set up the constrained delegation for the backend SQL behind those web services?

• Anonymous
  May 19, 2014
  A couple of weeks ago, a customer asked me how to setup BI capabilities in SharePoint and what were the

• Anonymous
  June 24, 2014
  I'm annoyed by all these articles claiming C2WT delegation to various external services being necessary.
  C2WT does not delegate. It merely acts as a mediator for the user credential between the SharePoint claims and the Windows token. No transitioning through C2WT ever happens.
  
  See http://support.microsoft.com/kb/2722087 for explanation:
  
  "In the example above we see that we have added delegation to a SQL Server SPN. It could be set to any SPN. The C2WTS does not delegate any identity to SQL. C2WTS just gets a Windows identity from a UPN. C2WTS needs to be able to create a token that can be delegated which means, it needs to have the UF_TRUSTED_TO_AUTHENTICATE_FOR_DELEGATION bit set… To have the UF_TRUSTED_TO_AUTHENTICATE_FOR_DELEGATION bit set, you need to have the C2WTS identity having the constrained delegation with protocol transitioning enabled but it can be to any SPN (even a dummy one)."

• Anonymous
  June 24, 2014
  Sorry for failed name above - please delete post :)
  
  I'm annoyed by all these articles claiming C2WT delegation to various external services being necessary.
  C2WT does not delegate. It merely acts as a mediator for the user credential between the SharePoint claims and the Windows token. No transitioning through C2WT ever happens.
  
  See http://support.microsoft.com/kb/2722087 for explanation:
  
  "In the example above we see that we have added delegation to a SQL Server SPN. It could be set to any SPN. The C2WTS does not delegate any identity to SQL. C2WTS just gets a Windows identity from a UPN. C2WTS needs to be able to create a token that can be delegated which means, it needs to have the UF_TRUSTED_TO_AUTHENTICATE_FOR_DELEGATION bit set… To have the UF_TRUSTED_TO_AUTHENTICATE_FOR_DELEGATION bit set, you need to have the C2WTS identity having the constrained delegation with protocol transitioning enabled but it can be to any SPN (even a dummy one)."

• Anonymous
  July 17, 2014
  You have a typo in your SSRS Reporting path, it should be C:Program FilesCommon Filesmicrosoft sharedWeb Server Extensions15WebServicesReporting

• Anonymous
  August 01, 2014
  I found that all your steps almost correct with the exception of modifying file RSReportServer.config AuthenticationTypes from RSWindowsNTLM to RSWindowsNegotiateto. If you do the change, in the SSAS profiler you will see anonymous. Once I revert you modification of the file to its original authentication type, it show the correct user.

• Anonymous
  September 25, 2014
  Ryan - question regarding connecting to other data sources for SSRS.
  
  If I read this correctly, it suggests adding a delegation to C2WTS and SSRS accounts to the SPN associated to the remote data source?
  
  Can you clarify what additional SPN and delegations need to be set in order to pass credentials to what would be an external (that is, outside of the SP farm) data source.
  
  Thanks

• Anonymous
  October 23, 2014
  Hello Ryan,
  
  Would it possible to show report parameter section on top (Like header)in integrated mode in SharePoint 2013 and SSRS 2012? Right now, everything is working fine just parameter section is coming in right-side of page.
  I am searching it on so many forums but did not get any solution :( .
  
  Any help would be appreciated.
  
  Thanks!

• Anonymous
  January 09, 2015
  Intro I’ve been meaning to write this blog for some time now so I finally got around to doing it. In