Excel Services provides you with the means to increase the overall level of security on workbook data and formulas without compromising ease of access and flexibility. Because Excel Services is built on top of SharePoint Server 2007, it offers all the same features for securing and managing workbooks as files, as well as a range of additional options that are specific to Excel Services. By allowing you to configure most users with read-only rights so that they can view only the version of the workbook rendered in the Web browser, you immediately reduce the risk profile by removing direct access to the workbooks themselves. This allows you, and the select users who author workbooks, to restrict the general visibility of proprietary equations and data that most users will not need to see.
Users who previously had to be given Read access to the physical workbook file on a file share might not need permissions to open the file at all. The ability to leverage server-side calculations means that most users will not need to run embedded queries and computations on their workstations, allowing you to deploy stricter client-side security policies.
When a user requests a workbook to view in the Web browser, or it is displayed within a Web Access Web Part, the user does not interact with the workbook directly. Instead, Excel Services loads the workbook on the user's behalf and performs all calculations and external data calls. Excel Services supports two modes for the service to access workbooks and back-end data sources: Impersonation, where Excel Services uses the Windows account of the user making the request; and Process Account, where Excel Services uses the identity account of the application pool associated with the Web application that the workbook is hosted in. This setting is controlled from the Excel Services Settings page available on the Shared Services Administration site.
The Process Account setting is the easiest to configure because it requires no additional steps beyond making sure that the Application Pool identity account has at least Read permissions on all files in all the Trusted File Locations it is configured for. It automatically has these permissions for workbooks hosted within document libraries, but it might not have permissions for workbooks in shared folders (UNC location) or other Web sites (HTTP location). Under this authentication model, a user can view data through Excel Web Access that she would not have permissions to view otherwise.
The Impersonation setting provides a higher level of security because Excel Services impersonates the account of the user and attempts to access the workbooks using that person's credentials. If the user does not have permissions to view the workbook in the folder or site it is on, Excel Services will not have permissions to load and render it. Using the Impersonation mode in a multiserver environment requires that Windows Kerberos authentication be used to allow the delegation of credentials between servers. The implementation of Kerberos requires that additional steps be taken to configure Active Directory accounts to support delegation.
|More Info|| |
For more information on configuring Kerberos authentication, see the Microsoft Knowledge Base article at http://support.microsoft.com/?id=832769.
Excel 2007 supports linking to external data by referencing an Office Data Connection (.odc) file stored in a data connection library on the server. When Excel Calculation Services loads and processes the file, it can refresh the data by using the ODC information to access the external data source. Within the configuration settings is a button for Excel Services Authentication Settings. The Excel Services Authentication Settings offers three options: Windows Integrated Authentication, SSO, and None as shown in Figure 20-8.
Figure 20-8: Office Data Connection-Excel Services Authentication Settings
|More Info|| |
For more details on configuring external data connections, see the "Accessing Data from Other Sources" section later in this chapter.
When Windows Integrated Authentication is selected, Excel Calculation Services attempts to execute the external data query by impersonating the credentials of the user accessing the workbook. This setting requires that the user have sufficient permissions on the data source for Excel Calculation Services to execute the query. If the external data source is on a server separate from the Excel Calculation Server, Kerberos delegation has to be configured so that the account credentials can be passed to the data server.
|Best Practices|| |
Implementing Kerberos delegation with Impersonation for communication between servers and Windows Integrated Authentication for communication with back-end data sources provides the highest level of security and is the recommended approach.
The SSO, or Single Sign-On, authentication option can be used for authentication against data servers that do not support Windows Authentication (for example, most UNIX and mainframe systems). In this case, Excel Calculation Services must submit a user name and password combination to the data server on behalf of the user for the request to be authenticated. To avoid storing the credentials permanently in the .odc file, and to avoid prompting the user for the credentials each time the data is refreshed, Excel Calculation Services can query an encrypted database where the credentials are stored. The SSO authentication method supports storing either a single set of credentials to be used by any user accessing the workbook (Group mapping) or separate credentials unique to each user (Individual mapping). In both cases, the credentials are stored in an encrypted format in a separate SSO database that is accessed by the Microsoft Single Sign-On Service running on the Web front-end and application servers. The credentials are all associated with an application identity (App ID), which identifies the external data server that is being queried. Individual mappings allow for more granular control over permissions and auditing, but Group mappings are easier to maintain and provide better performance.
The third Excel Services Authentication option of None specifies to Excel Calculation Services that the credentials used for the external data connection are either embedded in the .odc file itself or make use of a single set of default credentials configured on the server. Using this authentication method, you can configure the .odc file with a provider-specific connection string either by editing it directly on the Definition tab in the Connection properties in Excel or by opening the .odc file and editing it directly. If you choose to store the password in the connection string, Excel Calculation Services passes it along with the rest of the string to the data server for validation.
|Security Alert|| |
The password will not be encrypted when it is stored in the .odc file, and if the file is stored in a Data Connection Library on the server, it could potentially be read by other users.
If the data provider supports Windows Integrated Authentication, the connection string can indicate that this mode be used for authentication, in which case no username and password are stored in the connection string. This authentication model also requires that you configure the Unattended Service Account setting found on the Excel Services Settings page available on the Shared Services Administration site. If the connection string specifies Integrated Windows Authentication, Excel Calculation Services attempts to authenticate using the Unattended Service Account. In this case, the account should be configured as a Windows domain account with permissions to access the data source. Otherwise, the account can be configured as either a domain account or a local account that is given network access to connect to the external data server. If a local account is used, it must be configured the same on every Excel Calculation Services application server.
The level of access that a user has to a workbook stored in Excel Services is determined by the permissions given to the user in the file storage location: the SharePoint Server 2007 document library, the Windows file share, or the Web site. In the case of the document library, the permissions are those assigned to the user within SharePoint 2007. In the case of the file share or Web site, access is controlled by share permissions and NTFS rights.
You might want to do the following:
Limit some users to having read-only access to a workbook but still give them the ability to open it and view the formulas inside it.
Give other users more restricted access to prevent them from viewing the formulas behind the cells.
If a user has the standard Read permissions on a site, the user can open and view the entire workbook, although she won't necessarily be able to save any changes. To restrict a user further, remove the Open Items permission and leave her with only the View Items permission. With only the View Items permission, the user can view the workbook in the browser and in the Web Access Web Part but can open it only as a snapshot in Excel. Setting this permission in the SharePoint document library will affect users' rights both through Excel Web Access and Excel Web Services, but it will not affect a user accessing a workbook referenced in a shared folder location or a URL.