How To Kerberos – Configure Constrained Delegation for Excel Services and PowerPivot in SharePoint 2013

I have a SharePoint farm consisting of two web front end servers, two application servers and one SQL server 2012 instance. On both application servers I have Excel Services and PowerPivot services running.

In order to successfully configure constrained kerberos delegation from the SharePoint farm to an external SQL data source you must configure constrained delegation on the application servers too.

Following are Microsoft best practices that I will keep in mind when configuring this:

Best Practices

• As a best practice you should run the C2WTS using a dedicated service account and not as Local System (the default configuration). Refer this link.
• Each server running excel services must be trusted to delegate credentials to each back-end service excel will authenticate with. Refer this link.
• The excel services service account must also be configured to allow delegation to the same back-end services. Refer this link.
• As a security best practice, Microsoft always recommends constrained delegation over unconstrained delegation. Unconstrained delegation is a major security risk because it allows the service identity to impersonate another user on any downstream computer, service, or application (as opposed to just those services explicitly defined via constrained delegation). Refer this link.

1. To connect to an external SQL data source via kerberos first set a SPN on the SQL Server. Refer this link.
   
2. Create a dedicated account for the Claims to Windows Token service which converts claims tickets to windows tokens for the external SQL Server to authenticate. Refer this link.
3. Create a dedicated account for Excel Services and configure the service for SharePoint. Refer this link.
4. Create a dedicated account for PowerPivot and configure. PowerPivot does not require the C2WTS service. Refer this link.

Example configuration

Lets assume we need to connect to an external SQL server as in the image below.

My SharePoint environment consists of the following:

Servers

• WEB001
• WEB002
• APP001 – running Excel, PowerPivot services
• APP002 – running Excel, PowerPivot services
• SQL001
• SQL004 (external data source to connect to)

Service Accounts

• coolwriteups\svcC2WTS – C2WTS service running only on APP servers
• coolwriteups\svcExcel
• coolwriteups\svcPwrPivot
• coolwriteups\svcSQL001
• coolwriteups\svcSQL004 (service account for SQL service on external data source)

Steps to configure

1. Set an SPN (What is an SPN?) for the SQL service on the external data source using the service account running the service.For example: SetSPN -S MSSQLSvc/SQL004.fully.qualified.domain.name:1433 coolwriteups\svcSQL004 provided 1433 is the port number in use. If you are connecting to an instance on the SQL server other than the default then use the port number of that instance instead.
2. For the Claims to Windows Token Service svcC2WTS, right click the service account to open the properties window and click the delegation tab. Check the “Trust this user for delegation to specified services only” and “Use any authentication protocol” and add the delegation MSSQLSvc/SQL004.fully.qualified.domain.name:1433 using the coolwriteups\svcSQL004 account.
3. Repeat the step above for the svcExcel and svcPwrPivot accounts.
4. For both the applications servers, right click the name of the servers in Active Directory and click the Delegation tab. By default, you should see “Trust this computer for delegation to any Service (kerberos only)” enabled. Change this to “Trust this computer for delegation to specified services only” and “Use any authentication protocol” and then add the delegation MSSQLSvc/SQL004.fully.qualified.domain.name:1433 using the coolwriteups\svcSQL004 account.

Testing

1. Open SQL Profiler and connect to the external SQL data source. Start the profiler and then refresh your PowerPivot or Excel Services dashboard. You should see the user account of the user who refreshed the dashboard in the trace.
2. Also, open Event Viewer on the external SQL data source and click the Security tab on the left. You should see the user’s account under “New Logon“, Kerberos under “Detailed Authentication Information” and the PowerPivot account under “Transited Services“.
3. If you see “ANONYMOUS LOGON” and NTLM as the Authentication Package then that means there is a problem and kerberos delegation hasn’t worked.

If you have any queries, post them in the comments section below and let the author or the community assist you.

Helpful links

• Reporting Services and Kerberos
• Excel Services and Kerberos
• Analysis Services and Kerberos
• Excel Services and Kerberos
• Service Principal Names
• Claims to Windows Token Service
• Analysis Services and Kerberos
• Kerberos Overview