I am using an excel workbook with pivot in it. I am using the Excess webpart to display it. As I click on the Refresh all data connection.

It throws an error.The scree shot is attached. enter image description here

I have configured the secure store service and Excel service application. Any Suggestions ??

Thanks in Advance

Regards Kishan

Now the error showing is :

Updated Error


Thanks for the reply @1c1cle , @Mauro and @Stuart Pegg.

I have looked into your recomendations. Finally I found where the problem is :

The current senario is :

I have installed SP2010 + Sql Server 2008R2 in server. I have an excel sheet which contains the pivot(the data comes from another sql server).

I want to show that excel file in Sharepoint. I am using the Excel Web Access webpart to show it, but on clicking the Refresh All Data conections, it throws an error. Firstly it was gving an error of Delegation. When i configured the Secure Store Service for Excel services, then the error changes to unable to refresh the Data Connection ,contact your system administrator.

Note: I tried to deploy powerpivot solution in SP 2010, but that solution was not found in the Farm Solution().

I am installing the Powerpivot(Sql Server) in existing setup.So please can you tell me how to proceed with the above mentioned scenario once when the installation of powerpivot is complete ?

Thanks In advance.

  • Kishan, could you try to perform a Excel connection refresh from a workbook outside a webpart? ( I mean, set up a XLSX file with the data connection, load the file to a document library, view xlsx in browswer). I would like to see if the issue has to do with your webpart or with your Secure Store and connections. Also, check out your IMLS logs.
    – 1c1cle
    Commented Jun 7, 2012 at 20:57
  • Much Thanks @1c1cle for the reply...!! I have checked it with the browser also, still it is throwing the same error. Just to check i have created the pivot with the Adventure Works database. However the pivot doesn't get refreshed. this time it's showing different error.The screeshot is attached in the Main Question for your reference. I have also checked the logs, but didn't find anything useful. Thanks
    – Kishan
    Commented Jun 8, 2012 at 16:18
  • To get the excel services refresh working, if I remember correctly, you need to save the data connection info as a data connection file in a trusted library connection in sharepoint. You can do this by, while in excel client view your data connection info, saving/exporting the connection to a file. then loading the to sharepoint. Then, later your excel file to point to that library location. Also ensure that your SSS field in your authenication settings in your data connection file is set to the name of the Unattended service store.
    – 1c1cle
    Commented Jun 11, 2012 at 14:40

3 Answers 3


The problem that causes this error is when the Claims to Windows token service is using a service account and not the Local System account. It's an easy fix that will get rid of the "Unable to refresh data for a data connection in the workbook" error. To fix the problem follow these steps:

Logon to the server you installed Powerpivot on

Click Start

Click All Programs

Click SharePoint 2010 Products

Click SharePoint 2010 Management Shell (run as administrator)

Type Get-SpServiceInstance (hit enter)

Find and copy the Claims to Windows Token Service ID

Type Get-SPServiceInstance -identity (hit enter)

Type $claims = get-spserviceinstance -identity (hit enter)

$claims.Service.ProcessIdentity.CurrentIdentityType=0 (0 will set the Claims to Local System account)(hit enter)

$claims.Service.ProcessIdentity.Update() (hit enter)

$claims.Service.ProcessIdentity.Deploy() (hit enter)

$claims.Service.ProcessIdentity (hit enter)

Once you have done this reboot your server and go test your workbook and see if it's refreshing the data in the workbook. It should now work with no problems and the nasty error will be gone forever.


If your Unattendend user account have access to your database the problem could be the position of your Excel file . If you save your Excel file on Sharepoint root site level can get error. Save your excel and put the Excel Viewer on a subsite site "blank template". If you have sharepoint 2010 +sp1 you must install October 25 2011 Culmulative update http://support.microsoft.com/kb/2596582.

  • Much Thanks @Mauro for reply. I will try this also. Please can you look up at the posted question above ? I have explanined the senario.
    – Kishan
    Commented Jun 11, 2012 at 15:14

I had same problem and resolved it by doing these steps:

  1. Go to Central Administration -> System Settings -> Manage services on the server
  2. Select Claims to Windows Token Service and click Start
  3. Verify that the Unattended User Account has access to the SQL data source
  • Much thanks @Mauro but i have Done this . Can you give some more detail about 3rd point ? for the 3rd Point i have given access to the Unattended User Account in Database. Any Suggestion ..??
    – Kishan
    Commented Jun 8, 2012 at 16:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.