Tips and Tricks to Growing Your SQL Server Estate from SMB to Enterprise Scale
Thursday, June 28, 2018 - click here to learn more
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.
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!
- Read more Excel Services Tips on MSSharePointTips.com
Last Update: 2010-10-26