Excel Services 2010 & 2013 – Kerberos Configuration


Configuring Kerberos for Excel Services is a two step process; Information Gathering and Configuration.

Step 1: Information Gathering:

1. Account running "Excel Services Application Web Service Application": In this example: ECSSvc.

Central Administration > Security > Configure service accounts

2.  Account running "Claims to Windows Token Service":  In this example: Local System.

Central Administration > Security > Configure service accounts

3.  Account running the data source you are connecting to:

SQL:

Log on to the server running SQL > Start > Run > Services.msc and locate "SQL Server (MSSQLSERVER)", you will see the account in the "Log On As" column. In this example: SQLSvc.

Analysis Services:

Log on to the server running SSAS > Start > Run > Services.msc and locate "SQL Server Analysis Services (<Instance>)", you will see the account in the "Log On As".  In this example: SSASSvc.

Since the "Claims to Windows Token Service" is running as Local System, we need to record the names of the SharePoint Servers running "Excel Calculation Services".  In this example: PRIME13.

Recap:

  1. Account running "Excel Services Application Web Service Application": ECSSvc
  2. Account running "Claims to Windows Token Service": Local System
  3. Account running "SQL Server (MSSQLSERVER)": SQLSvc
  4. Account running "SQL Server Analysis Services (MSSQLSERVER)": SSASSvc
  5. Name of the SharePoint Server running "Excel Calculation Services".  In this example: PRIME13.

Important Note:

If “Excel Services” is running on multiple SharePoint machines and the C2WTS is running as Local System, you will need to Constrain each SharePoint machine (Netbios Name) running “Excel Services” to the backend Service Account(s) (SQLSvc and/or SSASSvc).
If you are running the C2WTS as a domain account.  For example Contoso\C2WTSSvc, you will only need to Constrain that one account to the backend Service Account(s) (SQLSvc and/or SSASSvc).

Step 2: Configuration:

What we need to do now is add the correct Service Principal Name to the Service Accounts running SQL/SSAS and then use Kerberos Constrained Delegation.

Service Principal Names (SPNs):

Add these SPNs to the account running the data source your workbook is connecting to.  Either "SQL Server (MSSQLSERVER)" or "SQL Server Analysis Services (<Instance>)":

Account Running SQL Server (SQLSvc):

MSSQLSVC/<SQLServer_Name>
MSSQLSVC/<SQLServer_Name.FQDN

Example:

MSSQLSVC/SQLSvr
MSSQLSVC/SQLSvr.contoso.com

Account Running SQL Server Analysis Services (SSASSvc):

MSOLAPSvc.3/<SQL_Server_Analysis_Services_Server_Name>
MSOLAPSvc.3/<SQL_Server_Analysis_Services_Server_Name.FQDN>

Example:

MSOLAPSvc.3/SSASSvr
MSOLAPSvc.3/SSASSvr.contoso.com

Important Note:  If Analysis Services has a named instance other than ("MSSQLSERVER)" (this is the default Instance) you will need include that in the Service Principal Name.  In the below example, I will use the instance name "TABULAR".

MSOLAPSvc.3/<SQL_Server_Analysis_Services_Server_Name:Instance>
MSOLAPSvc.3/<SQL_Server_Analysis_Services_Server_Name.FQDN:Instance>

Example:

MSOLAPSvc.3/SSASSvr:TABULAR
MSOLAPSvc.3/SSASSvr.contoso.com:TABULAR

Important Note 2: If the Analysis Services Server has a Named Instance other than (MSSQLSERVER) (which is Default), like "TABULAR".  You will need to add MSOLAPDisco.3 SPNs to the account running the SQL Browser service (Example: Contoso\BrowserSvc). “Disco” is short for “Discovery”.  This needs to be set so the Browser Service can discover SSAS.

MSOLAPDisco.3/<SQL_Server_Analysis_Services_Server_Name>
MSOLAPDisco.3/<SQL_Server_Analysis_Services_Server_Name.FQDN>

Example:

MSOLAPDisco.3/SSASSvr
MSOLAPDisco.3/SSASSvr.contoso.com

Article: More Information regarding the MSOLAPDisco.3 SPNs can be found in the below article:

An SPN for the SQL Server Browser service is required when you establish a connection to a named instance of SQL Server Analysis Services or of SQL Server
http://support.microsoft.com/kb/950599

Kerberos Constrained Delegation (KCD):

Constrain Delegation between the account running "Excel Calculation Service" and "Claims to Windows Token Service" to "SQL Server" and SQL Server Analysis Services"

"Excel Services" to SQL:

In Active Directory Users and Computers > ECSSvc > Properties > Delegation Tab > Trust this computer for delegation to specified services only > Use any authentication protocol > Add… > Users or Computers… > SQLSvc> Check Names > OK > “Select All” Available Services > OK > OK.

"Excel Services" to "Analysis Services":

In Active Directory Users and Computers > ECSSvc > Properties > Delegation Tab > Trust this computer for delegation to specified services only > Use any authentication protocol > Add… > Users or Computers… > SSASSvc > Check Names > OK > “Select All” Available Services > OK > OK

"Claims to Windows Token Service" (if running as Local System): to SQL:

In Active Directory Users and Computers > PRIME13 > Properties > Delegation Tab > Trust this computer for delegation to specified services only > Use any authentication protocol > Add… > Users or Computers… > SQLSvc > Check Names > OK > “Select All” Available Services > OK > OK.

"Claims to Windows Token Service" (if running as C2WTSSvc) to SQL:

In Active Directory Users and Computers > C2WTSSvc > Properties > Delegation Tab > Trust this computer for delegation to specified services only > Use any authentication protocol > Add… > Users or Computers… > SQLSvc > Check Names > OK > “Select All” Available Services > OK > OK.

"Claims to Windows Token Service" (if running as Local System) to "Analysis Services":

In Active Directory Users and Computers > PRIME13 > Properties > Delegation Tab > Trust this computer for delegation to specified services only > Use any authentication protocol > Add… > Users or Computers… > SSASSvc > Check Names > OK > “Select All” Available Services > OK > OK

"Claims to Windows Token Service" (if running as C2WTSSvc) to SQL:

In Active Directory Users and Computers > C2WTSSvc > Properties > Delegation Tab > Trust this computer for delegation to specified services only > Use any authentication protocol > Add… > Users or Computers… > SSASSvc > Check Names > OK > “Select All” Available Services > OK > OK.

Please see my Visio Diagram.  It may take a few seconds to load since it is large.

If this fails please see our other blog:

Tools and Techniques: Troubleshooting Kerberos in Excel Services and PowerPivot for SharePoint
http://blogs.technet.com/b/excel_services__powerpivot_for_sharepoint_support_blog/archive/2013/02/08/troubleshooting-kerberos-for-excel-services-and-powerpivot.aspx

Comments (11)

  1. Ashok: Correct, you do not need to setup the SPN for the account running the application pool for the SharePoint web application, and WFE servers do not need trusted delegation.

  2. Ketan: If you are using EffectiveUserName, you do not need to configure KCD. However, please note that EffectiveUserName can only be used for tabular and multidimensional Analysis Services data sources. Also, EffectiveUserName works with Excel Services,
    but not PowerPivot. If the data connection is a PowerPivot connection, you will not be able to use EffectiveUserName.

  3. suma says:

    In Sharepoint 2013, Excel Preview doesnot works where as all other docs(Word, PPT, One Note, Paitn) preview works fine.

    Can any one have reason or answer for this?

  4. Hi Suma,

    Excel preview is a function of Office Web Apps 2013, and the above article does not apply as this is addressing Excel Services in SharePoint 2013.

  5. Ashok says:

    Does this then mean that since the Excel Services are running on others (non-wife) servers, we do not need to setup SPN for the domain account for the portal website’s application pool account and also do not need to enable the WFE servers for trusted
    delegation?
    Thank you for this very clear explanation.

  6. ketan says:

    Hey is the KCD configuration still needed with SP 2013 Excel Services? As I understand EffectiveUSerName property was introduced in SP 2013 and that should help avoid the KCD in Excel and PowerPivot? Or thats not the case?

  7. Lorenzo Soncini, Italy says:

    When you write "Excel Services to SQL" the red word ECSSvc is a user and my Active Directory User And Computer don’t have delegation tab for user but only for computer

  8. Lorenzo Soncini, Italy says:

    I have found this blog …http://blogs.technet.com/b/sharepoint_-_inside_the_lines/archive/2013/05/28/sharepoint-2013-with-ssrs-2012-and-constrained-delegation.aspx
    who tell me: "Once the SPNs are in place I can access the delegations tab for the service accounts". For Excel Service Account ECSSvc use SPExcel

  9. Matt says:

    Great article! It provided a general overview of steps needed that I was able to continuously refer to while configuring Kerberos in our environment. I did also rely on the detailed Microsoft documentation here:
    https://technet.microsoft.com/en-us/library/gg502605(v=office.14).aspx

    This article (https://msdn.microsoft.com/en-us/library/jj879294.aspx#bkmk_interactive_refresh) also gave me a good overview that lead me to some additional critical
    information and steps!

    In addition to the constrained delegation the accounts you are using will need some Local Policy or GPO rights that can be found in the articles above. Also the C2WTS account has to be able to read the users from Active Directory!!!! Local System generally
    will have that permission in an enterprise domain but a domain account may not our admins for example removed read access from Authenticated users.

    And last trick that I got from looking at diagrams from the second link is that if you have a PowerPivot model embedded into an Excel Work book you will have to also enable Kerberos delegation from SSAS user running POWERPIVOT SSAS Instance to SQLService for
    data sources. Otherwise the work book will still pass "Anonymous" as the last step of the chain and the refresh will fail.

  10. mahdi moghimi says:

    How Can I pass EffectiveUserName to SSAS through Excel Service using FBA(Forms based authentication) in SharePoint 2013

    We have an OLAP project which uses Sharepoint 2013 Excel Services to show excel files that contains external data from SQL Server Analysis Services (SSAS) cube. Our Authentication method to login users is FBA (Forms Based Authentication)
    Our Goal is to show only subset of data from SSAS cube for each user based on their role which has already been defined in SSAS side.
    FBA authenticates users against AD and all authorized user are already member of respective role in SSAS based on their clearance level. We need Excel Services passes EffectiveUserName to SSAS and for that we enabled the EffectiveUserName in global settings of Excel Services on SharePoint central admin.
    Than in Excel, we have enabled the Windows Authentication in the Excel Service Authentication Settings of Connection Properties as we read on some blog posts. But after accessing it via the SharePoint, the following error occurred.
    “The data connection uses Windows Authentication and user credentials could not be delegated.” As I already stated, I don’t want to delegate user credentials to SSAS. It’s already been configure for Excel Services to use single account to connect to Analysis Services. I only want to pass EffectiveUserName.
    Using windows authentication is not an option because users access the site from extranet and internet.

    need help – please help me. please send me email.

    1. Hi Mahdi,
      You cannot pass EffectiveUserName or use Kerberos without using Windows Authentication for your site. As you are forced into using Forms Auth for your site, your only option would be to use the Secure Store to refresh your data. We consider EffectiveUserName to be a “failover” or “backup” for Kerberos, not a replacement.

      Note the following:
      https://msdn.microsoft.com/en-us/library/dn140245.aspx#bkmk_auth
      “Use when an end user identity must be impersonated on the server. Specify the account in a domain\user format.”

      When the connection string is built it expects the user to be passed in WINDOWS format. FBA users cannot be passed in Windows format properly thus EffectiveUserName will fail.

      Lastly, for EffectiveUserName to work, the SQL server still needs to Authorize these users which means reaching out to AD to ensure that the account still exists and is valid. Even if you were able to pass an FBA user to SQL, it would not know where to validate the account. On the same token, if the users are from an untrusted domain, SQL cannot validate them and the refresh will fail.

Skip to main content