SharePoint 2010 Troubleshooting: Errors Refreshing PowerPivot (SQL 2008)

  • Article

 

Symptoms

When attempting to refresh a PowerPivot workbook in the browser, users a receiving one of the the below errors.

User credentials could not be delegated

Error message

Error message :"The data connection uses Windows Authentication and user credentials could not be delegated.  The following connection failed to refresh: PowerPivot Data"

Possible causes

Is most like caused by the "Claims to Windows Token Service" not being started on the machine running Excel Services and PowerPivot.  You need to start this via Start > Run > Services.msc.

Unable to refresh data

Error message

"Unable to refresh data for a data connection in the workbook.  Try again or contact your system administrator.  The following connections failed to refresh: PowerPivot Data"

Possible causes

Is caused by one of two things:

  1. Missing Data Providers
  2. Claims to Windows Token Service fails to convert a Claims Token to a Windows Token.

 

Troubleshooting

1. Missing Data Providers

A machine running Excel Services needs to have the Claims to Windows Token Service (C2WTS) started.  If this machine is not running PowerPivot (SQL Server Analysis Services & SQL Server PowerPivot System Services), it must have the ADOMD.NET (ADOMDClient.dll) and Analysis Services OLE DB provider (msolap100.dll). 

 These 2 providers are CRITICAL (see below).

PowerPivot for Excel

http://msdn.microsoft.com/en-us/library/ee210644.aspx

Install these here:

Microsoft® SQL Server® 2008 R2 SP1 Feature Pack

http://www.microsoft.com/download/en/details.aspx?id=26728

 

These are located here:

  • C:\Program Files\Microsoft Analysis Services\AS OLEDB\10\msolap100.dll 
  • C:\Program Files\Microsoft.NET\ADOMD.NET\100\Microsoft.AnalysisServices.AdomdClient.dll

2. Claims To Windows Token Service

We next need to look at the Claims to Windows Token Service. Look in IIS to see if it is running as LOCAL SYSTEM or a domain account. If it is running as a domain account, we recommend:

Log onto the servers which are running Claims to Windows Token Service (C2WTS) and give the C2WTS the following permissions:

 a. Add the service account to the Local Administrators Groups.

b. In local security policy (secpol.msc) > User Rights Assignment give the C2WTS account these permissions:

i. Act as part of the operating system 

ii. Impersonate a client after authentication

iii. Log on as a service (if you have started it before from within Central Admin, this may already be the case) 

Restart IIS

If you are still seeing the issue, you can run Rodney Viana's C2WTS tool:

Troubleshooting Claims to Windows NT Token Service (c2WTS) in SharePoint 2010 may be difficult if you don’t know where to start

http://blogs.msdn.com/b/rodneyviana/archive/2011/07/19/troubleshooting-claims-to-windows-nt-token-service-c2wts-in-sharepoint-2010-may-be-difficult-if-you-don-t-know-where-to-start.aspx

 

If you see an issue with the C2WTS, you may want to consider temporarily changing the account running the C2WTS back to LOCAL SYSTEM via the SharePoint Management Shell.

Launch the SharePoint Management Shell from the SharePoint Server.

Run the following command-let to view a list of Services: Get-SPServiceInstance

 

 

Find and copy the Id of the Claims To Windows Token Service.  Copy by right clicking inside the Powershell window and choose Mark.  This will allow you highlight-copy the Id with your mouse cursor.  After highlighting the Id, press Enter on your keyboard. 

Test your Id by running the following command-let. 

Get-SPServiceInstance �������identity <Paste the C2WTS Id>

Right-click>Paste the Id you copied earlier.

 

 

Next set a variable by running this command-let:

$claims = get-spserviceinstance –identity <Paste the C2WTS Id>

 

Run these command-lets to reset the C2WTS back to Local System:

$claims.Service.ProcessIdentity.CurrentIdentityType=0 // The 0 here is IdentityType.LocalSystem

$claims.Service.ProcessIdentity.Update()

$claims.Service.ProcessIdentity.Deploy() 

$claims.Service.ProcessIdentity 

// This output demonstrates the command-let was successful

CurrentIdentityType  : LocalSystem

CurrentSecurityIdentifier  : S-1-5-18

ManagedAccount  :

ProcessAccount  : S-1-5-18

Username  : NT AUTHORITY\SYSTEM

 

If changing the C2WTS from a domain account to LOCAL SYSTEM remedies your issue, then you an point your finger at the account running PowerPivot not having access to the User's Account. 

Locate the account that is running PowerPivot (via Central Administration > Security > General Security > Configure Service Accounts): 

  • Open Active Directory Users and Computers 
  • Select ”Advanced Features” from the View Menu 
  • Open the properties of the desired account 
  • Select the ”Security” tab 
  • Find Authenticated Users and give them READ permission on the user object 
  • Click OK 

 

 

 

Additional Articles