some notes on BCS, ECT authentication mode and SSO

SharePoint Business Connectivity Service, just like Excel service, is to get external data source into sharepoint, but its logic authentication is quite different from Excel Services, and it doesn't use C2WTS:

External Content Types in SPD have 4 types of authentications for external data source :

• User's Identity
• BDC Identity
• Impersonate Window Identity
• Impersonate Custom Identity

User's Identity is to use logon window user's identity to connect to external data, which requires Kerberos implemented for both sharepoint and external data source (no configuration needed for BCS application)

BDC Identity is to use Sharepoint application pool id to connect to external data ( it was called revertToSelf). no security enforcement.

Both Impersonate Window Identity and Impersonate Custom Identity are to use SSO:

The difference between those two is, the first one requires SSO application using window account and the second one use sql account as shown below:

To use ECT object, users need an execute permission which can only be set from central admin:

SharePoint 2010 Excel Services and External Data Refresh

Excel Services Configurations

Two most important configurations in Excel Services Application are:

• Trusted File Locations: the whole sharepoint farm is pre-configured as trusted location.
• Trusted Data Connection Libraries: it is still required in sharepoint 2010 (folder name can not be skipped).

Workbook Data Connection Properties:

Data Connection properties include usage and definition. The usage is per workbook, its setting is not saved in the configuration file. The connection can either use sql authentication or window authentication. Another import property for each connection definition is "Excel Services Authentication", which will be addressed in another post.

Ways to Refresh Data:

manual refresh (data connection)       refresh on open or periodic:

Some Refresh Problems:

• Table not refreshable, only pivot table and pivot chart can be refreshed from external data.

• Browser (IE) can cache old connection string. Close IE before testing new connection. 
• 
  
  periodic refresh won;t happen after excel service session timeout:

Excel service create a session (memory/disk) for each workbook and release them after timeout. Periodic refresh can't happen without a  ongoing session.

References:

http://blogs.msdn.com/b/andrasg/archive/2010/05/04/setting-up-sharepoint-2010-excel-services-to-get-external-data.aspx

SharePoint 2010 Excel Services Authentication Mode

One of properties in any workbook's connection is called "Excel Services Authentication". Its default choice is "Windows Authentication"

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:
  

      Update: actually it is easier to find it from Central Admin.

• 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

when configuring delegation, make sure use constrained delegation and choose "Use any authentication protocol" as shown below:

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: 

1.  Act as part of the operating system
2. Impersonate a client after authentication
3. 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