Setting up SharePoint 2010 Excel Services to get external data

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.