Identity delegation for PowerPivot for SharePoint 2010 (SharePoint Server 2010)

Applies to: SharePoint Server 2010, Excel Services

The farm topology described in Environment and farm topology does not require Kerberos authentication for PowerPivot for Microsoft SharePoint 2010 to work. The PowerPivot System Service is claims aware, and uses the Claims To Windows Token Service (C2WTS) to recreate the client’s Windows identity using the client’s claims token in order to connect with the Analysis Service Vertipaq engine that runs on the application server.

When a PowerPivot workbook is uploaded in SharePoint Server, it already contains the PowerPivot data that the workbook uses. When the user opens the PowerPivot workbook in Excel Web Access and interacts with the slicers, the PowerPivot System Service loads the data in the workbook directly into its Analysis Services engine. No access is made to the data connection embedded in the workbook.

When a data refresh job for a PowerPivot workbook starts executing, the PowerPivot System Service performs a Windows login using the credentials stored in the SharePoint Server Secure Store Service. Since the Windows identity is created on the application server, the connection from the PowerPivot Analysis Services Vertipaq engine (on the same computer, VMSP10APP01) to MySQLCluster is the first NTLM hop.

Scenarios requiring Kerberos authentication

As you can see from the discussion above, most common situations with PowerPivot do not require Kerberos authentication. However, there are some unusual edge cases where Kerberos authentication would be required. For example, if your PowerPivot workbook contains a data connection to a SQL Server instance that is linked to yet another SQL Server instance on a separate computer, you will need to configure Kerberos authentication with identity delegation for data refresh to work. For example, if MySQLCluster is linked to another remote SQL Server instance, then the link from MySQLCluster to the linked remote server is the second hop. In this case, NTLM is no longer adequate. You must configure Kerberos delegation for the data refresh to process successfully.

While they are outside the scope of the scenarios defined in this paper, the major steps to configure identity delegation for PowerPivot are as follows:

1. Change the service account of the C2WTS Windows service to a domain account (e.g. VMLAB\svcC2WTS). Configuring the C2WTS is a large topic and is covered in detail in the other scenarios in this document:

   • Configure and Start the Claims to Windows Token Service on Excel Services Servers

   • Configure and Start the Claims to Windows Token Service on Visio Graphics Servers

   • Configure and Start the Claims to Windows Token Service on PerformancePoint Services Servers

2. Configure delegation from the VMLAB\svcSQL account to the SPN for the linked SQL Server instance Configuration Checklist.

Scenario dependencies

Strictly speaking, the following Kerberos authentication scenarios are not required by PowerPivot for SharePoint. However it expedites your PowerPivot for SharePoint installation process if you successfully completed them, as the components themselves are prerequisites for PowerPivot for SharePoint.

• Scenario 1: Core Configuration

• Scenario 2: Kerberos Authentication for SQL OLTP

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

• Scenario 5: Identity Delegation for Excel Services

Configuration instructions

Install PowerPivot for SharePoint on the application server (vmsp10app01). For detailed instructions, see How to: Install PowerPivot for SharePoint in a Three-tier SharePoint Farm in the MSDN Library online. If you have already performed the dependent scenarios in this paper, you can skip the sections in the MSDN article that have already been covered by the scenario dependencies.