The data connection uses Windows Authentication and user credentials could not be delegated

This is one of the two main errors that users could see from Excel Services when using PowerPivot. This is encountered when refreshing PowerPivot data connections or performing an action which requires re-querying the PowerPivot database, such as clicking on a slicer or expanding a node in a pivot table. To debug, some level of understanding of what Excel Services is doing is required. For this, I recommend reading an earlier post on this blog by Dave … http://powerpivotgeek.com/2009/12/11/excel-services-delegation/. In general, this is an add-on to Dave’s post which is a quick summary of how to debug this error.

Here is a quick list of most likely causes for this error and how to debug and fix them (in order based on my 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:
  • Is the client user account (the logged in user who is browsing the workbook in IE) a domain account? If the account is a local machine account, then the Claims to Windows Token service will not be able to retrieve a Windows user security token. We do not support this scenario in V1 of PowerPivot. Interactive users must be domain users. For demo purposes in a bind you might try the workaround Dave provided for taking the server off of the network, but I have not personally tested it in this case.
  • Is the client user account in a different domain than the SharePoint servers? This is completely supported, but there must be a trust relationship established between the two domains. You could verify if a missing trust issue is causing your problems by logging in as a user account in the same domain as the SharePoint servers and try interacting with the workbooks (note that you had to have given that user access to the workbook). If it works for users in the same domain but not for users in other domains, it might be an issue with cross domain trust. Contact your domain admin to figure out what the relationships are setup as.
  • What account is the Claims to Windows Token Service running as? By default it is configured to run as Local System, and I am not aware of the reasons for changing this configuration (the msdn article also refers to the fact that it should be running as Local System). While there might be a good reason for trying to change it, it is possible that the person who altered it did not understand the implications of this change. You should probably track down the person who changed it and get an understanding of why. If you have permissions, switch it back to Local System and try the scenario again. If it works, you will need to determine why it was changed in the first place.
  • If you have gotten this far and none of the above have solved your issue, then there is the possibility that you have some custom AD configuration which is causing the issue. Dave points out one possibility:
    

    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.

Hopefully this list will help you quickly debug and fix this issue on your system. HTH

Lee