Use Excel Services with Secure Store (SharePoint Server 2010)

Applies to: SharePoint Server 2010

This series of articles describes how to configure data refresh in Excel Services in Microsoft SharePoint Server 2010 by using the Secure Store Service to map user and group credentials to the credentials of external data sources.

The Secure Store Service is a claims-aware authorization service. In Microsoft SharePoint Server 2010, it replaces the single sign-on (SSO) component that is used in Microsoft Office SharePoint Server 2007. The Secure Store Service includes a database for storing encrypted credentials that can be used to access external data sources.

In Secure Store you specify a group of users to whom you want to grant access to a data source and a set of credentials that has access to that data source. The user information is stored in a Secure Store target application and the associated credentials are stored, encrypted, in the Secure Store database. You can then specify the target application in a workbook, an Office Data Connection (ODC) file, or in Excel Services Global Settings, and Excel Services will use the stored credentials on behalf of the specified users to refresh data in a data-connected workbook.

Excel Services can be used with Secure Store in three primary scenarios:

Unattended Service Account: The unattended service account is an account that is used by Excel Services to provide broad database access to all users in the farm. Use the unattended service account for accessing data that is not considered sensitive or where you do not want to restrict access to a certain group of users. For information about how to configure this scenario, see Configure Excel Services data refresh by using the unattended service account (SharePoint Server 2010).

Embedded Connections: In Excel, you can specify a Secure Store target application directly in the workbook. When the workbook is published to a SharePoint document library and then rendered by using Excel Services, the specified target application is used to refresh the data. For information about how to configure this scenario, see Configure Excel Services data refresh by using embedded data connections (SharePoint Server 2010).

External Data Connections: You can specify a Secure Store target application in an Office Data Connection (ODC) file and then connect to that ODC file in Excel. When you publish the workbook to a SharePoint document library, it maintains its connection to the ODC file. The connection information in the ODC file is used when Excel Services refreshes the data in the workbook. Using an ODC file has the following advantages:

• A single ODC file can be referenced by multiple workbooks. If the data source connection parameters change (for example, if you want to use a different Secure Store target application than the one originally specified) you need only update the ODC file and not the workbooks themselves.

• Using ODC files allows administrators to create and maintain the data connections used by the organization. You can create data connections appropriate for users, place them in a trusted data connection library, and then notify the users of which ODC files to use for their queries.

For information about how to configure this scenario, see Configure Excel Services data refresh by using external data connections (SharePoint Server 2010).

See Also

Concepts

Use Secure Store with SQL Server Authentication (SharePoint Server 2010)
Secure Store for Business Intelligence service applications (SharePoint Server 2010)