SharePoint adventures

A blog about random SharePoint stuff

Setting up SharePoint 2010 Excel Services to get external data

Setting up SharePoint 2010 Excel Services to get external data

Rate This
  • Comments 13

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.
  • 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'ish

    Craig

  • 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

  • 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

  • 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

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

  • Ali, I'd check the ULS log. You should also check delegation settings on the computer account of the box running C2WTS. Also make sure there was a kerberos identity before C2WTS (meaniing you have logged on to the SharePoint site via Kerberos).

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

  • Delegation settings can be set in active directory users and computers on a dc. You should have delegation enabled on the computer account belonging box running the c2wts. The delegation should be any protocol and to the SQL server (MSSQLSvc/... and MSOLAPSvc.3/...).

    You could use DelegConfig v2 to verify your settings.

    Getting Kerberos on the http logon side requires the following:

    1) SPN pointing to the account hosting the application pool that is serving your request (http/portaladdress)

    2) Kerberos (Windows Auth - Negotiate) enabled on the webapp in sharepoint.

    3) Check kernel mode authentication in IIS (should be off)

    If correctly setup, you should see first a 401.2 response in the IIS logs, followed by a 200 response. If you see a 401.1 inbetween, you are on NTLM.

  • 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

  • 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?

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

  • 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?

  • It is possible to use NTLM instead of Kerberos - it is documented here :

    technet.microsoft.com/.../gg266385(v=office.14).aspx

Page 1 of 1 (13 items)
Leave a Comment
  • Please add 2 and 4 and type the answer here:
  • Post