Excel Services delegation

I am inspired by a recent post from a colleague about the various issues that can come up with Excel Services delegation (see a Denny Lee’s blog here: http://dennyglee.com/2009/11/18/troubleshooting-powerpivot-excel-services-connectivity/) and I wanted to take it a bit further (and maybe a bit ‘geekie’-er)

First, why is this a problem? After all, as you can see in Denny’s post, you can see the workbook and you even have a thumbnail for it in the Gallery. What’s up here? The core of the problem is that unless you’ve set the connection to refresh when you first open the workbook, Excel Services uses its pivot cache to construct the pivot table and slicers. It is only if you manually refresh the connection, or click on a slicer, that you make an actual connection to the embedded data. Until then you are just looking at cached information. Until you click on a slicer, you don’t really know if Excel Services is working – so a strong recommendation that I make to any person doing a validating their installation is to “ALWAYS CLICK ON A SLICER” if you want to make sure that your installation is working properly.

Ok, so now we’ve hit the problem. And you get the dredged “An error occurred during an attempt to establish a connection to the external data source.

The issue is (get your geek-armor ready) is that when accessing data PowerPivot looks like just another data source to both Excel desktop and Excel Services. Prior to accessing the data, if using Windows authentication, Excel Services needs to impersonate the user on the calling thread. But, in a claims-aware world, the only ‘identity’ that Excel Services has is the claims token. When the user connects (in whatever authentication method the SharePoint Web Application allows), the first thing that SharePoint does on the web front end is to translate the authentication method’s user identity to a claims token. And it is that claims token that is passed around within the farm. Remember this dialog box within Excel desktop:

The setting tells Excel Services what kind of lookup to perform when a new connection is started:

• ‘None’ means to use the Unattended Execution Account specified for the Excel Services service application being used by this connection. The username and password are retrieved from Secure Store. Using these credentials, Excel Services does a Windows logon and then it calls the data provider (the msolap OLEDB provider in the case of PowerPivot).
• ‘SSS’ means that Excel Services should access its Secure Store service application. The username and password are retrieved from Secure Store using this Application ID. Using these credentials, Excel Services does a Windows logon and then it calls the data provider Using and Excel Services does a Windows logon and then calls the data provider.
• ‘Windows Authentication’ (which is both the default and our case today) means that Excel Services should use the interactive user’s Windows identity. The original Windows token for the user is looked up, Excel Services impersonates that user on the calling thread and then calls the data provider.

In the case of Windows Authentication, to perform the lookup, Excel Services uses the “Geneva to Windows Token Service” (GTS) provided by SharePoint. GTS takes the claims token of the caller and translates it to the Windows identity of the caller (the underlying Windows API that is uses for this is S4U (see here: xx). Unfortunately S4U does have its restrictions, and those restrictions are the heart of the “Cannot Delegate” error message that we are seeing. GTS requires:

1. A domain controller must be available to validate the logon. GTS cannot use cached credentials. It has to validate the login token on every connection. This obviously has performance implications, but fortunately it isn’t on very query; but just when the connection is established. This is easy to see (and it was the way that I generated the error message box above) –> just unplug the network from your laptop. You see that you can use SharePoint and Excel Services for everything using cached credentials until you go to Excel Services and try to connect to any data source (PowerPivot included) using Windows authentication.
2. The server must be a member of the same domain as the caller; or there must be a two-way trust relationship between the domains. This means that a common Windows 2000 domain architecture cannot be used by GTS.
3. The caller cannot be a local machine account. GTS only understands how to talk to domain controllers.
4. 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.

So – this is happening to you – you cannot delegate credentials. What are your options? First, you could fix the problem so the restriction no longer holds, e.g. you could establish a two-way trust between the domains rather than a one-way trust, but this is likely not a doable approach because you probably have business justifications for why the configuration was done this way. A second alternative is that you could switch to “None” as the authentication, this is how the “off-the-network” blog entry that I wrote, see http://powerpivotgeek.com/2009/11/06/taking-your-server-off-the-network/. This is straightforward and easy to implement, but it lacks the strong security enforcement of Windows authentication and it is a more general account. So there will likely be business issues with that approach also.