Manage Excel Services trusted locations (SharePoint Server 2010)

  • Article

 

Applies to: SharePoint Server 2010, Excel Services (SharePoint 2010)

This article describes trusted file locations for Excel Services in Microsoft SharePoint Server 2010. Excel Services automatically creates a default trusted file location (http://) which trusts the whole SharePoint Server farm. This default trusted location enables any file to be loaded from the SharePoint Server farm or stand-alone deployment using Excel Services. Trusting the whole SharePoint farm by default enables easier setup for administrators. Administrators can define new trusted file locations to expand workbook capabilities and tighten security.

Manage Excel Services trusted file locations

Excel Services administrators can add new trusted file locations as needed. Trusted file locations are either SharePoint sites, UNC paths, or HTTP Web sites from which a server that is running Excel Services is permitted to access workbooks.

To add a trusted file location

  1. On the SharePoint Central Administration Web site home page, in the Application Management section, click Manage service applications.

  2. On the Manage service applications page, click the Excel Services service application that you want to configure.

  3. On the Manage Excel Services Application page, click Trusted File Locations.

  4. On the Excel Services Application Trusted File Locations page, click Add Trusted File Location.

To configure trusted file locations

  1. On the Excel Services Application Add Trusted File Location page, in the Location section, type the Address of the SharePoint document library that you want to add as a trusted file location in Excel Services. In Location Type you must match the address format that you named earlier. For instance, if the document library is stored in the SharePoint Foundation 2010 content database, select Microsoft SharePoint Foundation. If the document library is stored in a network file share, select UNC. If the document library is stored in a Web folder address, select HTTP. In Trust Children, select Children trusted if you want to trust all child libraries or directories. In Description, you can define the purpose of this trusted location.

  2. In the Session Management section, you determine the behavior Excel Calculation Services sessions can have on your server. Start with the default values for all settings and then adjust as necessary according to the network usage. Each Session Management setting is shown in the following list together with the default time.

    1. In the Session Timeout box, type a value in seconds that an Excel Calculation Services session can stay open and inactive before it is shut down, as measured from the end of each Open request. The default is 450 seconds.

    2. In the Short Session Timeout box, type a value in seconds that an Excel Services session stays open and inactive, before any user interaction, before it is shut down. This is measured from the end of the original open request. The default is 450 seconds.

    3. In the New Workbook Session Timeout box, type a value in seconds that an Excel Calculation Services session for a new workbook stays open and inactive before it is shut down, as measured from the end of each request. The default value is 1,800 seconds (30 minutes).

    4. In the Maximum Request Duration box, type a value in seconds for the maximum duration of a single request in a session. The default is 300 seconds.

    5. In the Maximum Chart Reader Duration box, type a value in seconds for the maximum time that is spent rendering any single chart. The default is 3 seconds.

  3. In the Workbook Properties section, you determine the resource capacity for the server when Excel Services opens workbooks.

    Performance and resource availability can be compromised if users open extensive workbooks. If you do not manage the approved size that workbooks running in open Excel Calculation Services sessions can have, you risk having users exceed your resource capacity and overloading the server.

    1. In the Maximum Workbook Size box, type a value in megabytes (MB) for the maximum size of workbooks that Excel Calculation Services can open. The default size is 10 megabytes.

    2. In the Maximum Chart or Image Size box, type a value in megabytes (MB) for the maximum size of charts or images that Excel Calculation Services can open. The default size is 1 megabyte.

  4. In the Calculation Behavior section, you determine calculation modes in Excel Calculation Services for workbooks from this location.

    1. In the Volatile Function Cache Lifetime box, type the value in seconds that a computed value for a volatile function is cached for automatic recalculations. The default is 300 seconds.

    2. Under Workbook Calculation Mode, select one of the following:

      • File   Select this option to perform calculations as specified in the file.

      • Manual   Select this option if you want recalculation to occur only when a Calculate request is received.

      • Automatic   Select this option if you want any change to a value to cause the recalculation of all other values that depend on that value. Also, volatile functions are called if their time-out has expired.

      • Automatic except data tables   Select this option if you want any change to a value to cause the recalculations of all other values dependent on that value (the values cannot be in a data table.) Also, volatile functions are called if their time-out has expired.

  5. In the External Data section, you determine how Excel Calculation Services handles external data connections for workbooks from this location.

    1. Under Allow External Data, select one of the following:

      • None   Select this option to disable all external data connections for the trusted file location.

      • Trusted data connection libraries only   Select this option to only enable using connections to data sources that are stored in a trusted data connection library. The server will ignore settings embedded in the worksheet.

      • Trusted data connection libraries and embedded   Select this option to enable connections that are embedded in the workbook file or connections that are stored in a trusted data connection library.

        If you do not have to have tight control or restrictions on the data connections that are used by workbooks on the server, consider selecting this option.

    2. Under Warn on Refresh, select the Refresh warning enabled check box to display a warning before refreshing external data for files in this location. When you select this option, you make sure that external data is not automatically refreshed without user interaction.

    3. Under Display Granular External Data Errors, select the Granular External Data Errors check box to display specific error messages when external data failures occur for files in this location. Displaying specific error messages can help troubleshoot data connectivity issues if they occur.

    4. Under Stop When Refresh on Open Fails, select the Stopping open enabled check box to prevent viewing a file in this trusted file location.

    5. Under External Data Cache Lifetime, you can determine the maximum time, in seconds that the system can use external data query results.

      • In the Automatic refresh (periodic / on-open) box, type a value in seconds for the maximum time that the system can use external data query results for automatically refreshed external query results. The default is 300 seconds.

      • In the Manual refresh box, type a value in seconds for the maximum time that the system can use external data query results for automatically refreshed external query results. To prevent data refresh after the first query, type -1. The default is 300 seconds.

    6. In the Maximum Concurrent Queries Per Session box, type a value for the maximum number of queries that can run at the same time during a single session. The default is 5 queries.

    7. Under Allow External Data Using REST, select the Data refresh from REST enabled check box to all requests from the REST API to refresh external data connections. Note that this setting has no effect if Allow External Data is set to None. Note too, that this setting has no effect if Warn on Refresh is enabled.

  6. In the User-Defined Functions section, under Allow User-Defined Functions, select User-defined functions allowed if you want to allow user-defined functions in Excel Calculation Services for workbooks from this location.

  7. Click OK.

To edit a trusted file location

  1. On the Excel Services Application Trusted File Locations page, point to the trusted file location that you want to edit, click the arrow that appears, and then click Edit.

To delete a trusted file location

  1. On the Excel Services Application Trusted File Locations page, point to the trusted file location that you want to delete, click the arrow that appears, and then click Delete.

  2. Click OK in the message box that asks whether you want to continue with the deletion.

Video demonstration

To view a demonstration of how to manage Excel Services Application trusted file locations, watch the following video.

Configure Excel Services Trusted Location

Watch the video │ To download a copy of this video, right-click the link, and then click Save Target As.