Secure Store for Business Intelligence service applications (SharePoint Server 2010)

Applies to: SharePoint Server 2010

This article describes how Microsoft SharePoint Server 2010 business intelligence features use the Secure Store Service to provide access to external data sources (such as SQL Server) for SharePoint Server 2010 users. For the purposes of this article, the SharePoint Server 2010 Business Intelligence service applications are:

• Excel Services

• PerformancePoint Services

• Visio Services

The SharePoint Server 2010 Business Intelligence service applications offer two methods of data access for users:

• Integrated Windows authentication using Constrained Kerberos delegation

• Secure Store Service

This article covers the Secure Store Service and its relationship to the Business Intelligence service applications. For information about using Integrated Windows authentication with Constrained Kerberos delegation, see Plan for Kerberos authentication (SharePoint Server 2010).

Secure Store Service

Secure Store is a feature in SharePoint Server 2010 that helps provide access to data outside SharePoint Server 2010 (for example, SQL Server data) by allowing a Business Intelligence service application to use a set of credentials with data access on behalf of a SharePoint Server 2010 user who is attempting to access that data. Such use of credentials by Business Intelligence service applications on behalf of users is called impersonation.

Secure Store provides this mapping between Business Intelligence services applications, users, and credentials through the use of a Target Application. A Secure Store Target Application is a collection of metadata that specifies which users shall be allowed access to a particular set of credentials that a Business Intelligence service application will use for impersonation when accessing external data. This metadata is stored in the Secure Store database along with the credentials themselves, which are encrypted.

Secure Store Target Applications can be used in many ways within SharePoint Server 2010, but for the purposes of SharePoint Server 2010 Business Intelligence scenarios, Target Applications consist of the following settings, configurable by the Farm Administrator:

• Administrators   Target Application Administrators are users who have privileges to administer a given Secure Store Target Application. This can be the Farm Administrator or a specific user or users, depending on your needs. For Target Applications created by PerformancePoint Services, the Administrator is configured automatically by PerformancePoint Services and the user configuring the Unattended Service Account is added as the Administrator.

• Members   The Members of a Target Application are the users on behalf of whom the Business Intelligence Service Application will impersonate the Target Application Credentials when it accesses external data. This could be a single user, multiple users, or an Active Directory group. Members are also referred to as Credential Owners. For Target Applications created by PerformancePoint Services, the service account used by the PerformancePoint Services application pool is used as the Member.

• Credentials   Target Application Credentials consist of an Active Directory account with direct access to data sources. (You must grant the required data access to this account directly — access to external data sources is not controlled by SharePoint Server 2010. This should be a low privileged account that only allows data access.) It is this account that is impersonated by Business Intelligence service applications to give users access to data.

The Administrators, Members, and Credentials are configurable by the Farm Administrator directly through Secure Store for Excel Services and Visio Services. For PerformancePoint Services, these values are configured through the PerformancePoint Service Application Settings and should not be modified through Secure Store.

Visio Services and Excel Services can use Secure Store using one of two methods:

• Specified Target Application   A specific Target Application is specified by the Excel worksheet or the Visio Web drawing. When a user accesses the worksheet or Web drawing, Secure Store uses the credentials associated with that Target Application for data access. For Visio Services, this Target Application must be specified using an ODC file that is hosted on SharePoint Server 2010.

• No specified Target Application (Unattended Service Account)   No Target Application is specified by the Excel worksheet or the Visio Web drawing. When a user accesses the worksheet or Web drawing connected to an external data source, Secure Store uses the Target Application specified in the Global Settings of Excel Services or Visio Services. When a Target Application is specified globally for a Business Intelligence service application, the Target Application Credentials are referred to as the Unattended Service Account.

PerformancePoint Services cannot specify a specific Secure Store Target Application — it can only use Secure Store with the Unattended Service Account.

The basic sequence of events that occurs is as follows:

1. A SharePoint Server 2010 user accesses a data-connected object such as an Excel Services worksheet, Visio Services Web drawing, or PerformancePoint Services dashboard.

2. If the object is configured to use Secure Store for data authentication, the Business Intelligence Service Application calls the Secure Store service to access the Target Application specified by the object.

3. If the user is a Member of that Target Application, the credentials stored in the Target Application are returned and the Business Intelligence Service Application impersonates the credentials while accessing the data.

4. The data is displayed to the user within the context of the worksheet, Web drawing, or dashboard.

Data connection files

All of the Business Intelligence service applications can use data connection files to specify authentication information. Excel Services and Visio Services use Office Data Connection (.ODC) files and PerformancePoint Services uses PerformancePoint Services Data Connection (.PPSDC) files. Use of such files allows multiple Excel Services worksheets, Visio Services Web drawings, or PerformancePoint Services dashboards to share a common set of data access parameters.

The SharePoint Server 2010 Business Intelligence service applications each use data connection files differently. For a description of how each uses data connection files, see the section for each service application, below.

The Unattended Service Account

Unattended Service Account refers to the credentials of a Secure Store Target Application that is specified in the global settings of a Business Intelligence service application. This Target Application is used to provide data access to users when another authentication method is not specified. For Visio Services, the Unattended Service Account is required any time that Integrated Windows authentication is not used, even if additional connection information is provided in the connection file (for example, a SQL Authentication string).

Data access from client and server

Microsoft Excel 2010 and Microsoft Visio 2010 are client applications that function independently from SharePoint Server 2010. Though they can publish documents to SharePoint Server 2010, they cannot use Secure Store directly for authentication to data sources. When you create or edit a data-connected worksheet or Web drawing, you must use Integrated Windows authentication or another applicable authentication method to connect directly to a data source from Excel 2010 or Visio 2010. (Other authentication methods you might use include SQL Authentication or an OLEDB connection string.) Once the worksheet or Web drawing is published to SharePoint Server 2010, Excel Services or Visio Services can use Secure Store to connect to the data source when displaying the content to a user.

PerformancePoint Services Dashboard Designer is directly integrated with SharePoint Server 2010. Dashboard Designer can use Secure Store directly to authenticate using the Unattended Service Account. As a result, users of Dashboard Designer do not need direct access to data sources through Integrated Windows authentication, provided the Unattended Service Account has the required access.

Excel Services and Visio Services

Excel Services and Visio Services use Secure Store similarly:

• Both can store a Secure Store Target Application that is specified in an ODC file.

• Both can use the Unattended Service Account.

However, there are some key difference between Excel Services and Visio Services, discussed in the sections that follow.

Excel Services

The data connections used by Excel Services must be configured in Excel 2010 prior to publication to a SharePoint Server 2010 site. An Excel 2010 worksheet can specify data connection information directly or it can include a pointer to an ODC file where connection information can be found.

The following authentication settings are available within a data-connected Excel 2010 workbook or ODC file:

• Integrated Windows authentication   Specifies Integrated Windows authenticationwith Kerberos delegation to authenticate each individual user when viewing an Excel 2010 workbook through Excel Services.

• SSS ID   Designates a specific Secure Store Service Target Application to be used for data source access.

• None   Uses the credentials specified in the connection string, if any; otherwise it uses the Secure Store Unattended Service Account designated in the Excel Services global settings.

These settings can only be edited by opening the worksheet or ODC file in Excel 2010.

Visio Services

Visio Services supports two methods of data connection for Visio Web drawings:

• Embedded connection information

• External connection information that uses an ODC file

When you create a Visio diagram and connect it directly to a data source, Visio 2010 stores the data source information directly in the file when you publish the Web drawing to SharePoint Server 2010. When a user views the Web drawing, Visio Services connects to the data source using the Secure Store Unattended Service Account specified in the Visio Services global settings.

If, instead of connecting directly to a data source from Visio 2010, you connect to a data source using an existing ODC file stored on SharePoint Server 2010, Visio 2010 maintains the link to that ODC file when you publish the Web drawing. Visio Services then uses the connection information stored in the ODC file when it connects to the data source. This includes using a specific Secure Store Target Application if one is specified in the ODC file.

Visio 2010 cannot edit ODC files. We recommend that you do as follows to use an ODC file with a Visio Web drawing: Create the ODC file in Excel 2010, publish it to SharePoint Server 2010, and then connect to it as a data source from Visio 2010 when you create a new data-connected diagram. You must use Excel 2010 to edit ODC files if you want to change the data query, authentication information, specify a Target Application, or modify other settings.

Visio Services cannot parse complex SQL queries. If you attempt to use an ODC file containing a complex query, Visio Services may be unable to run the query and retrieve the data.

PerformancePoint Services

PerformancePoint Services only makes use of Secure Store through the Unattended Service Account. The choice between Integrated Windows authenticationand the Unattended Service Account is made through Dashboard Designer when you create or edit a data source.

The Secure Store Target Application for the PerformancePoint Services Unattended Service Account is configured as part of the PerformancePoint Services service application settings by an administrator. While this Target Application appears on the Secure Store Target Applications list, it should not be modified directly through Secure Store.

Summary of differences

As described in this article, each of the Business Intelligence service applications makes use of Secure Store in a different way. The following table summarizes the Secure Store functionality and options for each Business Intelligence service application.

See Also

Concepts

Demo: Excel Services and Secure Store: Authentication using an ODC file
Configure the Secure Store Service (SharePoint Server 2010)
Excel Services overview (SharePoint Server 2010)
Use Excel Services with Secure Store (SharePoint Server 2010)
PerformancePoint Services overview (SharePoint Server 2010)
Plan for PerformancePoint Services security (SharePoint Server 2010)
Plan for Visio Services (SharePoint Server 2010)

Other Resources

Resource Center: Business Intelligence in SharePoint Server 2010