Share via

Plan Excel Services authentication (SharePoint Server 2010)

  • Article

 

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

Excel Services in Microsoft SharePoint Server 2010 gives you a significant level of precise control for the processing and displaying of Excel workbooks. You can control how workbooks are opened on the server and the specific capabilities that are enabled for each workbook. This article describes the security and authentication settings for Excel Services and related components that you must consider when planning a deployment. This article also contains prescriptive guidance for using Excel Services to help secure and manage access to workbooks on the server.

In this article:

  • About Excel Services security

  • Plan user authentication

  • Plan communication among servers

  • Plan external data authentication

About Excel Services security

The security model for Excel Services is based on the concept that to make sure of data integrity and quality, an administrator must be able to centrally manage shared resources and user access to corporate intellectual property contained in workbooks. To do this you can use Excel Services to specify:

  • Trusted file locations   These are SharePoint document libraries, UNC paths, or HTTP Web sites that have to be explicitly trusted before Excel Services can access them. Excel Services opens workbooks that are stored in trusted file locations only.

  • Trusted data providers   These are data providers that Excel Services is explicitly configured to trust when it is processing data connections in workbooks. Excel Services attempts to process a data connection only if the connection uses a trusted data provider.

  • Trusted data connection libraries   These are SharePoint document libraries that contain Office data connection (.odc) files. The .odc files are used to centrally manage connections to external data sources. Instead of allowing embedded connections to external data sources, Excel Services can be configured to require that you use .odc files for all data connections. The .odc files are stored in data connection libraries, and the data connection libraries have to be explicitly trusted before Excel Services will allow workbooks to access them.

    By default, cross-domain workbook and data connection access is not enabled. To allow workbooks in trusted file locations (and data connections in trusted data connection libraries) to be accessed across domains by Web Parts, Web pages, or Web services, run the Windows PowerShell cmdlets, as shown in the examples in Manage Excel Services with Windows PowerShell (SharePoint Server 2010).

    The requesting Web pages and the workbooks or data connections must live in the same farm.

    Note

    When you open a workbook in Excel Services, a temporary file is stored in the %TEMP% folder of the application server that is running Excel Services.

Plan user authentication

We recommend that you store Excel workbooks you will use with Excel Calculation Services in SharePoint Server 2010 document libraries, because SharePoint Foundation 2010 maintains an access control list (ACL) for these files. Excel Services can also open workbooks from UNC paths and HTTP Web sites.

Authentication for user access to a SharePoint site is performed by SharePoint Server 2010. By default, SharePoint Server uses Integrated Windows authentication.

In addition to the listed authentication methods, Excel Services also supports generic forms-based authentication. However, configuring SharePoint Server to use generic forms-based authentication is not discussed here.

Communication among servers

Claims-based authentication is the authentication mechanism used by SharePoint Server 2010 within the farm. It is a Microsoft and industry standard with broad support. Claims authentication helps improve security and authentication when you deploy farms, Office Business Applications, and SharePoint services in different environments. Excel Services uses claims-based authentication for all deployment scenarios, whether in a single server installation or in a farm environment. Additionally, the authentication and authorization of users to all content and resources within SharePoint Server 2010 is much more secure with claims-based authentication.

Plan external data authentication

Excel Services supports three data authentication options: Windows Authentication, SSS (Secure Store Service), and None. The data authentication setting is configured as part of the data connection properties in an Excel workbook or in an external Office Data Connection (ODC) file.

Integrated Windows authentication

If you choose the Windows Authentication option, Excel Services will attempt 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 Integrated 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 authentication

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.

Excel Services supports both individual and group mappings. With individual mappings, a single user is mapped to a single set of credentials stored in Secure Store. With group mappings, a group of users is mapped to a single set of credentials stored in Secure Store.

For more information about how to use Excel Services with Secure Store, 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 it 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 it 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 service account.

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.

You can configure the unattended service account either as a domain account or as a local computer account. If the unattended service account is configured as a local computer account, make sure that the configuration is identical on every application server that runs Excel Calculation Services.

The credentials for the unattended service account are cached on the connection and on each workbook session. Each time that a workbook is loaded that has a data connection that uses the unattended service account and if the credentials are not already cached for that connection, the unattended service account is obtained from Secure Store and used. In other words, the unattended service account credentials are not cached globally but are instead taken from Secure Store as needed for each session or data connection.

Restrict the permissions of the unattended service account to enable only logging on to the network. Verify that the unattended service account does not have access to any SharePoint Server 2010 databases.

Security settings

To configure administrative settings for Excel Services, including security settings, open the SharePoint Central Administration Web site and access the Excel Services Global Settings page. See Excel Services administration (SharePoint Server 2010) for more information.

The Excel Services Global Settings page provides configuration settings for the following security options:

  • File Access Method

  • Connection Encryption

File access method

On the Excel Services Global Settings page, in the Security section, under File Access Method, select either Impersonation or Process account.

  • Impersonation   This enables a thread to run in a security context other than the context of the process that owns the thread. Select Impersonation to require Excel Calculation Services to authorize users when they try to access workbooks that are stored in UNC and HTTP locations. Selecting this does not affect workbooks that are stored in SharePoint Server 2010 databases. In most server farm deployments in which front-end web servers and Excel Calculation Services application servers run on different computers, impersonation will require constrained Kerberos delegation.

  • Process account   If Excel Calculation Services application servers are opening workbooks from UNC shares or HTTP Web sites, the user account will not be impersonated, and the process account will be used.

Connection encryption

You can use Internet Protocol Security (IPsec) or Secure Sockets Layer (SSL) to encrypt data transmission among Excel Calculation Services application servers, data sources, client computers, and front-end web servers. To require encrypted data transmission between client computers and front-end web servers, click the Connection Encryption setting Required. Not required is the default setting. If you change the Connection Encryption setting to Required, the Excel Calculation Services application server will only enable data transmission between client computers and front-end web servers over SSL connections.

If you decide to require encrypted data transmission, you will have to manually configure IPsec or SSL. You can require encrypted connections between client computers and front-end web servers while enabling connections that are not encrypted between front-end web servers and Excel Calculation Services application servers.

Trusted file locations

Trusted file locations are SharePoint sites, UNC paths, or HTTP Web sites from which Excel Services is permitted to access workbooks.

In the Location section of the Excel Services Add Trusted File Location page, you can configure the address, the location type, and whether child libraries of trusted file locations are also trusted. By selecting Trust Children you can improve manageability. However, you can also create a potential security issue by enabling subsites and subdirectories of trusted locations to be automatically trusted as soon as they are created.

In the Session Management section, you can configure settings to help conserve resource availability and improve Excel Services performance and security. Performance can decrease when many users have multiple Excel Services sessions open at the same time. You can control resource consumption and limit the duration of open Excel Services sessions by configuring two time-out settings for open sessions.

The Session Timeout setting determines the time that an Excel Services session can remain open and inactive after each user interaction. The Short Session Timeout setting determines how long an Excel Services session can remain open and inactive after the initial session request. The New Workbook Session Timeout setting determines how long an Excel Services session for a new workbook can remain open and inactive before it is shut down. You can also control the number of seconds allowed for any single session request by configuring a Maximum Request Duration value. By limiting how long sessions remain open, you can help reduce the risk of denial-of-service attacks.

In the Workbook Properties section, you can configure a maximum size of any workbook, chart or image that is permitted to be opened in an Excel Services session. Performance and resource availability can be compromised when users open extremely large workbooks. Unless you control the allowable size of workbooks running in open Excel Services sessions, you risk users exceeding your resource capacity and causing the server to fail.

Note

If an application server that runs Excel Calculation Services fails or is shut down, all open sessions on the server are lost. In a stand-alone installation, Excel Services will no longer be available. This means that workbooks cannot be loaded, recalculated, refreshed, or retrieved by . In a server farm deployment that includes multiple application servers that run Excel Calculation Services, shutting down one server does not affect open sessions that are running on other servers. Users with sessions running on a server that is shut down are prompted to reopen their workbooks. When users start a new session, they are automatically routed to active application servers that are running Excel Calculation Services.

In the External Data section, you can determine whether workbooks stored in trusted file locations and opened in Excel Services sessions can access an external data source. You can designate whether Allow External Data is set to None, Trusted data connection libraries only, or Trusted data connection libraries and embedded. If you select either Trusted data connection libraries only or Trusted data connection libraries and embedded, the workbooks stored in the trusted file locations can access external data sources.

External data connections can be accessed only when they are embedded in or linked from a workbook. Excel Services checks the list of trusted file locations before it opens a workbook. If you select None, Excel Services will block any attempt to access an external data source. If you manage data connections for many workbook authors, consider specifying Trusted data connection libraries only. This ensures that all data connections in all of the workbooks generated by authenticated workbook authors have to use a trusted data connection library to access any external data sources.

If you manage data connections for only a few workbook authors, consider specifying Trusted data connection libraries and embedded. This enables workbook authors to embed direct connections to external data sources in their workbooks, but still have access to trusted data connection libraries if the embedded links fail.

In the Warn on Refresh area of the External Data section, you can specify whether a warning is displayed before a workbook updates from an external data source. By selecting Refresh warning enabled, you ensure that external data is not automatically refreshed without user interaction.

In the Display Granular External Data Errors option, if you enable the Granular External Data Errors setting it provides descriptive error messages to display that provide helpful information for troubleshooting and fixing connection problems.

In the Stop When Refresh on Open Fails area, you can specify if Excel Services stops opening a workbook if the workbook contains a Refresh on Open data connection that fails. By selecting Stopping open enabled, you ensure that cached values are not displayed if an update operation fails when the workbook is opened by any user having View Only permissions to the workbook. When Refresh on Open is successful, cached values are purged. By clearing the Stopping open enabled check box, you risk displaying cached values if Refresh on Open fails.

In the External Data Cache Lifetime area of the External Data section, you can specify the maximum time that cached values can be used before they expire, and the maximum number of external data queries that can execute at the same time in a single session.

To make sure that only trusted users have access to workbooks stored in trusted locations, it is important to enforce ACLs on all trusted file locations.

There are three core scenarios to deploy Excel Services: enterprise, small department, and custom.

In an enterprise deployment, consider the following guidelines:

  • Do not configure support for user-defined functions.

  • Do not enable workbooks to use embedded data connections to directly access external data sources.

  • Limit the use of data connection libraries for external data source access from workbooks.

  • Restrict the size of workbooks that can be opened in Excel Services.

  • Selectively trust specific file locations and do not enable Trust Children for trusted sites and directories.

In a small department deployment, consider the following guidelines:

  • Enable trust for all file locations that are used by department members to store workbooks.

  • Enable Trust Children for all trusted sites and directories.

  • Selectively restrict access to specific file locations if problems occur.

In a custom deployment, consider the following guidelines:

  • Enable Excel Services to open large workbooks.

  • Configure long session time-out settings.

  • Configure large data caches.

  • Create a single trusted location for this deployment.

  • Do not enable Trust Children for this trusted location.

Trusted data providers

You can control access to external data by explicitly defining the data providers that are trusted and adding them to the list of trusted data providers. The list of trusted data providers designates specific external data providers to which workbooks opened in Excel Services are permitted to connect.

Before instantiating a data provider to enable a workbook to connect to an external data source, Excel Services checks the connection information to determine whether the provider appears on the list of trusted data providers. If the provider is listed, a connection is tried; otherwise, the connection request is ignored.

Trusted data connection libraries

A trusted data connection library is a data connection library from which you have determined that it is safe to access .odc files. Data connection libraries are used to help secure and manage data connections for workbooks that are accessed by Excel Services. You can designate a data connection library as trusted by adding it to the Excel Services trusted data connection libraries list.

If a data connection is linked from a workbook that is accessed by Excel Services, the server checks the list of trusted data connection libraries. If the data connection library is listed, a connection is tried by using the .odc file from the data connection library; otherwise, the connection request is ignored.

View Only permissions

You can specify users who are only permitted to view workbooks by adding them to the SharePoint Server 2010 Viewers group or by creating a new group configured to use View Only permissions. By default, the Viewers group is configured to use View Only permissions. Users added to a group configured to use View Only permissions can view, open, interact with, refresh, and recalculate workbooks. But they are prevented from accessing the source file in any way, other than by using Excel Services. This helps you protect your proprietary information.

Workbooks and workbook data objects configured to use View Only permissions cannot be opened in Microsoft Excel 2010. However, a snapshot of the workbook, displaying only values and formatting of the server-viewable ranges, can be rendered in Excel 2010.

You can configure site settings in SharePoint Server 2010 to control access to workbook data by setting View Only permissions on centrally managed workbooks that are rendered in a web browser. You can also configure site settings in SharePoint Server 2010 to enable workbooks to refresh external data on the server, and to help secure and manage external data connections. See Excel Services administration (SharePoint Server 2010) for more information about how to save specified data objects as View Only items.

External data connections

The Excel Calculation Services component of Excel Services is used to connect to external data sources. Excel Calculation Services processes external data connection information that contains everything the server must have in order to connect to a data source. This includes how to authenticate, which connection string to use, which query string to use, and where and how to collect credentials to use for the connection. These connections can be defined in two locations: embedded within workbooks and in .odc files. The connection information is identical in both locations. The .odc files are small files that contain connection information in plain text. Each .odc file can be used by multiple workbooks.

You use Excel 2010 to author and edit .odc files and connections embedded in workbooks. In the Excel 2010 client, you can run the Data Connection Wizard or configure the settings in the Connections properties page. You can also export an .odc file that is based on these settings. The Connections properties page shows connection information, including Excel Services authentication settings.

.odc files

Workbooks can contain both links to an .odc file and embedded connection information. This enables workbooks to retrieve the .odc file, read the contents, and attempt to connect to an external data source if the embedded connection information fails.

You can also configure Excel Services to use connection information from the .odc file exclusively instead of first trying to connect by using the embedded information. This approach enables administrators to deploy a set of managed .odc files that provide connection information to many workbooks.

Managing .odc files

Data connection libraries provide a repository for collections of .odc files. Administrators can manage data connections on the server by creating a trusted data connection library and .odc files that require workbooks to always use a connection file.

If data source information changes (for example, the server name), you only have to update one .odc file in the data connection library and all of the workbooks that consume the .odc file will be automatically updated the next time that they refresh. You can also use View Only permissions to restrict access to .odc files.

User-defined function assemblies

If your deployment scenarios include workbooks that contain user-defined functions to extend the capabilities of Excel Calculation Services, you must configure Excel Services to support user-defined functions.

To configure this support, you must enable user-defined functions on trusted file locations that contain workbooks that require access to user-defined functions. In addition, you must register user-defined function assemblies on the Excel Services user-defined function assembly list. See Excel Services administration (SharePoint Server 2010) for more information about how to enable user-defined functions.

See Also

Other Resources

Resource Center: Security and Authentication for SharePoint Server 2010 (https://go.microsoft.com/fwlink/p/?LinkId=220216)