The mystery around Excel Services and the Secure store
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:
- Using user account delegation
- Using a predefined account
- Using anonymous connection
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.
Predefined account
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.
Anonymous
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:
- Windows Authentication
- SSS
- None
So what do these mean?
Windows Authentication
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:
- The Web Application you are using is set to Claims – Windows Authentication (NTLM or Negotiate)
- C2WTS is configured and running
- Kerberos authentication and constrained delegation are configured from the C2WTS identity to the external data source.
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).
SSS (SSO)
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.
None
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.
SSS – in this case Excel Services looks up (in the context of the logged on user) the target application in the Secure Store Service and verifies if the current logged on user is amongst the credential owners of the target application. If not, you will get an Access Denied exception similar to the following in the ULS log:
The Microsoft Secure Store Service application Secure Store Service Application failed to retrieve credentials. The error returned was “Access is denied”.
None – in this case Excel Services will be using the Unattended Account specified in Excel Services settings. The target application will be looked up, but in this case the context of Excel Services will be used. So when specifying the Credential Owner of the target application, add the service account of Excel Services as well.
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: