Excel Services Security Best Practices Trusted Data Providers And Data Connections

It is possible to have control over access for external data when you define the data providers that you trust and that you record them in your list of trusted data providers. This list of trusted data providers determines the external data providers that you will allow to connect to workbooks that are opened up in Excel Calculation Services. Before a data provider is enabled to connect to an external data source to access workbooks, a check is done. This is to ensure if the provider is on that list of trusted data providers. If the provider is found then the connection is attempted. Otherwise that request will be denied.

With a trusted data connection library, documents are safe for users to access through .odc files. These libraries are in place to help with securing and managing the data connections for the workbooks that will be accessible on a given server that is using Excel Calculation Services. There is a list of trusted data connection libraries that are designated for specific workbooks that will be accessed. Should the date connection be linked from a workbook that is accessible with a server running Excel Calculation Services then the server will check the connection information and compare the list of trusted data connection libraries in place. As long as it is listed then the connection will be attempted with the .odc file. If not that it will be terminated.

It is possible to choose users that only have permission to view workbooks by adding them to the SharePoint Server 2010 viewers group. Another option is to create a new group which can be done when you configure View Only permissions. When users are added to such a group they will be able to open, view, interact, refresh, and recalculate workbooks. However, such users won’t be able to access the file source unless they are using Excel Services Applications. This is a type of security measure that help you to protect information. The source data will never be displayed to those users. The workbooks and workbook data objectives that are configured for View Only permissions won’t be able to be opened with Microsoft Excel 2010.The ability to configure site settings for SharePoint Server 2010 in order to control user access to workbook data through View Only permissions is a method of centrally managing those workbooks with a web browser. It is also possible to configure the settings in SharePoint Server 2010 that allows these workbooks to be refreshed as external data on a server. This process makes it easier and more secure to manage external data connections.

With Excel Calculation Services, the Excel Services Application will use the connection for an external data source. This contains everything that a server has in order to successfully connect to a data source. Elements included in this are:

  • How to authenticate
  • Which connection string to use
  • Which query string to use
  • How to collect credentials for the connection

The connections can be found either as embedded workbooks or in .odc files. The connection information is the same for both of these locations. The .odc files are smaller and they offer plain text which can be formatted to be used again. The Excel 2010 client for author and edit of the .odc files and connections can be embedded in workbooks. The Data Connection Wizard can be used to configure the settings in the Connections properties page. It is possible to export an .odc file with these settings. The Connections properties page allows for the connection information and authentication properties to be determined in Excel Services Application.

There are often links in workbooks for .odc files and then embedded into connection information. This allows the workbooks to be able to retrieve the .odc file, read information, and to connect to an external data source should the embedded connection information experience a failure. The .odc files has to be managed so that they always contain information that is current and accurate. It is possible to configure Excel Calculation Services for connecting information from the .odc file. This is done before connecting through the use of embedded information. This approach allows administrators the opportunity to deploy a set of managed .odc files that are small in size. They will be used to update connection information for various workbooks. The authors of these workbooks are able to decide which connection information that the workbook is able to use. This can be accomplished by going to open Excel 2010 and then clicking on Workbook Connections on the Data tab. A connection can be added to the workbook through Workbook Connections and then viewed through the properties of the connection that has been added. The Definition tab allows you several options, and you will select Always Use a Connection File. This is a setting that allows the workbook to retrieve a connection file from the data connection library. It can be used to connect information from a file so that it can be connected to an external source.

The presence of the data connection libraries offer a place for collections of .odc files. Administration has the ability to manage these data connections on a server. This is accomplished by creating a data connection library and .odc files that are required for workbooks to connect to a file. Workbooks can use connections directly from a data connection library. They will get updated information for a connection before they are linked with a connection to a data source again. Should the data source change then there is only one update to a .odc file for all of the workbooks connected to it to be updated when the next refresh occurs. There is also the chance to use the View Only permissions for restricting access to .odc files.

There are options for deployment to take place with workbooks that have user defined functions associated with them. Such customization allows for the ability of the Excel Calculation Services to be extended. The application has to be configured in order for such user defined functions to be applied. This type of support is configured by enabling the user defined functions located on the trusted file locations. They contain workbooks that allow access to these user defined functions. It is necessary to register user defined function assemblies.


Excel Services Security Best Practices – Trusted File Locations

There are several trusted file locations that can be leveraged. They include UNC paths, HTTP websites, and SharePoint sites. These are all locations where the use of Excel Calculation Services are permitted to access workbooks. The location section of the Excel Services Add Trusted File Location Page is where you can configure information. This includes the location type, the address, and if there are child libraries of trusted file locations that can be trusted as well. Should you select trust children you will find that you have more control over management.

However, it is also important to point out that you can create a security issue if you have enabled subdirectories and sub sites to be trusted as soon as you create them. The Session Management section allows you to conduct configuration for settings so you can conserve your available resources. By doing so you will improve the performance and the security of Excel Calculation Services. If you have multiple users with various sessions of Excel Calculation Services open at the same time then performance will decrease. The best method for limiting this issue is to configure time out settings for sessions that are open and idle.

You can go to the Session Timeout settings to determine what intervals you wish to apply for the sessions to remain inactive before they are closed. There is the Short Session Timeout setting and the New Workbook Session Timeout. You can put information into the Maximum Request duration too. The values you place in any of these areas will help to control risk of denial of service for users. The Workbook Properties section allows you to be able to successfully configure the maximum size for workbooks, charts, or images that are opened through any Excel Calculation Services session. You want to apply such settings as performance and security can be compromised if such entities are too large. Should an application server that runs Excel Calculation Services fail or be shut down all of the open sessions on that server can be lost. If it is a standalone installation then the Excel Services Application can’t be accessed. That also means the workbooks can’t be accessed.

The External Data section allows you to determine if the workbooks will be stored in trusted file locations and then opened up in Excel Calculation Services and if they can access an external source of data. You can also decide if you want to set Allow External Data to none, trusted data connection libraries only, or trusted data connections libraries and embedded. With external data connections, they can only be accessed if they are linked from a workbook or they are embedded. Excel Calculation Services will check the list of trusted file locations before any workbook is opened. Should you select none, then the Excel Calculation Services will block attempts to access any external data source. If you want to manage data connections for several different authors then you should consider using trusted data connection libraries online. This will make it possible for all of the data connects in those workbooks to be generated by the workbook authors. They will have a trusted data connection library in place before they are able to use external data sources for access.

If you only have a few authors with workbooks then you should consider trusted data connection libraries and embedded. This will allow the authors of the workbooks to have direct connections to external data sources in their workbooks. They will have access to trusted data connection libraries even if the embedded links fail. The Warn on Refresh area of the External Data section there is the ability to decide if you want a warning to be on display before a workbook will refresh from an external data source. When you select Refresh Warning Enabled you will be able to have external data that doesn’t get refreshed automatically. Enabling the Display Granular External Data Errors gives you the option to have descriptive error messages on display. They can offer you information should you have connection problems that need fixed. This can help you with the troubleshooting aspect of the operations. You can use the Stop when Refresh on Open Fails if you want Excel Calculation Services to stop a workbook from opening up. The workbook will contain a connection that fails with Refresh on Open Data. When you select Stopping Open Enabled you will be able to have values that aren’t displayed when they are cached. Refresh on Open can be a success and if that is the case the values cached are purged. You can clear the Stop Open Enabled check box but you will risk the values in cache being displayed if Refresh on Open fails. The External Data Cache Lifetime is found in the External Data section. You have the opportunity to determine the maximum amount of time that the cached values will be available before they are considered expired.

You want to make sure you only have trusted users accessing the workbooks that are stored in the trusted locations. In order to accomplish this, make sure you enforce ACLS for all of your trusted file locations.
There are three scenarios you may consider when it comes to the deployment of the Excel Services Application with SharePoint Server 2010. They include:

  • Custom
  • Enterprise
  • Small department

There are several guidelines that you need to take into consideration with enterprise deployment. They include:

  • Never configure support for user defined functions.
  • Never allow workbooks to use data embedded data connections in order to have direct access to external data sources.
  • Always limit the use of data connection libraries for any external data source access that is from workbooks.
  • Always restrict the size of the workbooks that are allowed to open in Excel Calculation Services.
  • Be selective with the trust specific file locations.
  • Never enable Trust Children for trusted sites and directories.

With a small organization you want to consider the following guidelines in regards to deployment. Always enable trust for all file location that used by any users in the department for storing workbooks. Always enable Trust Children for your trusted directories and sites. Be selective when it comes to the access users have to specific file locations if you are experiencing problems.

With a custom deployment in place there are guidelines to consider. Configure log session time outs in the settings.

  • Enable Excel Calculation Services to open workbooks that are large in size.
  • Create a single trusted location for your deployment.
  • Don’t enable Trust Children for this specific trusted location.
  • Configure large data caches.

Excel Services Security Best Practices Common Security Settings

The ability to configure the administrative settings for Excel Services Application can be found by opening the SharePoint Central Administration Web Application. Then the Excel Services Settings page needs to be accessed.

It is important for the Excel Services Settings to be configured for several things. External data controls the external data connections for Excel Calculation Services. Load Balancing allows Excel Services Application sessions will be spread out across the Excel Calculation Services. Memory Utilization is the memory allocated for Excel Calculation Services. Security is where communication and web service settings are determined. The Excel Services Application is also authenticated here. Session Management maintains the sessions of behavior for the Excel Calculation Services. Workbook Cache are the settings for caching of the workbook files in memory and on disk. The use of Excel Service Settings Page can help you to configure options for a file. This access method also enables encryption for connections and methods. All of these scenarios directly affect the security of any deployment.

With impersonation you have the ability for a thread to run in the secure context. This is a good idea when you want Excel Calculation Services to authorize users to access any workbooks that have been stored in HTTP or UNC locations. This has no bearing on any workbooks that have been stored in SharePoint Server 2010 databases. Most of the server farms deploy front end web servers and Excel Calculation Services applications that run on various computers. With impersonation Kerberos delegate is restrained. When you have workbooks to open, Excel Calculation Services serves can allow that to happen from HTTP or UNC sites. However, the process account has to be used because the user account won’t be able to be impersonated.

The use of SSL for encryption for the data that will be transmitted is very important when you rely on Excel Calculation Services, data sources, client computers, or front end web servers. In order to encrypt the data while it is being transmitted, click on Connection Encryption settings and make sure it says required. If it says not required which is the default setting your data won’t be as secure as it needs to be. The Excel Calculation Services will only allow data that has been transferred between client computers and front end web servers to be done through SSL. If you don’t require encryption then you will have to configure the SSL manually. This will allow you to have encryption for the connections that occur between client computers and front end computers. However, you can have connections from front end servers and Excel Calculation Service applications that aren’t encrypted.