Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Next Webcast - Simple SQL Server Reporting - Click Here to Register
 

Configuring Excel Services in SharePoint 2010


By:   |   Read Comments   |   Related Tips: > 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 Update:


next webcast button


next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools