Various authentication methods for Excel Services in SharePoint Server 2013

Excel Services can connect to various external data sources, including SQL Server, Analysis Services, and custom OLE DB / ODBC data providers.
To connect to the data source, Excel Services uses a specific data provider for each data source.
As a security measure, Excel Services must explicitly trust data providers before they can be used.
Trusted data providers can be configured as part of the Excel Services service application settings in the SharePoint Central Administration website.

Technet Reference

Data sources and authentication methods for Excel Services

Data source Authentication method
Analysis Services
  • Windows authentication (integrated security)
    • using Constrained Kerberos Delegation
    • using Secure Store
    • using the Unattended Service Account
  • using the EffectiveUserName connection string property
SQL Server One of:

  • Windows authentication (integrated security)
    • using Constrained Kerberos Delegation
    • using Secure Store
    • using the Unattended Service Account
  • SQL Server Authentication
Custom data providers Varies per data source, typically a user-name and password pair stored in the connection string.

The authentication method to choose depends on various factors as outlined in the following table. Choose the one that best suits your scenario.

Authentication method Kerberos delegation Secure Store Unattended Service Account Effective User Name
Description Using constrained Kerberos delegation, the workbook viewer’s Windows credentials are sent to the data source directly. Using the Secure Store Service, the viewer’s Windows credentials are mapped to another set of credentials specified in a Secure Store target application. Using the Secure Store Service, all viewers are mapped to a unique set of credentials called the Unattended Service Account that is stored in a specific Secure Store target application specified in Excel Services Global Settings. Using the EffectiveUserName Global Setting, the user’s domain user name is passed to Analysis Services data sources.
Data connection credentials The Windows credentials of the workbook viewer. The credentials specified in the Secure Store target application. The credentials of the Unattended Service Account. The credentials of the Excel Services process identity.
Advantages
  • The Kerberos protocol is an industry standard in credentials management.
  • Kerberos ties into the existing Active Directory infrastructure.
  • Kerberos delegation permits auditing of individual accesses to a data source.
  • Given that the workbook viewer’s identity is known, workbook creators can embed personalized database queries into workbooks.
  • The Secure Store Service is part of SharePoint Server and is easier to configure than Kerberos.
  • Mappings are flexible: a user can be mapped either 1-to-1 or many-to-1.
  • Non-Windows credentials can be used to connect to data sources that do not accept Windows credentials. (Requires the Unattended Service Account to be configured also.)
  • Mappings created for Excel Services can be re-used by other business intelligence applications such as Visio Services.
  • The Unattended Service Account is easy to deploy and setup.
  • The Unattended Service Account does not require much administrative overhead.
  • Per-user data security without the need to configure Kerberos delegation.
  • Minimal configuration and administrative overhead.
Drawbacks
  • Additional administrative effort required to configure SharePoint Server and Excel Services.
  • Establishing and managing mapping tables requires some administrative overhead.
  • Secure Store permits limited auditing. In the many-to-1 scenario, individual incoming users are mapped into the same credentials through a target application, effectively blending them into one user.
  • Given that everyone is mapped to the same credentials, an administrator cannot distinguish who accessed a data source.
  • Only works with Analysis Services data sources.
For the authentication operation to succeed …
  • Kerberos delegation must be set up on the SharePoint Server.
  • The Secure Store Service must be provisioned and configured on the farm. It must also contain appropriate mapping information for a particular incoming user. Additionally the mapping information may need to be updated periodically to reflect password changes on the mapped account.
  • The Secure Store Service must be provisioned and configured on the farm. It must also contain the credentials for the Unattended Service Account. Additionally the mapping information may need to be updated periodically to reflect password changes on the mapped account.
  • Excel Services Global Settings must be configured to use the Unattended Service Account.
  • The EffectiveUserName option must be enabled in Excel Services Global Settings.
  • The user must be a member of the appropriate Analysis Services role.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s