Compartilhar via

Setting up SharePoint 2010 Excel Services to get external data

  • Artigo

Some of you out there might just want to run as far as possible when having to configure SharePoint 2007 Excel Services to refresh data from an external SQL or OLAP source using the logged on users’ credentials. Smile

The process involved configuring SPNs for quite some services, taking care of delegation, configuring ECS to use Kerberos and then usually a lot of hair pulling and tracing using netmon.

The reason for using Kerberos is quite simple: you need Kerberos to work around double or multi-hop authentication scenarios. For example in 2007, the user authenticated to the web frontend (1 hop), the web frontend contacted ECS running under the SSP (2 hops) which in turn went back to fetch data from the SQL Server or Analysis Services (3 hops).

Good news is that in SharePoint 2010 things got quite simple thanks to claims based authentication. As you might know SharePoint 2010 does claims based auth among its components, so the whole 2-3-many hops problem is now eliminated.

SharePoint 2010 solves this by having the user authenticate to the web frontend (either using Kerberos or NTLM – note: SAML won’t work here!) and then continuing to the Excel Services web service app using claims based auth. Thanks to claims, doing multiple hops is not a problem. The interesting part in continuing with the request is when ECS wants to fetch the necessary data from the external data source. As SQL and Analysis Services cannot be accessed using claims based auth (this changes in 2008R2 – Reporting Services will support it in SharePoint integrated mode) some magic is done and the claims token is converted back to a Windows (Kerberos) token. This magic is done by the service called Claims to Windows token service (c2wts) which is installed by default in SharePoint 2010.

In order for c2wts to work, you need to make sure, that the user running this service is trusted to transform claims to windows tokens by verifying the following in c2wtshost.exe.config (usually in C:\Program Files\Windows Identity Foundation\v3.5):

 <?xml version="1.0"?>
<configuration>
  <windowsTokenService>
    <allowedCallers>
      <clear/>
      <add value="WSS_WPG" />
    </allowedCallers>
  </windowsTokenService>
</configuration>

Usually when setting the C2WTS to a custom domain account, it is added to the WSS_WPG group, so probably you won’t need to modify the config.

To successfully authenticate to the external datasource, first it needs to be configured to support Kerberos auth (for SQL: Using Kerberos Authentication with SQL Server, for Analysis Services: How to configure SQL Server 2008 Analysis Services and SQL Server 2005 Analysis Services to use Kerberos authentication).

Next step is to allow the service account running the c2wts service delegation to the external data sources. This can be done on the delegation tab of the user account properties in Active Directory Users and Computers (ADUC).

Lastly configure delegation from the account running Excel Services to the external data sources.

Now you only need to make sure that the Excel Services calculation method is set to “Impersonate” and the document library in which your xlsx file resides is included in the trusted locations of ECS.

If everything is right, data refresh should succeed. If not, refer to the ULS log for errors.

 

UPDATE (05/14) : Fiddling with the c2wts service I discovered that it only works if the Windows service is set to LocalSystem identity. If you give it a custom identity it fails with:

 SPSecurityContext.WindowsIdentity: Could not retrieve a valid windows identity for NTName='test\user', UPN='user@test.local'. UPN is required when Kerberos constrained delegation is used. 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.IdentityModel.WindowsTokenService.S4UClient.CallService(Func`2 contractOperation)    
at Microsoft.SharePoint.SPSecurityContext.GetWindowsIdentity().

Stay tuned for an update on this. Running C2WTS under LocalSystem will trip a health validator rule in SharePoint and you’ll get a warning.

There’s a quite good article over at technet on this topic: Configure Kerberos authentication for the claims to Windows token service (SharePoint Server 2010). Worth a read.

UPDATE (06/17): Lee Graber has a nice writeup on the topic Claims to Windows token topic: Testing the Claims To Windows Token Service for different identities.

On the same note, the required user rights for the account running the C2WTS are “Act as a part of the operating system” and “Logon as a service”.

UPDATE (09/01): The SharePoint 2010 Kerberos Guide has a good summary of all necessary settings. To get c2wts working under a domain account instead of LocalSystem, give the account the following permissions on the box running the service. Don’t forget to restart the service after granting the rights!

  • Act as part of the operating system
  • Impersonate a client after authentication
  • Log on as a service

To revert back to LocalSystem use the following PowerShell commands:

 $claims = Get-SPServiceInstance | where {$_.TypeName -eq "Claims to Windows Token Service"} 
$claims.Service.ProcessIdentity.CurrentIdentityType = 0 
$claims.Service.ProcessIdentity.Update()

UPDATE (09/26/11): Reviewed some parts of the article and corrected a few things. Thanks Mads for the hints! Smile

  • delegation will only work if you have Claims – Windows auth (either NTLM or Kerberos) but not ADFS (SAML)
  • usually there’s no need to fiddle with the c2wts config.

Comments

  • Anonymous
    May 26, 2010
    Hi Andras,great post, and I'm keen to see where you get to with setting a custom (domain) identity for the c2wts service, as I've been working through my health reports on my Least Privelidge based install and this is indeed one of the issues I hit (first I hit the issue of it not starting up properly due to missing the service dependancy on the crypto service...).Late'ishCraig
  • Anonymous
    May 26, 2010
    Hi Craig,I have yet to find the time to debug it but my gut feeling tells me that there's some right missing (most probably SeImpersonate) for the user account.Will update the post if I found out something.Cheers,Andras
  • Anonymous
    July 23, 2010
    Hi Andras,You have mentioned that the account running the C2WTS service needs to be  allowed for delegation: " allow the service account running the c2wts service delegation to the external data sources" and then you say that the service only works with the LocalSystem only. In that case how would you do that since LocalSystem is not a domain account??Also you mention that Excel Services Calculation method needs to be set to Impersonate. Where do you check that?Thank you,Ali
  • Anonymous
    July 24, 2010
    Hi Ali,In case you are running c2wts under local system it will be the computer account that is delegating. You can also run c2wts under a domain account if you grant it the proper rights.Setting the ECS impersonation mode is done under the ECS service application settings.Cheers,Andras
  • Anonymous
    July 27, 2010
    I am still not able to refresh. I am running c2wts as Local System, SQL server is using Kerberos and ECS is using impersonation mode and the file is trusted. I get this error:The data connection uses Windows Authentication and user credintials could not be delegated.
  • Anonymous
    July 27, 2010
    The comment has been removed
  • Anonymous
    July 27, 2010
    Thanks for the quick response Andras.I checked the ULS log an nothing really caught my eyes. How do I check the delegation settings? And your final note about logging into the site using Kerberos, I was under the impression that if the IE settings are set to use windows authentication, it uses Kerberos if supported.
  • Anonymous
    July 27, 2010
    The comment has been removed
  • Anonymous
    March 14, 2011
    Hi,i want help on this... i want to create share point means logic should be if i update that particular file on my local system it should be updated in sharepoint location
  • Anonymous
    August 29, 2012
    Andras,Can you explain to me the reasoning behind delegation not being supported for ADFS (SAML)? I found this very misleading as the official Microsoft documentation does not seem to make mention of this. I don't understand why if the SAML token provides all of the claims necessary for C2WTS to get a windows account that SharePoint wont allow you to use it to establish Windows delegation. I'm using ADFS because SharePoint does not support client certificate authentication. Is it some kind of marketing decision to get you to buy UAG or TMG if you want to use client certificate authentication and still use delegation?
  • Anonymous
    August 29, 2012
    Hi Brett,To my best knowledge this is a limitation in Excel Services and PerformancePoint services. I can also only speculate on the reasons. In a custom application you can dehydrate the Kerberos user identity using C2WTS and use the identity to access extra-farm data.
  • Anonymous
    October 07, 2012
    Hi Andras,Can a user just use NTLM to access all services--  SSRS 2012 integrated mode, SSAS 2008R2, PowerPivot, PowerView, Excel Services, PPS, Visio Services?  When is Kerberos 100% necessary in SP 2010? Does C2WTS require it?
  • Anonymous
    January 02, 2013
    It is possible to use NTLM instead of Kerberos - it is documented here :technet.microsoft.com/.../gg266385(v=office.14).aspx