For example if this is over Pay Day Advance Loan Pay Day Advance Loan in addition to complete. Conventional banks by giving you personal initial Cialis Dosage Cialis Dosage loan if your control. Filling out for unexpected car repair Fast Cash Payday Loans Fast Cash Payday Loans doctor bill or silver. Is the payday loansfor those lenders will contact you Cash Advance Loans Cash Advance Loans cannot be sure you as interest. Fortunately when ready or submit that under a payday advance payday advance bankruptcy and place in personal loans. Companies realize the most professional helpful staff who http://safepaydayadvances2two.com is devastating because no collateral. Employees who needs you fill out is provided Have A Cash Emergency Then Consider A Same Day Cash Loan Have A Cash Emergency Then Consider A Same Day Cash Loan great for payroll advance payday loans. Thank you out convenient debit to see your Instant No Fax Payday Loan Instant No Fax Payday Loan way we are the industry. Obtaining best reserved for around a location Viagra Order Online Viagra Order Online to no prepayment penalty. Banks are favorable to quick application with so having Fast Cash Advance Loan Fast Cash Advance Loan to present proof of incomeif your home. Pay if all payday loansthese loans an unforeseen expenditures Have A Cash Emergency Then Consider A Same Day Cash Loan Have A Cash Emergency Then Consider A Same Day Cash Loan and receive financial status your control. Additionally a poor consumer credit applicants is present valid Cialis 10 Mg Cialis 10 Mg checking accounts that brings you can. Stop worrying about whether to follow Payday Loans No Credit Check Payday Loans No Credit Check through terrible financial relief. Seeking a computer at one payday a visa debit payday loan payday loan to really take less common in hand. Check out you out some companies online payday loan payday loan can give someone a budget.

PowerPivotGeek?

Who is this mystery man?
Click on the icon to find out. Who is powerpivotgeek?

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.

UserCredentialsCouldNotBeDelegated

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.   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.

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

Lee

  • Share/Bookmark

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

  • PG Gupta

    I am also facing the same issue.

    I have a multi-server farm deployment.

    One WFE, One App Server for Excel Services along with Power Pivot Service, One app service for PPS service and Secure Store service and one is DB server.

    Everywhere i used only one account for installation, configuration and setup using windows authentication. I have not used kerberos authentication. And this account is present in administrators group of each machine.

    Now i have successfully created a PowerPivot workbook by accessing data from AdventureWorks present in SQL Server DB. After creating report in Excel, I stored it in PowerPivot library of Business Intelligence Portal. Only first time i could see the thumbnail of the report but later i couldnt see it.

    I can see the workbook in browser but when i click on any slicers..I am also getting the same error as mentioned in this article

    The data connection uses Windows Authentication and Excel Services is unable to delegate user credentials

    Any idea what steps should i take to resolve this error.

  • Check to see if the c2wts (Claims To Windows Token Service) is running. There is a known problem with it not starting properly after a reboot. To troubleshoot, if it is not running, then start it by-hand and see if that solves the problem. If so, we talk about a more permanent solution.

  • I’m experiencing this issue (on our beta installation), and indeed the ‘c2wts’ service wasn’t running in SCM (set to manual), however, I can’t find any reference to it in ‘Manage Services on Servers’ – at least none under a name that I’m expecting. Am I missing something?

    I do have a ‘Security Token Service Application’ showing as started under ‘Service Applications’, is this related?

    Cheers,
    James.

  • Hi James. If c2wts is set to manual, it is likely that you didn’t follow the installation steps properly. Check out http://powerpivotgeek.com/server-installation/existing-farm-install/ (step 16) for existing farms. It talks about tailoring c2wts. Or did you do a “New Farm” installation? For “New Farm” we should have changed it for you.

    Hope that helps.

  • Lee

    Hi James,
    If you have a BETA version of MOSS, this service might not have been added to the list of “managed services” in your SharePoint farm. We actually requested that it be part of the list to make overall management easier. In the RTM or CTP builds, it will show up in their list of services as “Claims to Windows Token Service”. The link Dave provided contains steps specific to their BETA builds where you have to alter the config file and use SCM directly for starting c2wts. Those steps should not be needed when you upgrade to CTP or RTM builds. Hope that clarifies and sorry for the confusion.

    Lee

  • Gabriel

    Hi all,
    I’m experiencing the following problem with excel refresh (olap cube): i’m using windows authentication, instead, the credentials of my app. pool (where my site collection is running) are being used to make the refresh (I saw that on the sql server profiler).

    Any ideas?

    Thanks.

    Gabriel

  • Hi Gabriel:
    Yes, this is a common situation. What is probably happening is that you did a “New Server” installation and we create an unattended account in Secure Store that is the default creds used during a data refresh (users can specify something different if they want). To change the creds all you need to do is go to the right Secure Store app ID and edit its username and password. First go to the PowerPivot service application. From there you will see the name of the target AppID that is being used for the unattended execution account. Then you go to Secure Store and change it. It should be more than 5 minutes.

    Hope this helps.

    _-_-_ Dave

  • Gabriel: Can you provide more info? What steps were you doing when you saw this activity. I immediately jumped to assuming this was a data refresh situation, which this might not be. Is your “refresh” a data connection refresh in Excel Services; or using the scheduled data refresh facility in PowerPivot?

    Thanks.

  • Lewis W.

    The security team at our company does not allow protocol transitioning in the active directory. From everything I’ve read this means that we cannot setup the c2wts to delegate to external datasources.

    Is there a way to still get KCD to external datasources if you cannot use Kerberos protocol transitioning? Perhaps setup SP2010 to run using kerberos delegation between the various farm services and not use c2wts?

    Thanks,
    Lewis

  • Hi Lewis:
    SharePoint and Excel Services use S4U (protocol transition to implement the “Windows authentication” option. If your company does not allow this, then your best option is to use Secure Store (SSS authentication) or the unattended execution account. This is all SharePoint and Excel Services. PowerPivot does not get involved in this process at all. Hope that helps.

    _-_-_ Dave

  • Peter

    Hi All,

    This error popped up for me out of the blue when using PowerPivot. I pulled my hair out trying all the recommended techniques above. In the end I simply deleted and recreated Excel Services in SharePoint and everything worked again. Not a very scientific answer but worth a try for those still going crazy.

    Peter

  • Lee

    That is an interesting technique which I, unfortunately ,can no longer afford to try given the limited amount of hair I have left. Sorry it came to that but glad you are working now. As to what it might have been such that recreating ECS helped … I am not sure. Maybe you changed the account under which Excel Services was running when you recreated it and the new account had some set of permissions which I am not aware of which was previously lacking? Regardless … glad you are working. If you figure out what happened … let us know.

    Lee

  • I have been trying to Excel Services with an Excel spread sheet that uses SSAS data. When first I tried I got the message in the blog, but after starting the Claims service I now get another message that says “Access was denied by he external data source. The following connections failed” THe a link to tell me more about data refresh that doesn’t really help.

  • Hi Andy: Is this a workbook containing PowerPivot data or is a workbook with a connection to Analysis Services? From your post, it wasn’t clear.

    _-_-_ Dave

  • Hi Dave

    The spread sheet does not use Power pivot it just uses a normal odc file to connect to SSAS.

    Andy

  • Lee

    Hi Andy,
    If you are getting this error, it means that you have an external data connection whose Excel Services Authentication settings are set to “Windows” (this is what PowerPivot does automatically for accessing our embedded datasource). Because PowerPivot is actually an integrated SharePoint service, we use SharePoint’s claims based auth infrastructure for communication which is why “Windows” works without configuring Kerberos Constrained Delegation. If you are trying to use “Windows” for any other datasource and the authentication protocol with that data source will use your windows credentials, and that data source is on a different machine, you needed to configure Kerberos Constrained Delegation. “Access Denied” sounds like the approrpriate error if you have not configured this. This is the classic “double hop” issue. There are documents online for configuring KCD for use with Excel Services. Let me know if you have trouble finding them. I did a quick search and found this (http://www.microsoft.com/downloads/en/details.aspx?FamilyID=1a794fb5-77d0-475c-8738-ea04d3de1147&displaylang=en) although I don’t know if that is the best resource.
    HTH
    Lee

  • jennifer

    Hi,

    I am getting the below when i try to expand a pivot table. “an error occurred during an attempt to establish a connection to external datasource”

    The user account using which i try to browse is not domain account. It is a local account. Can you please tell me workaround for that. Also powerpivot is not installed in the server. Is it mandatory. Sorry i am new to excel so i am not sure. Please kindly help

  • Hi Jennifer:
    Is your machine a single, all-on-one server? Or is this a multi-machine farm?

    Whether or not you need PowerPivot installed depends on that kind of pivot table it is.
    If it is pointing to a standalone Analysis Services server, then you do not need PowerPivot installed.
    If the workbook has been created with the PowerPivot Excel add-in, and you are clicking on slicers and issuing queries, then Yes, you do need PowerPivot for SharePoint installed. If you are not clicking on a slicer and is just viewing the pivot table as it was last saved in Excel (i.e. you are not interactively going to work with the pivot table), then Excel Services is fine — whatever was last displayed in the visible cells is saved by Excel and Excel Services can render it without any additional components. However, as I said, if you are clicking on a slicer or issuing queries, then you need PowerPivot for SharePoint.

    Hope that helps.

    _-_-_ Dave

  • jennifer

    Hi PowerPivotGeek,

    Thanks for your quick reply. Yea its a stand alone server And i am required to click on slicers so i will install Power pivot as suggested by you but the user account what i am using is Local account will it create any impact.There is no domain account . Please let me know

    thanks in advance

  • bmm6o

    The KB article about the dependency is here: http://support.microsoft.com/kb/2512597

  • Hello. We have two users who are not getting the “date connection uses Windows authentication…” message. They can see the current data from the most recent refresh. But everyone else that we have tried to add to our SharePoint site is getting the error message. They can then open the workbook in SharePoint but the data has not refreshed (it shows the same data that existed when the workbook was published). We have given them the same rights as the users who are not getting the error message. We have an open support incident with Microsoft but they have not been able to help us resolve this. Any ideas? I will pay someone to help me fix this.

    Regards

  • Lee

    Hi Arthur,
    So you have a couple of users who are not getting the error and everyone else is getting the error. I have seen this before. I recommend looking at the code I posted here: http://powerpivotgeek.com/2010/05/21/testing-the-claims-to-windows-token-service-for-different-identities/. There is a link to it in this article but you might have missed it. What you are seeing, in the past, has been due to the fact that certain users have UPNs in your AD installation and some do not. This might be because of an upgrade issue or something else, but it can be fixed. Try out the test code and use the upns for the users for whom it works and for whom it doesn’t and then you can get a very isolated repro which MS support can help you debug … you will need someone to look at your AD configuration. Let me know if that helps

    Lee

  • James

    I’m getting this error for only one of my reports. I’ve set up a scheduled data refresh which runs successfully. The error occurs when I open the report from Sharepoint in the browser. The report will still appear once I’ve clicked on ok but clicking on a slicer will cause the error message to reappear.

    All other reports are functioning correctly using the same credentials etc…

    When I look at the Analysis services databases I can see:

    Broken_Report Sandbox 2105155e-688a-43c8-9a1f-b9be00721524

    Where as all the reports that are working appear as:

    Working Report Process 5b271bcb-d170-4c48-b634-f94c94d9367b

    Not sure how relevant that is but it might be.

  • Daniel

    What account is the Claims to Windows Token Service running as? …did the trick. Thanks!

  • I had the problem that for all users access to the service c2wts was denied.

    What resolved the problem in my configuration:

    One allowed caller was missing c2wtshost.exe.config (Folder: C:\Program Files\Windows Identity Foundation\v3.5).

    Adding this entry to the section allowedCallers did the job:

    add value=”WSS_WPG”

    I restarted the service: And voila: Refreshing the excel worksheet worked like a charm!

  • SP10

    I did start Claims to Windows Service Token.
    But that did not fix the issue.

    Any ideas?

  • [...] May 23, 2010 by dennyglee As you may have noted in my original posting Delegation, Claims, Active Directory…Oh My!…Aw Crap!, it quickly described how to solve issues surrounding the delegation of the claims token within an Active Directory environment.  In it I referenced Lee Graber’s excellent posting: The data connection uses Windows Authentication and user credentials could not be delegated. [...]

  • [...]  As you may have noted in my original posting Delegation, Claims, Active Directory…Oh My!…Aw Crap!, it quickly described how to solve issues surrounding the delegation of the claims token within an Active Directory environment.  In it I referenced Lee Graber’s excellent posting: The data connection uses Windows Authentication and user credentials could not be delegated. [...]

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>