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.


PerformancePoint Security Best Practices In SharePoint 2010 Authentication, Trusted Locations

Data content libraries that are trusted with SharePoint Server 2010 use document libraries that contain the PerformancePoint Services data connections. The .PPSDC files are used to manage connections for data sources. This includes Excel Services spreadsheets, OLAP Cubes, Relational databases, and SQL Server databases. The data sources in the Dashboard Designer are defined and stored in a trusted data connection library that SharePoint Server 2010 offers. The trusted data connection library consists of safe documents that belong to that particular library. Users are restricted when it comes to how data source files can be used. They can be read but not modified or deleted. Through PerformancePoint Services a document library is created through a default setting. Administrators do have the ability to manage those data connections on the server. This is accomplished through creating additional data connection libraries. When a user updates data source connections in a document library the information will be shared and updated through Dashboard Designer. There are many trusted lists that can be developed in a trusted SharePoint Server 2010 list. The list or the parent of a list allows the site collection to be trusted during the initial configuration. It can also be done later on through the Central Administrator. These lists include:

  • Filters
  • KPI’s
  • Reports
  • Scorecards

With PerformancePoint Services, the security setting for data sources gets stored in each of them. There is a setting that is used to determine if the server is connected with an unattended user account, a customized unattended user account, or an authenticated user. With the SharePoint Server 2010 Secure Store Service (SSS), the ability to securely store data is available. This includes the credentials that are associated with a specific identity or group. The Secure Store Service is available for all of the farms on SharePoint Server 2010. Each of the data sources is configured for a given user to work with the authenticated user credentials. This is referred to as the Unattended Service Account. This is a domain set of credentials that are duplication when a user is connecting to a data source.

The Unattended Service Account is used to manage the data source for the queries. This is done to prevent the PerformancePoint Services from accessing the content database when the query is being executed. With PerformancePoint Services, data is stored and retrieved through Unattended Service Account credentials. This takes place in the Secure Store Service verifications. The server has to keep both the user name and the password of a user so that they can access it. The user name is stored in the PerformancePoint Services and the password is stored in the Secure Store Service. It is important when you create an Unattended Service Account that you make sure it has the right access. There are data sources that that to be in place for it to function properly. Unattended Service Account credentials aren’t cached on a global scale. Instead, they get retrieved from the Secure Store Service as they are needed. When you open a WorkSpace file in Dashboard Designer, the credentials will be cached for the connection. The Unattended Service Account password is retrieved from the Secure Store Service so that it can be the target data source.

With claims based authentication taking place in the SharePoint Server 2010, there is support for many providers. It is used to communicate from the application servers and the front end web servers. PerformancePoint Services allows for the authentication of providers at the same time on one web application. This is limited though to when the Dashboard is used through a web browser. The Dashboard Designer relies on the web application to be extended. It is then configured in order to support the Windows authentication provider.