The core of the problem is that unless you’ve set the connection to refresh when you first open the workbook, Excel Services uses its pivot cache to construct the pivot table and slicers and it is only if you manually refresh the connection, or click on a slicer, that you make an actual connection to the embedded data. Until then you are just looking at cached information. Until you click on a slicer, you don’t really know if Excel Services is working – so a strong recommendation that I make to any person doing a validating their installation is to “ALWAYS CLICK ON A SLICER” if you want to make sure that your installation is working properly.
The issue is that when accessing data PowerPivot looks like just another data source to both Excel desktop and Excel Services. Prior to accessing the data, if using Windows authentication, Excel Services needs to impersonate the user on the calling thread. But, in a claims-aware world, the only ‘identity’ that Excel Services has is the claims token. When the user connects (in whatever authentication method the SharePoint Web Application allows), the first thing that SharePoint does on the web front end is to translate the authentication method’s user identity to a claims token. And it is that claims token that is passed around within the farm. Remember this dialog box within Excel desktop:
The setting tells Excel Services what kind of lookup to perform when a new connection is started:
Quick Solution if you do not want to use claims
Set up a Secure Store Application ID using a Windows Account that has read access to the data
Set Application ID field to the above Secure Store ID viA Central Admin > Excel Services > Global Settings
Set the Workbook PowerPivot connection to SSID and enter the above Application ID under authentications setting via the Connections button in Excel
Dave Wickert advises how to fix (in order based on his experience of likelihood to be the root cause):
Is the Claims to Windows Token Service started on the server running Excel Calculation Service (ECS)? For detailed information on the Claims to Windows Token Service (c2wts), you can read the msdn article. This service is turned on when doing a “New Farm” installation of PowerPivot, but if you do an “Existing Farm” installation, or have Excel Calculation Service (ECS) running on a different machine, the Claims to Windows Token Service might not be started. Check to make sure that this service is running on every server on which ECS is running. You can validate this via the “Services on Server” option in SharePoint’s Central Administration web site but it is also important to make sure that this is running from Service Control Manager (SCM accessed via services.msc). There is a known issue after reboot where the c2wts fails to start because of an unexpressed startup dependency on the crypto service. I will add a link to the KB when it is available but until then you can add the dependency manually from SCM or from an administrative command prompt with “sc.exe config c2wts depend= cryptsvc”.This will prevent the problem from reappearing after your next reboot. As per http://powerpivotgeek.com/2010/01/18/why-you-shouldnt-stop-start-analysis-services-from-scm-when-running-in-sharepoint-integration-mode/, you should not manage SharePoint services from SCM, however SCM is the truth when it comes to whether a service is running and so you should always double check by looking at the state in SCM. If SharePoint indicates that the service is started but it is not actually running per SCM, it is safe to start it from SCM (alternatively you could stop and start it from SharePoint Central Administration). As well as simply managing this service, SharePoint configures the security permissions for this service automatically as part of their setup so that all SharePoint Shared Services (which includes ECS) can use it. If you find that the service is stopped on the machine running ECS, start it. After starting this service, you should not need to do any type of IisReset to see the system start working.
Is your machine connected to the network? Dave has written a good blog on this also (http://powerpivotgeek.com/2009/11/06/taking-your-server-off-the-network/). If you are actually trying to run a PowerPivot demo with a machine which is not on the network, follow the steps in Dave’s blog to configure ECS to use a set of stored credentials for the PowerPivot datasource. If you are not doing this on purpose, then connect back to the network.
The final cause would be that for some reason other than network connectivity, the Claims to Windows Token Service is not able to convert the SAML claims token to a Windows User security token. Dave provides a lot of details on these potential issues. Some quick questions to ask yourself:
The account being used as the Excel Services service account must have AD rights to be able to query the object. One place where we know this restriction comes into play is if you have configured your domain controller to have a subgroup under “Users”, e.g. “Service Accounts”, which is a separate AD group that derives from “Users” –> but I am sure that there are more. AD rights for service accounts is a common problem across all of SharePoint.
What Dave describes is one possibility where an AD configuration could cause this issue. As we discover more potential AD configurations that could cause this issue, we will try to update this list. If you are comfortable building your own test application and have gotten this far without figuring it out (and feel very confident that it is not #1), you can try running the test application we have posted here to manually test your ability to acquire a Windows Identity. UPDATE: Okay we have started to get some feedback from CSS on AD related issues that have caused this failure. Here is the beginning of a list
“The given key was not present in the dictionary” – Apparently this error is caused by a change in defaults in Win2008 R2. Note that I have never seen this error in any trace associated with the delegated error which I am discussing in this post as of yet but apparently it can be the root cause even if you can’t see it. I will try to add more as I get more information. Here is a link to an additional thread on this issue specifically relating to SP2010.
The following is beta info and needs to be updated.
In the case of Windows Authentication, to perform the lookup, Excel Services uses the “Geneva to Windows Token Service” (GTS) provided by SharePoint. GTS takes the claims token of the caller and translates it to the Windows identity of the caller (the underlying Windows API that is uses for this is S4U (see here: xx). Unfortunately S4U does have its restrictions, and those restrictions are the heart of the “Cannot Delegate” error message that we are seeing. GTS requires:
Denny Says to check this as well