Plan Excel Services data sources and external connections (SharePoint Server 2010)

Applies to: SharePoint Server 2010, Excel Services (SharePoint 2010)

To configure Microsoft SharePoint Server 2010 to enable workbooks rendered in a browser by Excel Services to successfully refresh external data, you must understand the relationships and dependencies between SharePoint Server 2010 and Excel Services.

In this article:

• Connections and Excel workbooks

• Data providers

• Authentication to external data

• Data connection libraries and managed connections

• Excel Services security and external data

Connections and Excel workbooks

Every Excel workbook that uses external data contains a connection to a data source. Connections consist of everything that is required to establish communications with, and retrieve data from, an external data source. This includes the following:

• A connection string (a string that specifies which server to connect to and how to connect to it).

• A query (a string that specifies what data to retrieve).

• Any other specifics required to get the data.

Embedded and linked connections

Excel workbooks can contain embedded connections and can link to external connections. Embedded connections are stored internally as part of the workbook. External connections are stored in the form of Office Data Connection (ODC) files that can be referenced by a workbook.

Embedded and external connections function the same way. Both will correctly specify all the required parameters to connect to data successfully. External connection files can be centrally stored, secured, managed, and reused. They are a good choice when planning an overall approach to getting a large group of users connected to external data. For more information, see Data connection libraries and managed connections.

For a single connection, a workbook can have both an embedded copy of the connection information and a link to an external connection file. The connection can be configured to always use an external connection file to refresh data from an external data source. In this case, if the external connection file cannot be retrieved, or if it does not establish a connection to the data source, the workbook cannot retrieve data. If the connection is not configured to use only an external connection file, Excel attempts to use the embedded copy of a connection. If that fails, Excel attempts to use the connection file to connect to the external data source.

For security purposes, Excel Services can be configured to enable only connections from connection files. In this configuration, all embedded connections are ignored for workbooks loaded on the server, and connections are tried only when there is a link to a valid connection file that is trusted by the server administrator. For more information, see Trusted data connection libraries.

Data providers

Data providers are drivers that applications (such as Excel and Excel Services) use to connect to specific data sources. For example, a special MSOLAP data provider is used to connect to Microsoft SQL Server 2008 Analysis Services (SSAS). The data provider is specified as part of the connection string when you connect to a data source.

Data providers handle queries, parsing connection strings, and other connection-specific logic. This functionality is not part of Excel Services. Excel Services cannot control how data providers behave.

Any data provider that is used by Excel Services must be explicitly trusted by Excel Services. For information about how to add a new data provider to the trusted providers list, see Manage Excel Services connections (SharePoint Server 2010).

By default, Excel Services trusts many well-known data providers. In most cases, you do not have to add a new data provider. Data providers are typically added for custom solutions.

Authentication to external data

Data servers require a user to be authenticated, that is, identify oneself to the server. The next step is authorization, communicating to the server the permitted actions associated with the user. Authentication is required for the data server to perform authorization, or to enforce security restrictions that prevent data from being exposed to anyone other than authorized users.

Excel Services has to communicate to the data source which user is requesting the data. In most scenarios, this is going to be the user viewing an Excel report in a browser. This section explains authentication between Excel Services and an external data source. Authentication at this level is shown in the following diagram. The arrow on the right side shows the authentication link from an application server that runs Excel Calculation Services to an external data source.

Excel Services supports the following authentication options:

• Windows Authentication   Excel Services uses Integrated Windows authentication and attempts to connect to the data source by using the Windows identity of the user who is displaying the workbook.

• SSS   Excel Services will use the credentials associated with the specified Secure Store target application.

• None   Excel Services will impersonate the unattended service account and pass the connection string to the data source.

The authentication option is configured in Microsoft Excel and is a property of the external data connection. The default value is Windows Authentication.

Integrated Windows authentication

If you choose the Windows Authentication option, Excel Services attempts to pass the Windows identity of the user viewing the Excel workbook to the external data source. Kerberos delegation is required for any data source that is located on a different server than the server where Excel Calculation Services is running, if that data source is using Integrated Windows authentication.

In most enterprise environments, Excel Calculation Services will be running on a different computer from the data source. This means that Kerberos delegation (constrained delegation is recommended) will be required to enable data connections that use Windows authentication. For more information about how to configure Kerberos constrained delegation for Excel Services, see Configure Kerberos authentication for SharePoint 2010 Products (white paper).

Secure Store Service

Secure Store is a SharePoint Server 2010 service application that is used to store encrypted credentials in a database for use by applications to authenticate to other applications. In this case, Excel Services uses Secure Store to store and retrieve credentials for use in authenticating to external data sources.

If you choose the SSS (Secure Store Service) option, you must then specify the application ID of a Secure Store target application. The specified target application serves as a lookup that is used to retrieve the appropriate set of credentials. Each target application can have permissions set so that only specific users or groups can use the stored credentials.

When provided with an application ID, Excel Services retrieves the credentials from the Secure Store database for the user who is accessing the workbook (either through the browser, or using Excel Web Services). Excel Services then uses those credentials to authenticate to the data source and retrieve data.

For information about how to use Secure Store with Excel Services, see Use Excel Services with Secure Store (SharePoint Server 2010).

None

When you select the None option, no credential retrieval occurs, and no special action is taken for authentication for the connection. Excel Services does not try to delegate credentials, and does not try to retrieve credentials that are stored for the user from the Secure Store database. Instead, Excel Services impersonates the unattended service account and passes the connection string to the data provider that handles authentication.

The connection string may specify a user name and password to connect to the data source or may specify that the Windows identity of the user or computer that is issuing the request be used to connect to the data source. In either case, the unattended account is impersonated first and then the data source connection is made. The connection string and the provider determine the authorization method. Additionally, authorization can be based on either the credentials found in the connection string or the impersonated unattended account's Windows identity. For more information, see Unattended account.

Excel Services security and external data

Excel Services manages workbooks and external data connections by using the following:

• Trusted file locations   Locations designated by an administrator from which Excel Services can load workbooks

• Trusted data connection libraries   SharePoint Server 2010 data connection libraries that have been explicitly trusted by an administrator from which Excel Services can load data connection files

• Trusted data providers   Data providers that have been explicitly trusted by an administrator

• Unattended service account   A low-privileged account that Excel Services can impersonate when it makes data connections

Trusted file locations

Excel Services only loads workbooks from trusted file locations. A trusted file location is a SharePoint Server location, network file share, or Web folder address that the administrator has explicitly enabled workbooks to be loaded from. These directories are added to a list that is internal to Excel Services. This list is known as the trusted file locations list.

Trusted locations can specify a set of restrictions for workbooks loaded from them. All workbooks loaded from a trusted location adhere to the settings for that trusted location. Here is a short list of the trusted location settings that affect external data:

• Allow External Data   Defines how external data can be accessed. The options for this include the following:

  • No data access allowed (default).

  • Only connection files in a trusted SharePoint Server 2010 data connection library are allowed.

  • Connections embedded in workbooks allowed in addition to connection files from a trusted data connection library.

• Warn on Refresh   Defines whether to show the query refresh warnings or not.

• Stop When Refresh on Open Fails   Defines whether to fail the workbook load if external data does not refresh when the workbook opens. This is used in scenarios where the workbook has cached data results that will change depending on the identity of the user viewing the workbook. The objective is to hide these cached results and make sure that any user who views the workbook can see only the data that is specific to that user. In this case, if the workbook is set to refresh on open and the refresh fails, the workbook is not displayed.

  Note

  This only works if the user does not have Open Items permissions on the workbook, because a user who can open the workbook directly in Excel can always see the cached data results. You can prevent the user from opening the workbook in Excel by making sure that the user only has Viewers permissions in the document library.

• External Data Cache Lifetime   Defines external data cache expiration times. Data is shared among many users on the server to improve scale and performance, and these cache lifetimes are adjustable. This accommodates scenarios in which query execution should be kept to a minimum because the query might take a long time to execute. In these scenarios, the data often changes only daily, weekly, or monthly instead of by the minute or every hour.

Trusted data connection libraries and managed connections

A data connection library is a SharePoint Server 2010 library that is designed to store connection files, which can then be referenced by Office 2010 applications, such as Excel and Microsoft Visio. Excel Services only loads connection files from trusted SharePoint Server 2010 data connection libraries. A trusted data connection library is a library that the server administrator has explicitly added to an internal trusted list. For information about how to trust a data connection library for use with Excel Services, see Manage Excel Services connections (SharePoint Server 2010).

Data connection libraries let you centrally manage, secure, store, and reuse data connections.

Reusing connections

Users can reuse connections that were created by other users and create different reports that use the same data source. You can have the IT department or a business intelligence expert create connections, and other users can reuse them without having to understand the details about data providers, server names, or authentication. The location of the data connection library can even be published to Office clients so that the data connections are displayed in Excel or in any other client application that uses the data connection library. For more information, see Manage Excel Services connections (SharePoint Server 2010).

Managing connections

Because workbooks contain a link to the file in a data connection library, if something about the connection changes (such as a server name or a Secure Store application ID), only a single connection file has to be updated instead of potentially many workbooks. The workbooks will obtain the connection changes automatically the next time that they use that connection file to refresh data from Excel or Excel Services.

Securing connections

The data connection library is a SharePoint library, and it supports all the permissions that SharePoint Server 2010 does, including per-folder and per-item permissions. The advantage that this provides on the server is that a data connection library can become a locked-down data connection store that is highly controlled. Many users may have read-only access to it. This enables them to use the data connections. But they can be prevented from adding new connections. By using access control lists (ACLs) with the data connection library, and letting only trusted authors upload connections, the data connection library becomes a store of trusted connections.

Excel Services can be configured to load connection files only from data connection libraries that are explicitly trusted by the server administrator, and to block loading of any embedded connections. In this configuration, Excel Services uses the data connection library to apply another layer of security around data connections.

Data connection libraries can even be used together with the new Viewer role in SharePoint Server 2010 that enables those connections to be used to refresh workbooks rendered in a browser by Excel Services. If the Viewer role is applied, users cannot access the connection file contents from a client application, such as Excel. Therefore, the connection file contents are protected but still can be used for workbooks refreshed on the server.

Trusted data providers

Excel Services only uses external data providers that are on the Excel Services trusted data providers list. This is a security mechanism that prevents the server from using providers that the administrator does not trust. For information about how to trust a data provider, see Manage Excel Services connections (SharePoint Server 2010).

Unattended service account

Excel Services runs under a highly privileged account. Because Excel Services has no control over the data provider and does not directly parse provider-specific connection strings, using this account for the purposes of data access would be a security risk. To lessen this risk, Excel Services uses an unattended service account. This is a low-privileged account that is impersonated by Excel Services if either of the following conditions are true:

• Any time that it is trying a connection where the None authentication option is selected.

• Whenever the SSS (Secure Store Service) option is selected and the stored credentials are not Windows credentials.

If the None option is selected and the unattended account does not have access to the data source, Excel Services impersonates the unattended service account and uses information that is stored in the connection string to connect to the data source.

If the None option is selected and the unattended account has access to the data source, a connection is successfully established using the credentials of the unattended service account. Use caution when you design solutions that intentionally use this account to connect to data. This is a single account that potentially can be used by every workbook on the server. It is possible for any user opening a workbook with an authentication setting of None using Excel Services to view that data by using the server. In some scenarios, this might be needed. However, Secure Store is the preferred solution for managing passwords on a per-user or per-group basis.

If the SSS (Secure Store Service) option is selected and the stored credentials are not Windows credentials, Excel Services impersonates the unattended service account and then attempts to connect to the data source by using the stored credentials.

If the Windows Authentication option is selected, or if the SSS option is selected and the stored credentials are Windows credentials, then the unattended service account is not used. Instead, Excel Services impersonates the Windows identity and attempts to connect to the data source.

See Also

Concepts

Excel Services administration (SharePoint Server 2010)
Use Excel Services with Secure Store (SharePoint Server 2010)