We have a small SharePoint 2010 environment consisting on 1 WFE, 1 APP Server and 1 Database Server. PowerPivot is installed on the APP server. The relevant services are started as follows:
- Excel Calculation Services – WFE
- Claims to Windows Token Service – WFE
- SQL Server Analysis Services – APP
- SQL Server PowerPivot System Service – APP
Note: I had to play around with where these services were started and this was the only combination that worked for me.
Our environment is patched to:
- SharePoint 2010 with SP1, June 2011 CU.
- SQL Server 2008 R2 with SP1
Authentication Type: NTLM
Excel Web Services and the Claims to Windows Token service run on the same domain account, domain\sp.services. The domain account is not part of the local administrators group on the WFE or APP server.
You try to access the PowerPivot Management Dashboard in Central Administration but the Excel Web Services Webpart contains no data. The error message you get is “The data connection uses Windows Authentication and user credentials could not be delegated. The following connections failed to refresh: <connection name>“. You check the ULS Logs and see the following error:
SPSecurityContext.WindowsIdentity: Could not retrieve a valid windows identity for NTName=’domain\username’, UPN=’firstname.lastname@example.org’. UPN is required when Kerberos constrained delegation is used.
The dashboard is also unable to display the chart webpart and all you get is a big red X.
In my case, the account (sp.services) used to run the Excel Web Service and the Claims to Windows Token Service did not have sufficient rights on the APP Server and WFE.
Grant the correct permissions to sp.services
- Login to the servers which run the Claims to Windows Token Service and Excel Web service.
- Go to Start -> Run -> Secpol.msc
- Select Local Policies -> User Rights Assignment
- Add sp.services to the following policies: Act as part of the operating system, Impersonate a client after authentication, Log on as a service.
- Add the sp.services account to the local administrators group on the same servers.
To fix the issue with the chart webpart:
- Goto Central Administration -> Security -> Configure Service Accounts.
- Under Windows Service – Microsoft SharePoint Foundation Sandboxed Code Service, change the service account to sp.services or ensure that the running account has the same rights as above.
- Restart the Microsoft SharePoint Foundation Sandboxed Code Service.
While it is not best practice to have a service account as a local administrator. This was the only way I managed to resolve the problem. Feel free to comment if you managed to resolve your problem using a different method.