Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Bradley Schacht

Bradley is a consultant at Pragmatic Works in Jacksonville, FL. He was an author on the book SharePoint 2010 Business Intelligence 24-Hour Trainer and tech edited the SQL 2011 Bible. His experience on the Microsoft BI platform includes DTS, SSIS, SSRS, SSAS and migrations and conversions. He has helped numerous companies in successfully developing and implementing new business intelligence solutions into their organizations. Bradley also participates as a speaker in community events like SQL Saturday, Code Camp, SQL Lunch and SQL Server User Groups. He is a contributor on sites such as BIDN.com and SQL Server Central as well as an active member of the Jacksonville SQL Server User Group (JSSUG).

PowerPivot Data Connection Error

PowerPivot report returns the following error from Excel Services:
The data connection uses Windows Authentication and user credentials could not be delegated. The following connection failed to refresh: PowerPivot Data

When viewing a PowerPivot workbook in SharePoint 2010 the report is visualized using Excel Services since ultimately the report is inside an Excel workbook.  The root cause of this error is that Excel Services is unable to get a valid Windows account from whoever is running the report. After some research I found the following on MSDN.  I really wish I could take credit for finding all of these circumstances, but let’s be honest; I don’t have that much time on my hands. You will see this error when one of the following occurs:

  • I have found that this is the most common reason: Claims to Windows Token Service is not running
  • No domain controller is available to validate the Windows account
  • The computers are not members of the same domain
  • Excel Services account must have Active Directory permissions to query the object

Since the most common issue (again in my experience here) is the Claims Token Service is not running I’ll show you how to fix that and hopefully you will be back to PowerPivot-ing in no time.

To enable the Claims to Windows Token Service in SharePoint 2010:

  1. Open SharePoint 2010 Central Administration
  2. Navigate to the System Settings section
  3. Click Manage services on server
  4. Click Start next to Claims to Windows Token Service

When the Claims to Windows Token Service is stopped it will look like the screenshot below.

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

Comments

Leave a comment on the original post [www.bradleyschacht.com, opens in a new window]

Loading comments...