Manage Excel Services connections (SharePoint Server 2010)

  • Article

 

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

Excel Services in Microsoft SharePoint Server 2010 provides the ability to connect to external data sources and refresh the data in data-connected Microsoft Excel workbooks when it renders them in a browser. Data connections can be loaded by using information from the workbook file, but using a data connection library provides an additional layer for data connections so that they can be managed separately from workbooks.

Data providers are drivers that client applications (such as Excel and Excel Services) use to connect to specific data sources. Data providers handle queries, parse connection strings, and perform other connection-specific logic. Any data provider that is used by Excel Services must be explicitly trusted by the server administrator.

This article helps you configure the following Excel Services features:

  • Trusted data connection libraries

  • Trusted data providers

Trusted data connection libraries

Trusted data connection libraries are SharePoint Server 2010 data connection libraries that contain data connection files that Excel Services will trust to use to connect to databases. These files contain everything that Excel Services and Excel client have to have to connect to an external data source. Data connection libraries enable broad reuse and sharing of data connections.

Excel Services does not use data connection files that are not stored in a trusted data connection library. However, data connection information can be embedded directly in a workbook that is trying to make a connection.

You can create different trusted data connection libraries for different purposes or projects, and you can customize the settings and permissions to the libraries accordingly.

Data connections connect workbooks to data providers. For workbooks that use the same data connection file, changing the data connection file is all that is required to change connection information; changing the individual workbooks is not necessary.

Initially, there are no Excel Services trusted data connection libraries (except for Report Center sites, which create their own trusted data connection file). To store data connection files, you must create at least one trusted data connection library.

To manage Excel Services trusted data connection libraries

  1. In Microsoft SharePoint 2010 Central Administration, 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 page, click Trusted Data Connection Libraries.

To add a trusted data connection library

  1. On the Excel Services Application Trusted Data Connection Libraries page, click Add Trusted Data Connection Library.

  2. On the Excel Services Application Add Trusted Data Connection Library page, in the Location section, type the address of the trusted data connection library in the Address box.

  3. In the Description box, you can also type a description of the purpose for this trusted data connection library.

  4. Click OK.

To configure a trusted data connection library

  1. On the Excel Services Application Trusted Data Connection Libraries page, either click the data connection library that you want to configure or point to the data connection library, click the arrow that appears, and then click Edit.

To delete a trusted data connection library

  1. On the Excel Services Application Trusted Data Connection Libraries page, point to the data connection library 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.

    Note

    Deleting a trusted data connection library does not affect the library itself. It only removes the library as a trusted data connection library in Excel Services.

Video demonstration

To view a demonstration of how to manage a trusted data connection library in Excel Services Application, watch the following video.

Configure Excel Services Trusted Data Connections

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

Trusted data providers

Trusted data providers are data providers from which Excel Services accesses data. A data provider is a database type combined with a protocol for accessing data (for example, SQL Server combined with ODBC).

  • Excel Services does not access data that does not come from a trusted data provider.

  • Excel Services contains entries for common data providers. Add additional data providers as needed.

To manage Excel Services trusted data providers

  1. In the Central Administration 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 page, click Trusted Data Providers.

To add a trusted data provider

  1. On the Excel Services Application Trusted Data Providers page, click Add Trusted Data Provider.

  2. On the Excel Services Application Add Trusted Data Provider page, in the Provider section, type the provider ID of the trusted data provider in the Provider ID box (for example, type SQL Server). Look in a valid connection string to find the provider ID.

  3. Under Provider Type, select one of the following:

    • OLE DB   Select this option to access data by using Object Linking and Embedding (OLE).

    • ODBC   Select this option to access data by using Open Database Connectivity (ODBC).

    • ODBC DSN   Select this option to access data by using Open Database Connectivity with Data Source Name (ODBC DSN).

  4. In the Description box, you can also type a description of the purpose for this trusted data provider.

  5. Click OK.

To configure a trusted data provider

  1. On the Excel Services Application Trusted Data Providers page, click Edit on the menu of the data provider that you want to configure.

To delete a trusted data provider

  1. On the Excel Services Application Trusted Data Providers page, click Delete on the menu of the data provider that you want to delete.

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

See Also

Concepts

Plan Excel Services data sources and external connections (SharePoint Server 2010)