As stated in its description, this mode is supposed to use logon user's credential to connect to external data source. However this happens only if kerberos and delegation are configured correctly for both SharePoint and Excel Services, if not, excel services connect to external data source anonymously, which nevertheless doesn;t necessarily mean the failure of Excel Services data accessing. Why? since it largely depends on the connection definition in your connection file:
- if sql authentication is used and sql account/password are available in the connection string, excel service can still fetch external data.That explains why excel services can work OOB without any kerberos configuration even if its authentication is set as "window authentication".
- if window authention is used in the connection string, and external data is not allowed anonymous access, you will get access deny error
- Alternatively SSO can be used such as ProjectServer sample connection all use SSS ID called "ProjectServerApplication"
- the "None" option requries unattended service account setup. by default, Excel service application doesn't create unattended account for itself.
To use logon user's credential to access external data source by using Excel Service, there are 3 subsystems involved: sharepoint, Excel Service, and external Data source.
Normally for scenario like that, all of 3 subsystems are required to be kerberos enabled (registering SPNs) and both sharepoint and Excel Service should have delegation rights. However, SharePoinit 2010 Excel Service is claim aware, i.e, it is claim that will be used for communication, thus no kerberos or SPN for Excel service is needed, nor SharePoint WFE service account (application pool id) needs the delegate right to Excel Services.
It is C2WTS that will take initial client kerberos token to external data sources. In order for that to happen, the following 2 accounts need to have delegation right to external datasoure:
- service account running Excel Services Application.
Find the application pool id from IIS:
- service account running C2WTS (which is a window service), or, computer account where C2WTS is running if "local system" instead of a domain account is used to run C2WTS
OOB SharePoint 2010 use "local system" to run C2WTS, but it is considered as the best practices to replace with a domain account. That will need some extra configurations:
- register a trivial SPN for this domain service account (to trigger "delegation" option)
- add to local admin group (need to reboot server)
- In local security policy (secpol.msc) under user rights assignment give the service account the following permissions:
- Act as part of the operating system
- Impersonate a client after authentication
- Log on as a service
add this domain account into c2wtshost.exe.config as "allowedCallers"- add this domain account as sharepoint managed account and change c2tws service account from sharepoint central administration site
- re-start the C2WTS service. for details see this white paper
