Configuring Excel Services in SharePoint 2010


By:   |   Updated: 2010-10-26   |   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
  • 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


Last Updated: 2010-10-26


get scripts

next tip button



About the author





Comments For This Article





download





Recommended Reading

Reading SharePoint Lists with Integration Services 2017

Using a SharePoint List as a Data Source in SQL Server Reporting Services 2008 R2

Using SSIS to Automatically Populate a SharePoint List

Understanding SharePoint Exports to Excel

PerformancePoint Dashboard - a Primer








get free sql tips
agree to terms