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.