Sooner or later in the life of a SharePoint admin there will be a time when the dreaded Secure Store Service will have to be configured. Usually this time comes when Excel Services has to be configured to map certain users to a predefined account to enable data refresh.
When using Excel Services data refresh can happen in the following ways:
Let’s look at each of these methods in detail:
User account delegation
When using delegation, Excel Services will attempt to use the logged on user’s identity to authenticate to the external data source.
In SharePoint 2007 delegation worked by using Kerberos Constrained Delegation (CD). A big drawback of this solution was that it had to be configured end-to-end, so the Web Application had to support it, Excel Services had to be configured, the external data source had to be configured and also quite some work was needed with (fake) SPNs and delegation rules.
Fortunately this has changed quite a lot in SharePoint 2010. With the use of claims based authentication, delegation got a lot simpler. As long as you are using Claims – Windows Authentication on the Web Application and have the Claims to Windows Token Service (C2WTS) configured no double-hop scenario is produced and authentication delegation can succeed.
When using a predefined account, Excel Services will use the account you have configured to access the external data source. This account can be any account you specify. You will need to take care of updating the password of this account when it changes.
It’s pretty self-explanatory, Excel Services will not attempt to authenticate against the external data source.
Influencing which of these methods Excel Services will be using can be a little tricky. Excel Services will use what it is instructed to use in the connection definition. To complicate things even further this connection definition could be in a separate file (eg: .odc file in a data connection repository) or embedded in the Excel file. If the Excel file contains a reference to a connection definition file it will be used.
The most important setting in the selection process is set on the following panel:
The settings are:
So what do these mean?
Excel Services will try to delegate the logged on users’ identity to the external data source. This will only succeed if the following are true:
In SharePoint 2007 using this setting was only possible after Kerberos Constrained Delegation was fully configured for the whole path (user – Web Application – Excel Services – external data source).
When choosing this authentication method Excel Services will use the Secure Store Service to find a Target Application ID matching what you enter here. Credentials you have set for this application will be used to access the external data source. As this practically breaks the hops into one-hop journeys, there’s no need for Kerberos configuration.
Usually this choice causes the most headaches when not configured properly. In this case the authentication scheme specified in the connection string will be passed to the database provider. In case Windows Authentication is specified in the connection string, the Unattended Account will be used. If a specific username and password is set, those will be used.
In case the Unattended Account is selected, Excel Services will try to go to the Secure Store Service and fetch a credential identified by the Unattended Account Application ID in the Excel Services Settings page:
So now we understand where Excel Services is looking for the credential to use. What happens when Excel Services tries to fetch a credential from the Secure Store Service? To understand what’s happening, let’s separate the case when SSS is set in the connection and when None is set and the Unattended Account is used.
As you can see it is pretty important to set the Credential Owner for the target application. Unfortunately I haven’t found any good way to change the Credential Owner after creating the target application. I have also not found a way to find out what account you have set to be used to access the external data source. This makes configuring the target application a bit tricky…
More info on the topic:
Plan Excel Services authentication (SharePoint Server 2010)