Office Data Connections can connect to virtually any data source for which a data driver is configured on the machine making the query. In the case of ODC files stored in Data Connection Libraries, these drivers must be installed and configured on the Excel Calculation Server. In addition, Excel Calculation Services must be configured to explicitly trust the driver by entering it into the list of Trusted Data Providers. Excel Calculation Services is configured to trust a set of data providers that includes SQL Server 2000, SQL Server 2005, Oracle 9.2, and IBM DB2. If your organization uses any database systems not on this list and you want to access them through Excel Calculation Services then you need to configure the drivers as trusted. Once the drivers have been installed on the server and trusted, they should be available through any ODC that has authenticated access to the data. To configure a Trusted Data Provider, first install the driver for the data source and then take the following steps.
Open Central Administration from the Start menu.
Click the link for the Shared Services Provider.
Under Excel Services Settings, click Trusted Data Providers.
Click Add Trusted Data Provider.
In the Provider ID section, as shown in Figure 11-8, type the identifier of the driver you want to add as a Trusted Data Provider.
Select one of the following provider types:
OLE DB: Use for most Microsoft and many third-party drivers that have been written to use the current OLE DB interface.
ODBC: Use for drivers that confirm to the Open Database Connectivity standard used by most database manufacturers.
ODBC DSN: Use to identify a reference to a Data Source Name on the local server that contains connection string information for an ODBC driver.
Figure 11-8: Adding a Trusted Data Provider.