Configuring Excel Services in SharePoint 2010

By: Matt Takhar | Comments | Related: > SharePoint

Problem

Configuring Excel Services in SharePoint 2007 is fairly simple. However, in SharePoint 2010, Microsoft has changed the "Unattended Service Account" to use an "Application ID" from the "Secure Store Service Application". If you don't setup an Unattended Service Account then all of your data connections in Excel will need the credentials within the connection or will need to revert to the current user.

Configuring the "Unattended Service Account" means you can set your authentication on the Excel data connection to None and use the credentials in the Secure Store Service application instead.

This is an easy step to miss.

Solution

Please follow the steps below to correctly configure SharePoint 2010 Excel Services to use an Application ID from the Secure Store Services.

Configure the following in SharePoint Central Administration

Secure Store Services

Under Application Management, click on Manage Service Applications

Click on Secure Store Service (the first one in the list, application, not the proxy)

In the Edit tab on the ribbon bar, click New.

In the "Create New Secure Store Target application" page, enter the following:

Target Application ID: ExcelServices
Display Name: ExcelServices
Contact Email: [email protected] (Use a real address)
Target Application Type: Group
Target Application URL: None

Click Next.

On the next screen, use the field defaults (Windows UserName, Windows Password) and click Next
On the next screen, enter the following values:
- Target Application Administrators: (Domain\Administrator)
- Members: (Domain\Domain Users)
Click OK

After the Target Application ID is created, select the checkbox next to ExcelServices, and click Generate New Key in the ribbon.

On the next screen, enter your Pass Phrase in the Pass Phrase and Conform Pass Phrase boxes

Again on the screen listing the Target Application IDs, select the checkbox next to ExcelService, and click Set Credentials in the ribbon.
On the next screen, enter the Username and password into the fields: (this user must have access to the data that needs to be queried)

Configure Excel Services

In SharePoint Central Administration

Click Application Management
Click on Manage Service Applications
Click on Excel Services Applications
Click Global Settings.
Scroll to the bottom and in the Application ID field, enter ExcelServices
Click OK.

You will now find Excel spreadsheets with data connections in them which have their authentication set to None will use the Unattended Service Account to retrieve their data!