Excel Services configuration on SharePoint 2010

In this document, I will explain how to configure Excel Services on Microsoft SharePoint 2010 and how to solve an issue that may occur during Excel Services configuration\testings.

Configuration

Enable Claims to Windows Token Service

The first step, check the status of the Claims to Windows Token Service on SharePoint.

Use the following instructions to check and enable the Claims to Windows Token Service.

  1. In Central Administration, in System Settings, click Manage services on server.
  2. Select Claims to Windows Token Service, and then click Start.
  3. Verify the service is also running on the Services console:
    • In Administrative Tools, click Services.
    • Start the Claims to Windows Token Service if it is not running.

Create a New Secure Store Application

Create a New Secure Store Target Application and set the credential.

This credential will be used by SharePoint to access the database. The account set into this step need to have access to the database or SSAS cube/tabular databases.

Use the following instructions to create and set credential.

  1. In Central Administration, in Application Management, click Manage service applications.
  2. Click Secure Store Services, and then click New.
  3. Target Application Settings:
    • Target Application ID: ExcelServicesSSS
    • Display Name: Excel Service Secure Store App
    • Contact e-mail: administrator@contoso.com
    • Target Application Type: Individual
    • Target Application Page URL: None
  4. Click Next.
  5. Click Next again.
  6. Target Application Administrators: contoso\administrator (Type the user account that will administrate the Secure Store Application.
  7. Click OK.
  8. Select Target Application ID ExcelServicesSSS, and then click Set Credential.
  9. Type Credential Owner, Windows User Name and Password.

Configure Excel Services

Add the Application Id created in the previous step to the Excel Services and configure the Trusted Data Connection Library.

Use the following instructions to add the Application ID.

  1. In Central Administration, in Application Management, click Manage service applications.
  2. Click Excel Services Application, and then click Global Settings.
  3. On External Data, Application ID type ExcelServicesSSS.
  4. Click OK.
  5. Click Trusted Data Connection Libraries.
  6. Click Add Trusted Data Connection Library.
  7. Address: http:/// (Enter the Data Connection Address)
  8. Click OK.

Connection Issue

Before start look at Excel Services issues, please make sure that you have installed SharePoint 2010 SP1 and also the cumulative updates for SharePoint 2010, please follow Microsoft recommendations for cumulative updates.

“The data connection uses Windows Authentication and user credentials could not be delegated. The following connections failed to refresh:”

Follow a list of the most likely causes of this error:

  • Set the connection in the Excel file to the ODC connection file in SharePoint.
  • The ODC connection file needs to be on Trusted Data Connection Library folder.
  • On the Excel File set Excel Service Authentication Settings to NONE.

 

Leave a Reply

%d bloggers like this: