Share via

Identity delegation for Excel Services (SharePoint Server 2010)

  • Article

 

Applies to: SharePoint Server 2010, Excel Services

In this scenario you add the Excel Services service application to the SharePoint Server environment and configure Kerberos constrained delegation to allow the service to refresh data in a worksheet from an external SQL Server data source.

Note

If you are installing on Windows Server 2008, you may have to install the following hotfix for Kerberos authentication:
A Kerberos authentication fails together with the error code 0X80090302 or 0x8009030f on a computer that is running Windows Server 2008 or Windows Vista when the AES algorithm is used (https://support.microsoft.com/kb/969083)

Scenario dependencies

To complete this scenario you need to have completed the following articles:

Configuration checklist

Area of Configuration Description

Active Directory Configuration

Create Excel Services service account

Configure SPN on Excel Services service account

Configure Kerberos constrained delegation for servers running Excel Services

Configure Kerberos constrained delegation for the Excel Services service account

SharePoint Server configuration

Start Claims to Windows Token Service on Excel Services Servers

Start the Excel Services service instance on the Excel Services server

Create the Excel Services service application and proxy

Configure Excel services trusted file location and authentication settings

Verify Excel Service Constrained Delegation

Create document library to host test workbook

Create test SQL database and test table

Create test Excel workbook with SQL data connection

Publish workbook to SharePoint Server and refresh data connection

Scenario environment details

Kerberos constrained delegation paths

Diagram of scenario environment

In this scenario we will configure the SharePoint Server Excel Services service account for Kerberos constrained delegation to the SQL Server service.

Note

In this scenario we will configure the Claims to Windows Token Services (C2WTS) to use a dedicated service account. If you leave the C2WTS configured to use Local System you will need to configured constrained delegation on the computer account for the computer running the C2WTS and Excel Services.

SharePoint Server logical authentication

Diagram of authenticaiton flow

Authentication in this scenario begins with the client authenticating with Kerberos authentication at the web front end. SharePoint Server 2010 will convert the Windows authentication token into a claims token using the local Security Token Service (STS). The excel service application will accept the claims token and convert it into a windows token (Kerberos) using the local Claims to Windows Token Service (C2WTS) that is a part of Windows Identity Framework (WIF). The excel service application will then use the client’s Kerberos ticket to authenticate with the backend DataSource.

Step-by-step configuration instructions

Active Directory configuration

Create Excel Services service account

As a best practice Excel Services should run under its own domain identity. To configure the Excel Service Application an Active Directory accounts must be created. In this example the following accounts were created:

SharePoint Server Service IIS App Pool Identity

Excel Services

vmlab\svcExcel

Configure SPN on the Excel Services service account

Kerberos constrained delegation must be configured if Excel Services is going to delegate the client’s identity to a back end data source. In this example Excel services will query data from a SQL transactional database, therefore Kerberos delegation is required.

The Active Directory Users and Computers MMC snap-in is typically used to configure Kerberos delegation. To configure the delegation settings within the snap-in, the Active Directory object being configured must have a service principal name applied; otherwise the delegation tab for the object will not be visible in the object’s properties dialog. Although Excel Services does not require a SPN to function, we will configure one for this purpose.

On the command line, run the following command:

SETSPN -S SP/ExcelServices

Note

The SPN is not a valid SPN. It is applied to the specified service account to reveal the delegation options in the AD users and computers add-in. There are other supported ways of specifying the delegation settings (specifically the msDS-AllowedToDelegateTo AD attribute) but this topic will not be covered in this document.

Configure Kerberos constrained delegation for Excel Services

To allow excel services to delegate the clients identity Kerberos constrained delegation must be configured. It is required to configure constrained delegation with protocol transition for the conversion of claims token to windows token via the WIF C2WTS.

Each server running excel services must be trusted to delegate credentials to each back-end service excel will authenticate with. In additional, the excel services service account must also be configured to allow delegation to the same back-end services.

In our example the following delegation paths are defined:

Principal Type Principal Name Delegates To Service

User

svcExcel

MSSQLSVC/MySqlCluster.vmlab.local:1433

*User

svcC2WTS

MSSQLSVC/MySqlCluster.vmlab.local:1433

**Computer

VMSP10APP01

MSSQLSVC/MySqlCluster.vmlab.local:1433

* Configured later in this scenario

** Only required if the C2WTS is running as local system

To configure constrained delegation

  1. Open the Active Directory Object’s properties in Active Directory Users and Computers.

  2. Navigate to the Delegation tab.

  3. Select Trust this user for delegation to specified services only.

  4. Select Use any authentication protocol. This enables protocol transition and is required for the service account to use the C2WTS.

  5. Click the add button to select the service principal allowed to delegate to.

  6. Select User and Computers.

  7. Select the service account running the service you wish to delegate to. In this example it is the service account for the SQL service.

    Note

    the service account selected must have a SPN applied to it. In our example the SPN for this account was configured in a previous scenario.

  8. Click OK. You will then be asked to select the SPNs you would like to delegate to on the following screen.

  9. Select the services for the SQL cluster and click OK.

  10. You should now see the selected SPNS in the services to which this account can presented delegated credentials list.

  11. Repeat these steps for each delegation path defined in the beginning of this section.

Verify MSSQLSVC SPN for the Service Account running the service on the SQL Server (performed in Scenario 2)

Verify the SPN for Analysis Services service account (vmlab\svcSQL) exists with the following SetSPN command:

SetSPN -L vmlab\svcSQL

You should see the following:

MSSQLSVC/MySqlCluster MSSQLSVC/MySqlCluster.vmlab.local:1433

SharePoint Server configuration

Configure and Start the Claims to Windows Token Service on Excel Services Servers

The Claims to Windows Token Service (C2WTS) is a component of the Windows Identity Foundation (WIF) which is responsible for converting user claim tokens to windows tokens. Excel services uses the C2WTS to convert the user’s claims token into a windows token when the services needs to delegate credentials to a back-end system which uses Integrated Windows authentication. WIF is deployed with SharePoint Server 2010 and the C2WTS can be started from Central Administration.

Each Excel Services Application server must run the C2WTS locally. The C2WTS does not open any ports and cannot be accessed by a remote caller. Further, the C2WTS service configuration file must be configured to specifically trust the local calling client identity.

As a best practice you should run the C2WTS using a dedicated service account and not as Local System (the default configuration). The C2WTS service account requires special local permissions on each server the service runs on so be sure to configure these permissions each time the service is started on a server. Optimally you should configure the service account’s permissions on the local server before starting the C2WTS, but if done after the fact you can restart the C2WTS from the Windows services management console (services.msc).

To start the C2WTS

  1. Create a service account in Active Directory to run the service under. In this example we created vmlab\svcC2WTS.

  2. Add an arbitrary Service Principal Name (SPN) to the service account to expose the delegation options for this account in Active Directory Users and Computers. The SPN can be any format because we do not authenticate to the C2WTS using Kerberos authentication. It is recommended to not use an HTTP SPN to avoid potentially creating duplicate SPNs in your environment. In our example we registered SP/C2WTS to the vmlab\svcC2WTS using the following command:

    SetSPN -S SP/C2WTS vmlab\svcC2WTS

  3. Configure Kerberos constrained delegation on the C2WTS services account. In this scenario we will delegate credentials to the SQL service running with the MSSQLSVC/MySqlCluster.vmlab.local:1433 service principal name.

  4. Next, configure the required local server permissions that the C2WTS requires. You will need to configure these permissions on each server the C2WTS runs on. In our example this is VMSP10APP01. Log onto the server and give the C2WTS the following permissions:

    1. Add the service account to the local Administrators Groups.

    2. 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

  5. Open Central Administration.

  6. Under Security->Configure Managed Service Accounts, Register the C2WTS service account as a managed account.

  7. Under services, select Manage services on server.

  8. In the server selection box in the upper right hand corner select the server(s) running excel services. In this example it is VMSP10APP01.

  9. Find the Claims to Windows Token Service and start it.

  10. Go to Security->Manage Service Accounts. Change the identity of the C2WTS to the new managed account.

    Note

    If the C2WTS was already running before configuring the dedicated service account, or if you need to changes the permissions of the service account after the C2WTS is running you must restart the C2WTS from the services console.

In addition, if you experience issues with the C2WTS after restarting the service it may also be required to reset the IIS application pools that communicate with the C2WTS.

Add Startup dependencies the WIF C2WTS service

There is a known issue with the C2WTS where it may not automatically startup successfully on system reboot. A workaround to the issue is to configure a service dependency on the Cryptographic Services service:

  1. Open the Command Prompt window.

  2. Type: sc config "c2wts" depend= CryptSvc

  3. Find the Claims to Windows Token Service in the services console.

  4. Open the properties for the service.

  5. Check the Dependencies tab. Make sure Cryptographic Services is listed.

  6. Click OK.

Grant the Excel Services service account permissions on the web application content database

A required step in configuring SharePoint Server 2010 Office Web Applications is allowing the web application’s service account access to the content databases for a given web application. In this example, we will grant the Excel Services service account access to the “portal” web application’s content database by using Windows PowerShell.

Run the following command from the SharePoint 2010 Management Shell:

$w = Get-SPWebApplication -Identity http://portal

$w.GrantAccessToProcessIdentity("vmlab\svcExcel")

Start the Excel Services service instance on the Excel Services server

Before creating an Excel Services service application, start the excel services serve service on the designated Farm servers.

  1. Open Central Administration.

  2. Under services, select Manage services on server.

  3. In the server selection box in the upper right hand corner select the server(s) running excel services. In this example it is VMSP10APP01.

  4. Start the Excel Calculation Services service.

Create the Excel Services service application and proxy

Next configure a new Excel Services service application and application proxy to allow web applications to consume Excel Services:

  1. Open Central Administration.

  2. Select Manage Service Applications under Application Management.

  3. Select New, and then click Excel Services Application.

  4. Configure the new service application. Be sure to select the correct service account (create a new managed account if the excel service account is not in the list).

Configure Excel services trusted file location and authentication settings

Once the Excel Services application is created, configure the properties on the new service application to specify a trusted host location and authentication settings.

  1. Open Central Administration.

  2. Select Manage Service Applications under Application Management.

  3. Click the link for the new Service Application, Excel Services in this example.

  4. In the excel services management screen, click "Trusted File Locations".

  5. Add a new trusted file location.

  6. Specify the location to your test library.

    Note

    In our example, we trust the root web application URL and all children. In a production environment you may choose to constrain the trust to a more granular location.

  7. In External Data Select trusted data connection libraries and embedded.

    Note

    This example will use an embedded connection to connect to SQL Server. In your environment you may choose to create a separate connection file and store it in a trusted data connection library. In that case you might select Trusted data connection libraries only.

  8. Change the External Data Cache Age — For testing purposes, it is convenient to change the external data cache lifetime to ensure data refreshes are coming from the data source and not the cache. Under External Data, change the following settings:

    Automatic refresh (periodic / on-open) = 0

    Manual refresh = 0

    Note

    In a production environment you will want to configure a cache setting higher than 0. Setting the cache to 0 is for testing purposes only.

Verify Excel Services constrained delegation

Create document library to host the test workbook

Open a site in the trusted path that was configured in the previous step. Create a new document library to host a test Excel workbook.

Create test Excel workbook with SQL data connection

Next create an Excel workbook with a data connection to the new test database:

  1. Open Excel.

  2. On the Data tab, select From other sources->From SQL Server.

  3. Connect to the test SQL data source.

  4. Select the test database and the test table (Sales in our example).

  5. Click Next. Click the authentication settings button. Ensure Windows Authentication is specified.

  6. Click Finish.

  7. Select Pivot Table Report.

  8. Configure the pivot table. Ensure data is returned from the SQL source.

Publish workbook to SharePoint Server and refresh data connection

The last step to validate the Excel Services application is to publish the workbook and test refreshing the embedded SQL connection.

  1. Click the File tab.

  2. Click Save and Send, then click Save to SharePoint, and then click Browse for a location.

  3. Enter the location to the trusted library created in previous steps.

  4. Ensure Open with Excel in the browser is selected.

    A new browser window will open at this point with your test workbook displayed. Once the workbook renders, refresh the data connection by clicking Data and then clicking Refresh All Connections.

    If the data connection refreshes you have successfully configured Kerberos delegation for excel services. To further test connectivity, change the source data via SQL Management Studio then refresh the connection. You should see the newly changed data in your workbook. If you do not see any changes, and you do not receive any errors on refresh you are most likely seeing cached data. By default, Excel Services will cache data from external sources for five minutes. You can change this cache setting; see Configure Excel services trusted file location and authentication settings in this article for more information.