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 – Overview Of Excel Services Security

The when designing security strategies for Excel Services it involves learning about holistic SharePoint security, user authentication, communication for servers, and the authentication of external data. All of these areas must be covered before implementation so you can make well informed decisions. There is a great deal more to consider with Excel Services than just security however. When it comes to deploying SharePoint 2010 and its associated features, you need to take many different considerations and evaluate them. One of the many elements that can be of benefit to you is the Excel Services Application. It is one of the platforms that is part of SharePoint 2010. The function of Excel Services works with SharePoint Server 2010 to offer security, control, and management for the various Excel workbooks that are part of that enterprise. This type of application is a server that allows for performance and security to be key issues addressed. It can be deployed within workbooks or work with them so that the various components such as PivotTable reports and charts can be used for dashboards within any organization. This allows a user to take advantage of calculations that are associated with a server side Excel spreadsheet.

From there custom applications can be determined. Users have the option of locking workbooks so that they can have more security over their private data and property. This makes it possible for the data in a workbook to be protected at a higher level while they are on a server. It also allows for the data to be refreshed and recalculated through the Excel Services Application.

There is no denying that security is very important for any such component. There are several elements that you need to consider when it comes to the planning for your environment. You want it be one that has high security for the workbooks that will be placed on a server. When designing the Excel service security a plan should be developed to manage the security of the workbooks and the security of server that they are placed on. With the help of Excel Services Application you will be able to have complete control over the process and how everything is displayed for the Excel workbooks. You get to control how the workbooks will be opened on the server. You get to control who will be authorized to open them and what elements that they get access to from those workbooks. Understanding the security and the authentication settings you can choose from with Excel Services Application is very important.

You will need to consider all of this information before you move forward with deployment. The guidance offered here in these materials will ensure you get the most benefit from Excel Services Applications. At the same time it will ensure that your workbooks are very secure on the server.The security model is based on the concept of ensuring the data is in quality form, that the administrator is able to centrally manage the resources being shared, and that the intellectual property of the workbooks is maintained. In order for this to happen you will need to specify something things in Excel Services. Trusted data connection libraries are SharePoint document libraries with .odc files. Those files are used to offer a centrally managed connection with external data sources. Rather than allowing for the connections to be embedded, they can be configured through Excel Calculation Services for all data connections with those .odc files. The .odc files are stored and the connection must have trust before the workbooks can be accessed through Excel Calculation Services. Trusted data providers are the external database where the Excel Calculation Services can be configured. They have to be able to trust so that the data being processed is able to successfully connect to the workbooks. The connection will only be attempted by Excel Calculation Services when there is a trusted data provider in place. Trusted file locations are document libraries in SharePoint are the trusted file locations. They have to be trusted before the Excel Calculation Services will be able to access them. It is important to understand that Excel Calculation Services is only able to open workbooks that are stored in trusted files.

The default setting for a cross domain workbook and data connection isn’t allowed to occur. If you want to have the workbooks in trusted file locations that can be accessed across the web part domains you have to run the Windows PowerShell. The web pages that are requested and the workbooks have to be on the same farm. When you open up a workbook in Excel Calculation Services, there will be a temporary file stored in the %TEMP% folder. This will be located on the application server where the Excel Calculation Services is running.


Secure Store Service Best Practices In SharePoint 2010

With Microsoft SharePoint Server 2010 the legacy single sign on feature has been replaced. The Secure Store Service (SSS) has been introduced to offer a claims authorization service. This includes a database that is secure for the use of storing credentials associated with any given application identification.

The application identification can be used to authorize access to external data sources. As you learn about the Secure Store Service, how to prepare it, ID’s, mapping, and claims authentication you will quickly realize what a valuable access it happens to be.

 The Secure Store Service is a type of service that allows for authorization to be conducted on the application server in the SharePoint server farm. This provides a database that is used for credentials to be securely stored though the use of password and identity verification of the user. With SharePoint Server 2010 there is the use of the Secure Store Database. It is used to store and to retrieve credentials for accessing external data sources. The Secure Store Service also provides support for the storage of credentials to multiple back end systems. They can have multiple application ID’s too.

 There are some very important issues that you need to take into consideration when you are preparing for the Secure Storage Service to be implemented. You need to run the Secure Store Service in an application that isn’t being used for any other services, this is both a logical and technical restraint. You need to create the Secure Store Service database on an application that is running SQL server. You don’t want to use the same SQL server application though that is being used for your content database. Prior to generating your new key for encrypting, you need to back up the Secure Store Service database. It is recommended that you do so right after it is created too. Each time you create a new key, you want those credentials to be encrypted again with it. You never want the key refresh to fail as this can result in the credentials failing to allow you to have access. Never store the backup media to the encrypted key in the same location as the backup for the Secure Store Service database. This is an additional layer of protection that can prevent your database information from being compromised by an unauthorized user.

 There are application ID’s for each of the Secure Storage Service entries. They are used to retrieve a given set of credentials from the Secure Store Database. Each of the application ID’s can be set up with given permissions that have to be applied. This will restrict the users or groups that are able to successfully access those credentials stored within the application ID. The application can be used to retrieve a given data source. These application ID’s are also used to map out users within given sets of credentials. It can be set up for mapping to occur both for individuals and for groups. With individual mapping each user has their own set of credentials that are different from others. If there is a group then each user that belongs to that group gets mapped with the same credentials.

 There are individual mappings and group mapping to consider. The Secure Store Service supports both of them and maintains credentials for the application ID’s of the resources that are stored in the Secure Store database. With individual credentials of an application, they are retrieved from the application ID. This type of individual mapping is beneficial when a user logs in using information to personally identify themselves. With group mapping there is a layer of security in place that will check the credentials of the group. It will look for multiple domain users and compare them to a given set of credentials that are in place to identify a application ID which is stored in the Secure Store database. It is easier to maintain group mapping versus individual mappings so keep that in mind if you are after optimal performance.

Claims authentication can occur within Secure Store Service. It is able to accept security tokens and to decipher the encrypted application ID. From there it is able to look up the information for verification of authentication. With SharePoint Server Security Token Service, a token is created in response to a request for authentication. The Secure Store Service deciphers the token so that it can successfully read the value of the application ID. The Secure Store Service uses that application ID in order to successfully retrieve the credentials that are in the Secure Store database. These credentials will be used to authorize access to the various resources offered.