Configuring Excel Services in SharePoint 2010

By:   |   Comments   |   Related: > SharePoint


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
  • service applications
  • Click on Secure Store Service (the first one in the list, application, not the proxy)
  • secure store service
  • In the Edit tab on the ribbon bar, click New.
  • generate new key
  • 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.
  • central administration
  • 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
  • sharepoint
  • After the Target Application ID is created, select the checkbox next to ExcelServices, and click Generate New Key in the ribbon.
  • edit
  • On the next screen, enter your Pass Phrase in the Pass Phrase and Conform Pass Phrase boxes
  • generate new key
  • 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)
secure store target

Configure Excel Services

In SharePoint Central Administration

  1. Click Application Management
  2. Click on Manage Service Applications
  3. Click on Excel Services Applications
  4. Click Global Settings.
  5. Scroll to the bottom and in the Application ID field, enter ExcelServices
  6. Click OK.
site actions

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!

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Matt Takhar Matt Takhar

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

get free sql tips
agree to terms