Excel data services authentication issue (TfsOlapReport ) in dashboard

Question

Hi,

A recent installation of TFS2010 with SharePoint 2010, etc. has developed a problem in displaying the Excel report graphs on the project dashboard web portal page.

The error I get is:

"The data connection uses Windows Authentication and user credentials could not be delegated. The following connections failed to refresh: TfsOlapReport"

Originally this worked fine, but I've not been able to identify what changed to break it.

Can anyone help diagnose what's going on here?

A partial dump of the log file follows (with server name, domain and username replaced with XXXX,DDD,UUU).

Things I've looked at:

• The "Claims to Windows Token Service" is running (system service and also in SharePoint central admin)
• The claims to windows token service is running under the Local Service account.
• The SecurityTokenServiceApplicationPool is also running under Local Service account (I had previously changed this to a managed account due to a warning in the best practices analyzer, but then changed it back in case that was responsible for the problem).
• The server machine is a domain member and I'm accessing it as a domain user. My user id has full rights to SharePoint & TFS.
• It seems that loading Excel reports into Excel itself works ok, so the problem appears isolated to the dashboard.

Here's the log:

0x0BA8    SharePoint Foundation             Logging Correlation Data          xmnv    Medium      Name=Request (GET:http://XXXX:80/sites/Demo/DriveCom/_vti_bin/DynamicGridContent.json/GetChartContent?context=%7B%22SessionId%22%3A%2236.eb5892b8-fa5f-496c-b663-42d30aba0c61162.1.V21.6PjscIBR4d1WpGwOD3uF%2F90.5.en-US5.en-US73.%2B0000%230000-10-00-05T02%3A00%3A00%3A0000%23%2B0000%230000-03-00-05T01%3A00%3A00%3A0000%23-006036.002273d4-fb8e-4ee9-9b70-aa3c019943e31.N%22%2C%22TransientEditSessionToken%22%3Anull%2C%22PermissionFlagsHash%22%3A%22L3iWVF9BQaZeiWDnL4QqjCxhIaSjLpoJ1Ub4XeGnrY%2BdWaOTfrcY7TOwr%2FaIrGDGZov0hljzblPNNmgSUNJRc3Ag38AG24CkySjIWqiYZWY%2Bz3ijs3TbZL0S10QoQLV1vntZnolk0hSiTTePmdt1dGs9sffKiZwUJFGlcAVXkdiaS%2BQJydU%2B4Fsl%2BcabKNvw%22%2C%22CompleteResponseTimeout%22%3A0%2C%22CollaborationParameter%22%3A%7B%22CollaborationState%22%3A%7B%7D%7D%7D&ewaControlId=%22ctl00_m_g_3ee    9385721a-9d8d-4fef-8d89-76838d74ee4d
0x0BA8    SharePoint Foundation             Monitoring                        b4ly    Medium      Leaving Monitored Scope (Request (GET:http://XXXX:80/sites/Demo/DriveCom/_vti_bin/DynamicGridContent.json/GetChartContent?context=%7B%22SessionId%22%3A%2236.eb5892b8-fa5f-496c-b663-42d30aba0c61162.1.V21.6PjscIBR4d1WpGwOD3uF%2F90.5.en-US5.en-US73.%2B0000%230000-10-00-05T02%3A00%3A00%3A0000%23%2B0000%230000-03-00-05T01%3A00%3A00%3A0000%23-006036.002273d4-fb8e-4ee9-9b70-aa3c019943e31.N%22%2C%22TransientEditSessionToken%22%3Anull%2C%22PermissionFlagsHash%22%3A%22L3iWVF9BQaZeiWDnL4QqjCxhIaSjLpoJ1Ub4XeGnrY%2BdWaOTfrcY7TOwr%2FaIrGDGZov0hljzblPNNmgSUNJRc3Ag38AG24CkySjIWqiYZWY%2Bz3ijs3TbZL0S10QoQLV1vntZnolk0hSiTTePmdt1dGs9sffKiZwUJFGlcAVXkdiaS%2BQJydU%2B4Fsl%2BcabKNvw%22%2C%22CompleteResponseTimeout%22%3A0%2C%22CollaborationParameter%22%3A%7B%22CollaborationState%22%3A%7B%7D%7D%7D&ewaContro...    9385721a-9d8d-4fef-8d89-76838d74ee4d
0x0BA8    SharePoint Foundation             Monitoring                        b4ly    Medium      ...lId=%22ctl00_m_g_3eec439a_dd51_4419_9b32_994e540b6996_ctl01_m_ewa%22&currentObject=%22Chart%201%22&isNamedItem=true&revision=-1)). Execution Time=1.36011111286042    9385721a-9d8d-4fef-8d89-76838d74ee4d
0x235C    SharePoint Foundation             Topology                          e5mb    Medium      WcfReceiveRequest: LocalAddress: 'https://XXXX:32844/SecurityTokenServiceApplication/windowstokencache.svc' Channel: 'System.ServiceModel.Channels.ServiceChannel' Action: 'http://tempuri.org/ISPWindowsTokenCacheServiceContract/GetUserHandle' MessageId: 'urn:uuid:3d5c5306-646a-48ba-afdb-55bfa6efd766'    89b167dd-b171-4c38-91d3-fc9254465daa
0x235C    SharePoint Foundation             Monitoring                        nasq    Medium      Entering monitored scope (ExecuteWcfServerOperation)    89b167dd-b171-4c38-91d3-fc9254465daa
0x235C    SharePoint Foundation             Claims Authentication             d52v    High        SPWindowsTokenCacheServiceApplication.GetUserHandle() could not find token for user '0#.w|DDD\UUU'.    89b167dd-b171-4c38-91d3-fc9254465daa
0x235C    SharePoint Foundation             Monitoring                        b4ly    Medium      Leaving Monitored Scope (ExecuteWcfServerOperation). Execution Time=0.419322793688194    89b167dd-b171-4c38-91d3-fc9254465daa
0x18EC    SharePoint Foundation             Claims Authentication             fvx8    Medium      SPSecurityContext.WindowsIdentity: Could not retrieve a valid windows identity for NTName='DDD\UUU' from the windows token cache service. Exception: System.ArgumentException: Token cannot be zero.     at System.Security.Principal.WindowsIdentity.CreateFromToken(IntPtr userToken)     at System.Security.Principal.WindowsIdentity..ctor(IntPtr userToken, String authType, Int32 isAuthenticated)     at System.Security.Principal.WindowsIdentity..ctor(IntPtr userToken)     at Microsoft.SharePoint.SPSecurityContext.GetWindowsIdentity().    89b167dd-b171-4c38-91d3-fc9254465daa
0x18EC    Excel Services Application        Excel Calculation Services        d51k    Medium      MossHost.TryGetWindowsIdentity: Failed to get WindowsIdentity from IClaimsIdentity. SPSecurityContext.GetWindowsIdentity() threw exception: System.InvalidOperationException: Could not retrieve a valid Windows identity. ---> System.ArgumentException: Token cannot be zero.     at System.Security.Principal.WindowsIdentity.CreateFromToken(IntPtr userToken)     at System.Security.Principal.WindowsIdentity..ctor(IntPtr userToken, String authType, Int32 isAuthenticated)     at System.Security.Principal.WindowsIdentity..ctor(IntPtr userToken)     at Microsoft.SharePoint.SPSecurityContext.GetWindowsIdentity()     --- End of inner exception stack trace ---     at Microsoft.SharePoint.SPSecurityContext.GetWindowsIdentity()     at Microsoft.Office.Excel.Server.MossHost.MossHost.<>c__DisplayClass8.<Try...    89b167dd-b171-4c38-91d3-fc9254465daa
0x18EC    Excel Services Application        Excel Calculation Services        d51k    Medium      ...GetWindowsIdentity>b__6()    89b167dd-b171-4c38-91d3-fc9254465daa
0x18EC    Excel Services Application        Excel Calculation Services        c9la    Medium      CredentialsProvider.GetCredentials: Failed to get WindowsIdentity.    89b167dd-b171-4c38-91d3-fc9254465daa
0x18EC    Excel Services Application        External Data                     5252    Warning     Credential delegation failed because Excel Services Application was unable to obtain a Windows Identity. [Session: 1.V22.70k+HHYrrgNDv77b3qYqxa90.5.en-US5.en-US73.+0000#0000-10-00-05T02:00:00:0000#+0000#0000-03-00-05T01:00:00:0000#-006036.002273d4-fb8e-4ee9-9b70-aa3c019943e31.N User: 0#.w|DDD\UUU]    89b167dd-b171-4c38-91d3-fc9254465daa
0x18EC    SharePoint Server                 Logging Correlation Data          xmnv    Medium      Document=http://XXXX/sites/Demo/DriveCom/Reports/Burndown.xlsx    89b167dd-b171-4c38-91d3-fc9254465daa
0x18EC    SharePoint Server                 Logging Correlation Data          xmnv    Medium      Result=Success    89b167dd-b171-4c38-91d3-fc9254465daa
0x18EC    Excel Services Application        Excel Calculation Services        eci4    Medium      ExcelService.PostProcessRequest: finished request of type OpenWorkbook    89b167dd-b171-4c38-91d3-fc9254465daa

Answer 1

Can anyone help with this?

I have tried everything I can think of and I simply cannot get this Claims Authentication stuff to work.

Things I've checked/tried:

1. The C2WTS service is running fine. Running a test app which does a token and delegation check on this also works.
2. For the "SecurityTokenServiceApplicationPool", I've tried setting it to run as either Local Service, or the managed farm account. Makes no difference.
3. I've checked the 'c2wtshost.exe.config' file - it allows the WSS_WPG as an allowedCaller. That security group has the Sharepoint farm account in it as well as built in accounts like LOCAL SERVICE.
4. I've restarted, looked further into ULS logs, read more about how this should all work and my config looks sensible, but so far have come up blank...

What's going wrong?!

Answer 2

Base on the error, it looks like there are issues with C2WTS service.

 

Credential delegation failed because Excel Services Application was unable to obtain a Windows Identity. [Session: 1.V22.70k+HHYrrgNDv77b3qYqxa90.5.en-US5.en-US73.+0000#0000-10-00-05T02:00:00:0000#+0000#0000-03-00-05T01:00:00:0000#-006036.002273d4-fb8e-4ee9-9b70-aa3c019943e31.N User: 0#.w|DDD\UUU]    89b167dd-b171-4c38-91d3-fc9254465daa

 

1. Double check to make sure C2WTS is started.

2. Review the following for any hints(even though error is different but it gives some good hint of troubleshooting C2WTS):

http://powerpivotgeek.com/2010/02/08/the-data-connection-uses-windows-authentication-and-user-credentials-could-not-be-delegated/

Answer 3

For the record, I eventually got a workaround for this problem I had with the aid of MS support.

Summary is to enable and configure the Secure Store service. I did this by:

1. Open up SharePoint Central Admin
2. App management -> Manage Service Applications
3. Select Secure Store Service (add it if not present)
4. Generate a new key - give it a pass phrase
5. Click 'New' in Manage Target Applications ribbon group. Enter in details and go next.
   App Id: tfs
   Display Name: tfs
   Target type: Group
   Url: none
6. Click next to accept suggested fields.
7. Set admin to yourself and members to an AD group of TFS users.
8. Select the newly created 'tfs' entry and click Set Credentials.
9. Enter in the TFSReports account details
10. Launch TFS administration console
11. Select Extensions for SharePoint products.
12. For enterprise app enter in 'tfs' (App id in step 5).

I say this is a workaround as when I first configured the server I didn't need to do any of this and the Excel services graphs worked fine.

For info, for the SharePoint web app the IIS Authentication is set to Negotiate (Kerberos). We did try extending the web app and used NTLM authentication in the new one, but that had no effect.