Plan external data connections for Excel Services

Applies To: Office SharePoint Server 2007

This Office product will reach end of support on October 10, 2017. To stay supported, you will need to upgrade. For more information, see , Resources to help you upgrade your Office 2007 servers and clients.

 

Topic Last Modified: 2017-01-25

In this article:

• About Excel Services connections to external data

• Connections and Excel workbooks

• Data providers

• Authentication to external data

• Data connection libraries and managed connections

• Excel Services security and external data

• Step-by-step server configuration for external data

• Step-by-step workbook configuration for external data

• Frequently asked questions

To configure Microsoft Office SharePoint Server 2007 to enable workbooks loaded on Excel Services in Microsoft Office SharePoint Server 2007 to successfully refresh external data, you need to understand the relationships and dependencies between Office SharePoint Server 2007 and Excel Services.

The guidance provided in this article helps you resolve the following Data Refresh Failed error message.

About Excel Services connections to external data

This article provides guidance to help you configure the following Office SharePoint Server 2007 application server components:

• Excel Services

• Single sign-on (SSO)

• Office SharePoint Server 2007

This article also includes background information that provides an overview of application server and external data concepts, some of which are specific to Office Excel 2007. This background information contains important information that will help you successfully set up Excel Services to enable workbooks to consume external data.

This article also shows you how to configure Office Excel 2007 workbooks and provides answers to frequently asked questions, including questions about:

• Server security and external data

• Managed data connections

Connections and Excel workbooks

Every Excel workbook that uses external data contains a connection to a data source. Connections consist of everything that is required to establish communications with, and retrieve data from, an external data source. This includes:

• A connection string (a string that specifies which server to connect to and how to connect to it).

• A query (a string that specifies what data to retrieve).

• Any other specifics required to get the data.

Embedded and linked connections

Excel workbooks can contain embedded connections and linked connections. Embedded connections are stored internally as part of the workbook. Linked connections are stored externally as separate files that can be referenced by a workbook.

Functionally, there is no difference between embedded and linked connections. Both will correctly specify all the required parameters to connect to data successfully. Linked connection files can be centrally stored, secured, managed, and reused. They are often a good choice when planning an overall approach to getting a large group of users connected to external data. For more information, see Data connection libraries and managed connections.

For a single connection, a workbook can have both an embedded copy of the connection information and a link to an external connection file. The connection can be configured to always use an external connection file to refresh data from an external data source. In this example, if the external connection file cannot be retrieved, or if it fails to establish a connection to the data source, the workbook cannot retrieve data. If the connection is not configured to use only an external connection file, Excel attempts to use the embedded copy of a connection. If that fails, Excel attempts to use the connection file to connect to the external data source. The ability to specify that only connection files can be used to establish a communications link to an external data source is a new feature of 2007 Microsoft Office system and provides support for the managed connection scenarios described in Data connection libraries and managed connections.

Excel Services can use connections coming from an external connection file and connections that are embedded in the workbooks. There are some restrictions for external connection files. For more information, see Excel Services security and external data.) If both types of connections are allowed on the server, the behavior is the same as the Excel behavior described in the previous paragraph.

For security purposes, Excel Services can be configured to only allow connections from connection files. In this configuration, all embedded connections are ignored for workbooks loaded on the server, and connections are only attempted when there is a link to a valid connection file that is trusted by the server administrator. For more information, see Trusted data connection libraries.

Note

There are many types of connection files, and Excel Services only works with Office data connection files (.odc).

Data providers

Data providers are drivers that client applications (such as Excel and Excel Services) use to connect to specific data sources. For example, a special MSOLAP data provider is used to connect to Microsoft SQL Server 2005 Analysis Services. The data provider will be specified as part of the connection in the connection string. You do not need to know a lot about data providers in the context of this article, but you need to understand that:

• Data providers are typically well-tested, stable sets of libraries that can be used to connect to external data.

• Any data provider used by Excel Services must be explicitly trusted by the server administrator. For information about adding a new data provider to the trusted providers list, see Adding a trusted data provider for Excel Services.

  Note

  By default, Excel Services trusts many well-known and stable data providers. In most cases, it is not necessary to add a new data provider. Data providers are typically added for custom solutions.

• Data providers handle queries, parsing connection strings, and other connection-specific logic. This functionality is not part of Excel Services. Excel Services cannot control how data providers behave.

Authentication to external data

Data servers require that a user be authenticated. Authentication is essentially the act of telling the server who you are. The next step is authorization. Authorization is essentially the act of telling the server what you can do. Authentication is required to enable the data server to perform authorization, or to enforce security restrictions that prevent data from being exposed to anyone other than authorized users.

Excel Services needs to tell the data source who is requesting the data. In most scenarios, this is going to be the user who is viewing an Excel report in a browser. Essentially, the purpose of this article is to explain authentication between Excel Services and an external data source. Authentication at this level is shown in the following diagram. The arrow on the right depicts the authentication link from an application server running Excel Calculation Services to an external data source.

There are many ways to implement authentication, but this article is going to focus on the three methods that apply to Excel Services:

• Windows authentication

• SSO

• None

Excel Services determines the type of authentication based on a property of the connection. This property must be explicitly set, and can be set by using Office Excel 2007 client. If the authentication type is missing, the default of Windows authentication is attempted. For more information, see Specifying the server authentication for an existing connection.

Windows authentication

This method uses your Windows user identity to authenticate against a data source. For the scope of this article it is not important to know the specific mechanism that the operating system uses to do this (such as NTLM or constrained delegation). What is important to understand is that Windows authentication is considered to be the most secure way to access external data, and is typically the default method for external data access when using an Excel client to connect to data sources, such as SQL Server 2005 Analysis Services.

In most enterprise environments, Excel Services will be set up as part of a farm with the front-end Web server, back-end Excel Calculation Services server, and data source, all running on different computers, as depicted in the diagram in Authentication to external data. This means that delegation, or Kerberos, (constrained delegation is recommended) will be required to allow data connections that use Windows authentication. This is because delegation is required to ensure that user identities can be communicated from computer to computer in a trusted and secure way. In a farm deployment, these kinds of connections will not work on Excel Services unless Kerberos is correctly configured.

SSO

Single sign-on (SSO) is a centralized database that is commonly used to store credentials (a user ID and password pair) that can be used by applications to authenticate to other applications. In this case, Excel Services relies on SSO to store and retrieve credentials for use in authenticating to external data sources.

Each SSO entry contains an application ID that serves as a lookup that is used to retrieve the appropriate set of credentials. Each application ID can have permissions applied so that only specific users or groups can access the credentials that are stored for that application ID.

Given an application ID, Excel Services will retrieve the credentials from the SSO database on behalf of the user who is accessing the workbook (either through the browser or Excel Web Services). Excel Services will then use those credentials to authenticate to the data source and retrieve data.

Note

The application ID must be explicitly set for the connection. For information on how to specify an application ID, see Specifying the server authentication for an existing connection.

None

This authentication method simply means that no credential retrieval should take place or that no special action is taken for authentication for the connection. For example, Excel Services should not try to delegate credentials, and should not try to retrieve credentials from the SSO database. In these cases, Excel Services should simply hand the connection string to the data provider and allow the provider to worry about how to authenticate.

In more practical terms, this means that typically a connection string will specify a user name and password that should be used to connect to the data source. However, sometimes the connection string might specify that the integrated security be used. That is, the Windows identity of the user or computer that is issuing the request should be used to connect to the data source. In these cases, the data source will not be connected to as Excel Services, but will instead be connected to as the unattended account. For more information, see Unattended account.

Data connection libraries and managed connections

A data connection library is a new type of list added to Office SharePoint Server 2007. This is a SharePoint list that is designed to store connection files, which can then be referenced by 2007 Office system applications, such as Office Excel 2007.

Data connection libraries give customers the ability to centrally manage, secure, store, and reuse data connections.

Reusing connections

Because the data connection library is in a well-known place in Office SharePoint Server 2007 and displays friendly business names and descriptions, people can reuse connections that other people create or configure. A knowledgeable information worker or data expert can create connections, and other people can reuse them without having to understand the details about data providers, server names, or authentication. The location of the data connection library even can be published to Office clients so the data connections will be displayed right in Excel or in any other client application that makes use of the data connection library. For more information, see Creating a data connection library.

Managing connections

Because workbooks contain a link to the file in a data connection library, if something about the connection changes (such as a server name or an SSO application ID), only a single connection file needs to be updated as opposed to hundreds of workbooks. The workbooks will pick up the connection changes automatically the next time they use that connection file to refresh from Excel or on Excel Services.

Securing connections

The data connection library is a SharePoint list and supports all the permissions that Office SharePoint Server 2007 does, including per-folder and per-item permissions. The advantage that this provides on the server is that a data connection library can become a locked-down data connection store that is highly controlled. Many users might have read-only access to it so that they can use the data connections, but they can be prevented from adding new connections. By using access control lists (ACLs) with the data connection library, and allowing only trusted authors to upload connections, the data connection library becomes a store of trusted connections. Trusted connections are connections that are known not to contain malicious queries.

Excel Services can be configured to load connection files only from data connection libraries that are explicitly trusted by the server administrator, and to block loading of any embedded connections. In this configuration, Excel Services uses the data connection library to apply another layer of security around data connections.

Data connection libraries can even be used in conjunction with the new Viewer role in Office SharePoint Server 2007 that will allow those connections to be used to refresh workbooks loaded on Excel Services. If the Viewer role is applied, users cannot access the connection file contents from a client application, such as Excel. Therefore, the connection file contents are protected but can still be used for workbooks refreshed on the server.

Excel Services security and external data

Excel Services has many layers of security. The following subsections address only the concepts that are directly relevant to external data access.

Trusted file locations

Excel Services will only load workbooks from trusted file locations. A trusted file location is essentially a directory (that might include all subdirectories) that the administrator has explicitly allowed workbooks to be loaded from. These directories are added to a list that is internal to Excel Services. This list is known as the trusted file locations list.

Trusted locations might specify a set of restrictions for workbooks loaded from them. All workbooks loaded from a trusted location will adhere to the settings for that trusted location. Here is a short list of the trusted location settings that affect external data:

• How external data can be accessed. The options for this include:

  • No data access allowed (default).

  • Only connection files in a Office SharePoint Server 2007 data connection library can be used.

  • Connections embedded in workbooks are allowed in addition to connection files from a data connection library.

• Whether to show the query refresh warnings or not.

• Whether to fail the workbook load if external data fails to refresh when the workbook opens. This is used in scenarios where the workbook has cached data results that will change depending on the identity of the user viewing the workbook. The goal is to hide these cached results, and ensure that any user who views the workbook can see only the data that is specific to them. In this case, the workbook will attempt to refresh on open. You can set refresh on open for each connection. If the refresh fails, the workbook is not displayed to users who cannot open it in Excel client.

  Note

  This only works if the workbook is locked down by Viewer role permissions in Office SharePoint Server 2007, because a user who can open the workbook directly in Excel can always see the cached data results.

• External data cache expiration times. Data is shared among many users on the server to improve scale and performance, and these cache life times are adjustable. This accommodates scenarios where query execution should be kept to a minimum because the query might take a long time to execute. In these scenarios, the data often changes only daily, weekly, or monthly as opposed to by the minute or every hour.

Trusted data connection libraries

As with workbook files, Excel Services will only load connection files from Office SharePoint Server 2007 trusted data connection libraries. A trusted data connection library is a library that the server administrator has explicitly added to an internal trusted list. For information about how data connection libraries allow an administrator to secure and manage connection files, see Data connection libraries and managed connections. For information about how to trust a data connection library for use with Excel Services, see Trusting a data connection library on Excel Services.

Trusted data providers

Excel Services will only use external data providers that are added to an internal trusted providers list. This is a security mechanism that prevents the server from using providers that the administrator does not trust. For information about how to trust a data provider, see Adding a trusted data provider for Excel Services.

Unattended account

The unattended account is a special account that Excel Services impersonates any time it is attempting to authenticate to a data source that is not using Integrated Windows authentication. Because Excel Services has no control over the data provider, and does not directly parse provider-specific connection strings, it needs to mitigate security threats whereby the identity of Excel Services itself can be used to connect to a data source. The unattended account is used to mitigate such threats.

Excel Services will often run with a highly privileged account, and this level of privilege is not appropriate for end users who are trying to only view data. When external data authentication is set to either None or SSO, where the SSO application ID is not storing Windows credentials, the unattended account is impersonated before attempting to connect to data. Because the unattended account is not expected to have privileges to the data source, this will prevent accidental or malicious connections to data sources in the context of a privileged account.

If the unattended account has access to the data source (when authentication type is set to None), a connection will be successfully established by using the credentials of the unattended service account. Use caution when designing solutions that purposely use this account to connect to data. This is a single account that can potentially be used by every workbook on the server. Any user loading a workbook on Excel Services and setting the authentication type to None might be able to view that data by using the server. In some scenarios, this might be needed. However, SSO is the preferred solution for managing passwords on a per-user or per-group basis.

Trusted subsystem and delegation

When Excel Services is deployed, the method that is used to communicate among the different server farm components is specified. It is configured in either trusted subsystem mode or delegation mode. This mode can be changed only by using the Stsadm.exe command-line tool.

Trusted subsystem (default in a farm deployment) is a mode in which the front-end and back-end server components have a two-way trust. This allows files to be retrieved from Office SharePoint Server 2007 by using the Excel Services account. However, even though Excel Services retrieves the files, it performs a security check to verify that the user requesting the file has the appropriate permissions. In this mode, the back-end Excel Calculation Services server does know the user's identity, but does not have a full user security token and so cannot delegate it to other computers.

Delegation (default in single computer or evaluator deployment) is a mode in which the front-end servers of the farm always delegate the user's identity to the back-end servers. In this case, files are retrieved as the end user who is requesting the workbook instead of the Excel Services account. The back-end Excel Calculation Services server has the user's full identity (security token) and so can delegate it to other servers.

Connections that use Windows authentication will only work when Excel Services is deployed in delegation mode. This is because when the server is in trusted subsystem mode, the back-end calculation component of the application server does not have the end user's (the person viewing the workbooks) full security token and so it cannot delegate the identity to the data source.

Step-by-step server configuration for external data

This section gives step by step instructions for performing basic configuration on the server side to enable external data connectivity. This includes configuration of all the Office Server components that are required for Excel Services to refresh external data, and therefore covers more than just Excel Services administration.

This section references the SharePoint Central Administration Web site and Shared Services Provider (SSP) administration. This section also contains the instructions for launching those consoles. To complete these steps, you will need access to Central Administration and SSP administration, respectively.

Access the Central Administration console

1. On the taskbar, click Start, point to All Programs, point to Microsoft Office Server, and then click SharePoint 3.0 Central Administration.

Access the SSP administration console

1. From the Central Administration console, perform either of the following procedures:

2. Select the name of the SSP from the left-hand navigation bar. For example, SharedServices1 as shown in the following figure.

   

3. In the top navigation bar, click the Application Management tab.

   1. On the Application Management page, click Create or configure this farm's shared services.

   2. On the Create or Configure this Farm's Shared Services page, select the name of the SSP.

Adding a trusted data provider for Excel Services

Excel Services will only attempt to process connections that use trusted data providers. For most common types of data connections (such as ODBC, OLEDB, SQL Server, and OLAP), it is not necessary to add a provider because by default Excel Services trusts many standard providers. Providers are typically only added for very custom solutions. For more information, see Data providers.

Add a new provider for use with Excel Services

1. Ensure that the data provider is installed on each server in the farm. This is specific to the data provider being used and is beyond the scope of this article.

2. Launch the SSP administration console. For more information, see Step-by-step server configuration for external data.

3. In the Excel Services Settings section, click Trusted data providers, as shown in the following figure.

   

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

5. Fill in the values for the Provider ID, Provider Type, and description, as shown in the following figure.

   

6. Click OK.

   Note

   The ID that is in the Provider ID should be used in the connection strings that this provider generates.

Allowing external data for Excel services

External data must be explicitly enabled per trusted file location.

Enable external data access for a trusted file location

1. Launch the SSP administration console. For more information, see Step-by-step server configuration for external data.

2. In the Excel Services Settings section, click Trusted file locations, as shown in the following figure.

   

3. On the Excel Services Trusted File Locations page, select the name of the trusted file location that you are attempting to load workbooks from, or click the Add Trusted File Location to create a new one, as shown in the following figure.

   

4. On the details page for the trusted file location, scroll down to the External Data section, as shown in the following figure.

   

5. Select the Trusted data connection libraries only option button to allow access to connection files in a data connection library. Or, select the Trusted data connection libraries and embedded option button to enable connections embedded in a workbook and connections from a data connection library. This is also the location where you can toggle other external data settings that affect the server, such as refresh warnings, data cache time-outs, and so on.

Setting the unattended account

The unattended account is required to enable connections that have their authentication set to either None or SSO where the SSO application ID is not using Windows credentials. For more information on the unattended account, see Unattended account. If these types of connections will not be used, the unattended account does not need to be configured.

Configure the unattended account settings

1. Launch the SSP administration console. For more information, see Step-by-step server configuration for external data.

2. On the Shared Services home page, in the Excel Services Settings section, click Edit Excel Services settings, as shown in the following figure.

   

3. On the Edit Excel Services Settings page, scroll down to the Unattended Service Account section. Enter the domain user name and password of the account you want to use, as shown in the following figure.

   

4. Click OK.

Configuring Office Server single sign-on

SSO enables middle-tier application servers to store and retrieve user credentials. The key to credential retrieval is the application ID.

Application IDs are used to map users to credential sets. This mapping is available for groups and individuals. A group mapping means that every user that is a member of a specific domain group is mapped to the same set of credentials. Group mappings are best for scale and performance on Excel Services because they allow external data cache sharing. Individual mappings map each individual user to that user's own set of credentials.

Office SharePoint Server 2007 can support any SSO provider that implements the pluggable Office SharePoint Server 2007 SSO interface. Office Server ships with its own version of SSO, referred to Office SharePoint SSO. This section only deals with the configuration of Office SharePoint SSO, and does not cover any third-party SSO provider configuration in any detail.

If the user needs more SSO functionality beyond what Office SharePoint SSO provides, another SSO provider can be used.

Note

Microsoft also ships a new version of Enterprise Single Sign-On (EntSSO) with Microsoft Host Integration Server and Microsoft BizTalk Server. EntSSO is not supported by Excel Services. EntSSO provides more functionality, such as application IDs that support multiple group mappings, administration of application IDs on a per-application ID basis, and functionality that helps populate and update passwords that are stored in the SSO database.

This section is not intended to be a comprehensive guide on everything a user can do by using SSO. It is only intended to give the basic steps, under common configurations, to get Office SharePoint SSO successfully configured on the server so that it is ready to be used for refreshing data on Excel Services.

Start the Microsoft Single Sign-On service on the server

The Microsoft Single Sign-On service should be started on each server in the farm.

Start the Single Sign-On service

1. From Administrative Tools, click Services.

2. Double-click Microsoft Single Sign-On Service to launch the Single Sign-On Service Properties page, as shown in the following figure.

   

3. On the General tab of the Single Sign-On Service Properties page, set the startup type to Automatic.

   

4. On the Log On tab of the Single Sign-On Service Properties page, click This account, and then type the domain account that you want to run the service as, as shown in the following figure.

   

   Note

   For most evaluator or demonstration deployments, such as a single-click installation, it is easiest to use the same credentials as the account that was used to install Office Server. For more information about account requirements for installing SSO, see More on Office Server SSO accounts and privileges.

5. Click Apply.

6. On the General tab of the Single Sign-On Service Properties page, click Start to start the service, and then click OK.

Configuring SSO on Office Server

SSO must be configured so that application IDs and passwords can be added. For information on how to configure application IDs, see Creating a new application ID in SSO.

Configure SSO on Office Server

1. Launch the Central Administration console. For more information, see Step-by-step server configuration for external data.

2. On the Operations tab, in the Security Configuration section, select Manage settings for single sign-on, as shown in the following figure.

   

3. From the Manage Settings for Single Sign-On page, select Manage server settings, as shown in the following figure.

   

4. From the Manage Server Settings for Single Sign-On page, enter the credentials of the SSO administrator account and the Enterprise Application Definition administrator account.

   Note

   The Enterprise Application Definition administrator account is used for management of application IDs and passwords in SSO. For most configurations, the account that was used to install the server can be used for both of the SSO administrator account, and the Enterprise Application Definition administrator account. For more information on accounts used to set up SSO, see More on Office Server SSO accounts and privileges.

5. Accept the default database and time-out settings. Click OK to save and close the page.

More on Office Server SSO accounts and privileges

This section provides more detail about the specific memberships, rights, and privileges of the different accounts that are used to configure Office Server SSO. This does not necessarily apply to any third-party pluggable SSO providers.

For evaluation or demonstration purposes, the account that was used to install Office Server can be used to configure SSO. This is the simplest configuration. However, in a production or enterprise deployment, it is considered more secure to ensure the SSO administrator and service account is different from the account that was used to install the server.

• SSO service account   The user that the SSO service account is running as must be a member of the WSS_Admin_WPG group on the local machine, and it must be a domain account.

• SSO administrator account   This can be any domain group or user account. The user who is setting the SSO administrator account must be a member of the domain group that the SSO administrator account is getting set to. Or, if an individual account is being used as the SSO administrator account, it must be the account of the current user.

  When Excel Services is configured in a trusted subsystem mode, the SSP application pool account must be a member of the SSO administrator group. (For more information, see Trusted subsystem and delegation.) This enables SSO ticketing, which Excel Services uses in such configurations, to work. The SSP application pool account is the security account that is used to run the shared services application pool in IIS.

• SSO Application Manager account   This can be any domain group or individual user account. Accounts that are added as SSO Application Managers must have at least Read access to the Central Administrator site, as this is the site where the SSO accounts are managed from.

• SSO configuration account   This section addresses the requirements of the account that is being used to configure SSO. The account being used to setup and configure SSO must:

  • Be a local administrator of the server that stores the master key used for password encryption/decryption. This server is also referred to as the "secret server."

  • Be a member of the following SQL Server roles on the SQL Server computer that stores the SSO passwords: Security Admin, Server Admin, and Db Creator.

  • Be an Office Server farm administrator. This allows the user to write to the Office SharePoint Server 2007 configuration database during SSO configuration.

  • Be a member of the SSO administrator group that is set during SSO configuration.

  • Have the ability to log on to the console of the server where SSO is going to be configured.

Example of SSO accounts and privileges

An administrator named Bob uses the domain account, CORPDOMAIN\ServerAdmin, to install Office SharePoint Server 2007. This account is an administrator account on every server in the farm, and also has Office Server farm administrative rights. Bob logs on to the computer by using the CORPDOMAIN\ServerAdmin account. Bob adds the account, CORPDOMAIN\ServerService, to the WSS_Admin_WPG group on each computer. Bob starts the Single Sign-On service on each computer, running the service as CORPDOMAIN\ServerService.

Bob then launches the Central Administration console to configure SSO. Bob sets the domain group CORPDOMAIN\OfficeServerAdmins to be both the SSO administrator account and the SSO application manager account. The CORPDOMIAN\ServerAdmin account, which Bob is using for this configuration, is a member of the domain group CORPDOMAIN\OfficeServerAdmins. Bob goes to the SSP administration pages to double-check the account that the SSP is running as. He sees that the SSP is running as CORPDOMAIN\SharedServiceAdmin. Bob knows that for Excel Services to work, CORPDOMAIN\SharedServiceAdmin must have rights as an SSO administrator. CORPDOMAIN\SharedServiceAdmin is a member of the domain group CORPDOMAIN\OfficeServerAdmins, which was the group used as the SSO administrator. After configuring the rest of the server and Excel Services, Bob sees that Excel Services can use SSO for external data refresh.

Creating a new application ID in SSO

Create a new application ID in SSO

1. Launch the Central Administration console. For more information, see Step-by-step server configuration for external data.

2. On the Operations tab, in the Security Configuration section, select Manage settings for single sign-on.

3. From the Manage Settings for Single Sign-On page, select Manage settings for enterprise application definitions, as shown in the following figure.

   

4. From the Manage Enterprise Application Definitions page, click New Item from the toolbar to create a new application ID.

5. Enter the values for the new application. The application name is what users must enter as the SSO application ID into their data connections in Excel client when configuring the connection to work on the server. Typically, the Display name and the Application name should be the same.

6. Enter the e-mail address of the user or group that will be managing this application definition.

7. Select the Account type.

   1. Select Group if all users of a specific group should be mapped to a single set of credentials. You will get a chance to define the domain group to map from later.

   2. Select Individual if every user should be mapped to a unique set of credentials.

      Note

      Do not choose Group using restricted account. This is not supported by Excel Services.

8. In the Authentication type section, select the Windows authentication check box if the credentials you plan to store for this application ID will be Windows domain credentials.

   Note

   For Excel Services scenarios, because this SSO entry will store user name and passwords, the Field values under the Logon Account Information at the bottom of the page do not need to be altered.

9. If appropriate, configure the settings for an application that uses a group mapping and stores Windows credentials, as shown in the following figure. This will be a common configuration when retrieving from an OLAP source, such as SQL Server 2005 Analysis Services.

   

10. Click OK at the bottom of the page to save the application definition.

11. In the Manage Settings for Single Sign-On page, click Manage account information for enterprise applications, as shown in the following figure.

    

12. From the Manage Account Information for Enterprise Applications page, you can control which accounts can access the credentials stored for an application ID, and manage the actual credentials that are stored for an application ID.

13. From the Enterprise application definition drop-down list, select the application ID that you entered in the last step, as shown in the following figure.

    

14. In the Group account name text box, enter the group that should be granted access to the credentials stored for this application ID. This is not the account that is used to access the data source, this is simply the domain group or user account that should map to the credentials that are actually used to authenticate to the data source.

    Note

    If every user in the domain should be allowed to access these credentials, enter DOMAIN\domain users in this text box.

15. Ensure the Update account information option button is selected, and click the Set button.

16. The account information page will launch. Enter the user name and password of the account that should be stored in SSO, as shown in the following figure. This is the account that is used by Excel Services to access the data source. After you have typed the credentials, click OK.

    

    Note

    If the password is left blank when the SSO credentials are Windows credentials, Excel Services will attempt to use Protocol Transitioning on behalf of the end user to connect to the data source. Protocol Transitioning is beyond the scope of this article.

17. You will be returned to the page for managing account information. You can close the browser or click Done to return to the SSO administration home page.

Creating a data connection library

The data connection library is a list template in Office SharePoint Server 2007. It can be created almost anywhere a list can be created in Office SharePoint Server 2007.

Note

The Report Center site already has a data connection library created by default, and serves as a convenient place to manage all the data connections that are used by reports in that site.

Add a data connection library to an existing SharePoint site

1. From a SharePoint site, click View All Site Content from the left navigation bar for the site, as shown in the following figure.

   

2. Click the Create button near the top of the page, as shown in the following figure.

   

3. On the next page, in the Libraries section, select Data Connection Library, as shown in the following figure.

   

4. On the next page, enter a name for the data connection library and optional description, as shown in the following figure.

   

5. Click Create.

Trusting a data connection library on Excel Services

Before connection files can be used by Excel Services, the data connection library must be explicitly added to the internal trusted data connection libraries list.

Add a data connection library to trusted list

1. Launch the SSP administration console. For more information, see Step-by-step server configuration for external data.

2. In the Excel Services Settings section, select Trusted data connection libraries.

3. On the Excel Services Trusted Data Connection Libraries page, enter the URL of the data connection library and enter an optional description, as shown in the following figure.

   

Note

The URL must connect directly to the data connection library, as shown in the previous figure. The URL cannot connect to the forms directory or to the default aspx page. If you copy a link to a data connection library directly from your browser, you must delete any /forms or /default.aspx strings from the URL.

1. Click OK.

Exposing the data connection library in the Office client

To make discovery and reuse of connections easier, the data connection libraries can be surfaced in the data connections UI in the Office client (currently displays in Office Excel 2007 and Microsoft Office Visio 2007). This allows users to choose a connection from the data connection library, based on friendly name and description, inline as part of the document authoring experience.

To enable data connection libraries to display directly in the client application, the data connection library must be published from the server to the client computer. The client computer must be associated with that server, or a registry key must be set directly.

Publishing the data connection library to the Office client

Publish the data connection library to the Office client

1. Launch the SSP administration console. For more information, see Step-by-step server configuration for external data.

2. In the User Profiles and My Sites section, click Published links to Office client applications, as shown in the following figure.

   

3. On the Published Links to Office Client Applications page, click New.

4. Enter the URL to the data connection library.

   Note

   The URL must be directly to the data connection library, as shown in the image and not to the forms directory or the default aspx page. If you copy a link to a data connection library directly from your browser, you must delete any /forms or /default.aspx strings from the URL.

5. Choose Data Connection Library from the Type drop-down list, as shown in the following figure.

   

6. Click OK.

   Note

   If Audience Targeting is used, only the users that are members of the specified SharePoint audience will receive the link to the data connection library. By default, if Audience Targeting is not used, all users will receive the link to the data connection library if they have permissions to view the data connection library.

Associating the client computer with the server

To enable the client computer to receive the published list of data connection libraries, it must first be associated with the portal server. This can be done by having My Site enabled, and by having a default My Site set.

Note

In most real-world deployments, this is already done and in those cases, no action needs to be taken here.

Enable My Site

1. Launch the Central Administration console. For more information, see Step-by-step server configuration for external data.

2. In the top navigation bar, click the Application Management tab.

3. On the Application Management page, in the Application Security section, click Self-service site management, as shown in the following figure.

   

4. From the Self-Service Site Management page, select a Web application (the default is acceptable in most deployments), and click On, as shown in the following figure.

   

5. Click OK.

Setting My Site as the default should be done by the end user who will be reusing connections from a data connection library.

Set My Site as the default

1. Navigate to the portal, and click My Site on the top right part of the toolbar, as shown in the following figure.

   

2. From the My Site page, select Set as default My Site from the top right of the page, as shown in the following figure.

   

3. Click OK to the dialog box that launches.

   Note

   The default My Site setting can be propagated to other servers for that user via Active Directory.

Setting a registry key to surface a data connection library in the client

This section contains steps that detail how to create a registry key that will cause a data connection library, or any other HTTP or UNC location, to surface in the connection UI.

Create a registry key to surface a data connection library

1. On the taskbar, click Start, and then click Run. Type regedit, and then press ENTER, as shown in the following figure.

   

2. Navigate to the folder at HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Common\Server Links\Published.

3. If the Published folder does not exist, you will need to create it. In this case, right-click the Server Links folder and select New Key, as shown I the following figure. Name the Key Published.

   

4. Create a new string value for the Published folder by right-clicking inside the folder and selecting New String value, as shown in the following figure.

   

5. Name the string the friendly name of the data connection library.

6. Right-click the string value, and select Modify, as shown in the following figure.

   

7. Enter the HTTP path to the data connection library in the Edit String dialog box, as shown in the following figure.

   

8. Click OK.

Step-by-step workbook configuration for external data

This section covers the common options that need to be configured from the Excel client to allow a workbook to refresh external data when loaded on Excel Services. This section also covers reusing connections from a data connection library, as well as exporting connections to a data connection library.

Creating a new connection

The simplest way to create a new data connection in an Excel workbook is to use the Data Connection Wizard. This wizard also allows you to configure the authentication options for Excel Services. The Data Connection Wizard produces .odc files, which are linked from the workbook, as described in Embedded and linked connections.

Create a new connection

1. In Excel, on the Data tab, in the Get External Data section, select From Other Sources, select From Data Connection Wizard from the drop-down list, as shown in the following figure.

   

2. Walk through each screen of the wizard, making appropriate choices for your data source. On the last screen of the wizard, click the Authentication Settings button, as shown in the following figure.

   

3. On the Authentication dialog box, select the option button that corresponds to your authentication choice. The types of authentication are described in Authentication to external data, and are shown in the following figure.

   

4. Click OK to close the Authentication dialog box, and then click Finish to end the wizard.

   Note

   This wizard produces an .odc file, and can save this file directly to a data connection library. To do this, choose the browse button on the last screen of the wizard, and type the HTTP path to the data connection library in the corresponding file dialog. When the file is in the data connection library, others can reuse it.

Reusing an existing connection from a data connection library

If a data connection library is populated with connection files and is exposed to the Office client, connections from it can be reused directly from within Excel. For more information, see Exposing the data connection library in the Office client.

Reuse an existing connection

1. From the Excel Data ribbon, select Existing Connections, as shown in the following figure.

   

   The DCL folder is shown under the Connection Files on the Network section of the Existing Connections dialog box, as shown in the following exhibit.

   

2. Each data connection library will have a separate folder. Double-click the folder for a data connection library to see the connections that are stored there, as shown in the following figure.

   

3. Select the connection that you want to use, and then click Open.

4. In the Import Data dialog box, select how you want to view your data in Excel, as shown in the following figure.

   

   Note

   The Table option is not supported in Excel Services. If it is chosen, that workbook will not load in Excel Services.

Specifying the server authentication for an existing connection

This section illustrates how to set the server authentication for connections that were previously created in an Excel workbook. For information about creating a new connection, see Creating a new connection.

Specify the server authentication

1. From the Excel Data ribbon, in the Connections section, click Connections, as shown in the following figure.

   

2. In the Workbook Connections dialog box, which shows all the external data connections that are currently being used by the workbook, select the connection that needs to be changed, and then click Properties, as shown in the following figure.

   

3. In the Connection Properties dialog box, which allows many properties of the connection to be changed, including the refresh on open setting, choose the Definition tab, as shown in the following figure.

   

4. Click the Authentication Settings button.

5. From the Excel Services Authentication dialog box, select the option button that corresponds to the preferred authentication type, as shown in the following figure. For more information about authentication types, see Authentication to external data.

   

6. Click OK to close the Excel Services Authentication dialog box, and click OK out of the workbook connections and properties dialogs.

Saving an existing connection to a data connection library

Save an existing connection

1. In the Connection Properties dialog box, on the Definition tab, click Export Connection File, as shown in the following figure.

   

2. In the File dialog box, enter the HTTP path to the data connection library, and then click Save. When an .odc file is exported in this manner, the workbook connection becomes linked to that .odc file, as described in Connections and Excel workbooks.

More about the properties on the Definition tab

The Definition tab on the Connection Properties dialog box has other properties that can affect how connections are used and behave on both the client computer and the server. All of these properties are persisted in the .odc file, which is generated by clicking Export Connection File, and will be inherited by any workbook that later uses that .odc file.

These settings are respected in both Excel client and Excel Services. This section contains notes on how two of the most important properties affect connections across client computer and server. For more information about the high-level behavior of both of these settings, see Connections and Excel workbooks.

• Connection file   This is the path to the connection file that this workbook connection maintains a link to. To change to a different connection file, choose the browse button and enter a path to a new connection file.

  Note

  Changing any settings on the Definition tab of the Connection Properties dialog box will break the link to the connection file because the copy of the connection in the workbook will no longer be an exact replica of the connection file.

• Always use connection file   This specifies whether to use the embedded copy of the connection first, or whether to always use the contents of the linked connection file to refresh data. If this check box is selected, the embedded copy of the connection information is always ignored, regardless of whether the connection file connection is successful.

Frequently asked questions

How do I get Microsoft Access data into my workbooks on Excel Services?

Workbooks that refresh data from an Access database are not supported with the features that come with Excel Services. This is because these types of solutions have problems scaling and performing in an enterprise server environment. Although it might be possible to make this work by adding the right provider to the trusted providers list and installing any required Data Source Name (DSN) files on the server, it is not supported.

How do I use other workbooks as data sources on Excel Services?

This is not supported in this version of Excel Services. Linked workbooks are an unsupported feature, and those workbooks will fail to load on the server. You might consider moving the data from the linked workbooks into a single workbook. An alternate approach is to use a user-defined function (UDF) to fetch data from a different workbook.

Why don't my Windows authentication connections work even when I have correctly configured Kerberos in my farm?

This is happening either because the user who is viewing the workbook does not have correct permissions on the data source, or because the server's Access Model was configured as a trusted subsystem. In the trusted subsystem mode, Windows authentication style connections fail automatically as the back-end calculation server does not have the full identity (security token) of the user and therefore cannot delegate to the data source.

If the credentials are not delegated, how can Excel Services make per-user permission checks on files?

Even if the Access Model is a trusted subsystem, Excel Services can still do per-user permissions checks on files stored in Office SharePoint Server 2007. Excel Services is part of Office SharePoint Server 2007, and so it is trusted to do per-user permission checks even when it cannot delegate an end user's identity; however, it does not affect how Excel Services connects to external data.

In these topologies, as described in the previous paragraph, Windows authentication–type connections do not work because the back-end application server cannot delegate the workbook viewer's identity.

What is the guidance around using local cubes on the server?

Local cubes for SQL Server 2005 Analysis Services are not supported by Excel Services. These local cubes do not scale well in an enterprise production environment because they were not designed to meet the demands of many users refreshing data immediately. It is not recommended to use these with Excel Services.

How can I have more control over how external data is accessed from the server?

Trusted file locations allow a granular level of control over how workbooks behave on the server, including how they refresh external data. When a user loads a workbook on Excel Services, the workbook trusted location is resolved to the deepest level. For example, if a workbook is loaded from https://server/site/doc%20lib/, and a set of trusted location rules exists for https://server, and a different set exists for https://server/site/doc%20lib, the rules from the latter will be applied. This makes it possible to enable scenarios where a particular set of security restrictions are applied at a very wide, or very granular, level.

For example, the entire portal can be added as a trusted location (https://server or http://).

Note

You must select the Include Children check box to allow workbook loading from all subdirectories. You might choose to configure the trusted location settings at this level to always show data warnings, and only allow refresh by using data connection libraries. Workbooks stored in a more secure document library might be enabled to load any data connections (data connection library or embedded). The external data cache time-out values might be extremely high because workbooks in this document library take a long time to refresh data and data is not updated very often.

Administrators are encouraged to add trusted file locations, and change what is allowed for each, in a way that fits their particular security requirements.

What factors should I consider regarding performance and external data?

There are a number of considerations to take into account when designing external data access from Excel Services.

• Excel Services shares data caches in the middle tier. The cache is shared when the connections are identical, and the credentials used to connect to the data source are the same. This ensures that users have rights to the same data. This means that Excel Services scales better when the same credentials are used by many users to connect to data. SSO group mappings, None where a user name/password are saved in a connection string, or None where the unattended account is used are all good authentication types for these cases. (Although it should be noted that SSO is the best option here from a security point of view.)

• For SQL Server 2005 Analysis Services and later, data caches can be shared even if individual SSO or Windows credentials are used. This is an optimization that only applies to SQL Server 2005 Analysis Services or later, and it only applies if the users are members of the same groups on Analysis Services, and if there are not any cube calculations or security that relies on an individual user's identity.

• Increase external data cache lifetimes where it makes sense. If a workbook takes a particularly long time to refresh data due to the size of a query, consider increasing the data cache timeout for that trusted location.

• Disable interactivity in the dashboard for OLAP PivotTable reports. When users interact with OLAP-based PivotTable reports, new queries are issued against the back-end data source for that user's session. In this case, that user will stop sharing the data cache. Consider publishing the workbook so that the view of the data is appropriate for most users, and disabling interactivity where it makes sense to do so.

How can I secure my data so that it is viewable only from a workbook published to Excel Services?

Excel Services takes advantage of the View Only permission set in Office SharePoint Server 2007. All users who are added to the Viewers group in Office SharePoint Server 2007 get this level of permissions by default. This only applies to files loaded from Office SharePoint Server 2007 when the trusted file location is set to be of type SharePoint.

Users who have View Only permissions have the right to load, render, interact, refresh, recalculate, and take snapshots of workbooks that are loaded on Excel Services. These users cannot access the actual file source, or contents, of the workbook directly. This means that they cannot download or save the workbook, open it directly in Excel, or access the file contents through any method other than Excel Services. This is the prescribed way to secure, "one version of the truth" workbooks.

In this case, users can see only the parts of the workbook that are marked as "Viewable on the Server," by the workbook author who uses the Publish to Excel Services feature in Excel client. By publishing the correct parts of the workbook and securing the workbook with View Only permissions, the workbook author can ensure that no data or proprietary information from the workbook is exposed when it should not be.

In cases where users are allowed to download the source of the workbook, and that user does not have direct access to the data source, if the data connection uses SSO or the unattended account to refresh on the server, the user cannot refresh that connection from Excel client. This is because Excel client cannot use the server's unattended account or SSO to refresh connections directly. The workbook author should take care in these cases to remove any cached data from the workbook, or ensure that any cached data in the workbook is allowed to be viewed by all users who might open the workbook in Excel client.

I followed the instructions in this article, but when I load my workbook on the server I see some unsupported features error talking about Query Tables. How can I fix it?

When authoring a workbook, and bringing the external data into a workbook for the first time, you are presented with an Import Data dialog box, as shown in the following figure, with choices on how to visualize the data.

When you select Table from this dialog box, a Query Table is created. These structures are not supported in Excel Services, and the workbook will not load on the server. To correct this, delete the table from the workbook. Reconnect to data, and select PivotTable Report next time.

A PivotTable report can be made to look a lot like a Query Table by using the PivotTable Tools, Design tab, from the ribbon. Try turning off Subtotals, Grand totals, and drill indicators. Then choose Tabular form for the report layout.

Another option, which requires custom code, is to use a UDF to retrieve the data, and return it to the workbook as a table. This can be rendered, loaded, and refreshed on Excel Services. This is beyond the scope of this article.

I have an ODBC connection, and therefore I do not have an .odc file. How can I use my connection with a data connection library so that it will work on Excel Services?

Excel Services works only with .odc files. If you have a connection in a workbook that you want to save to a data connection library and have work with Excel Services, you will need to export that connection as an .odc file. The Export Connection File command from the Connection Properties dialog box described in Specifying the server authentication for an existing connectioncan be used to do this as it always exports .odc files.

How do View Only permissions and .odc files in a data connection library work?

View Only permissions grant users the right to use an Excel file on Excel Services, but not from any other client application — not even Excel. The .odc files work the same way. If a workbook uses an .odc file to refresh data, the server copy of that workbook will correctly refresh for a user with View Only permissions to the .odc file. However, even if the user has permissions to open the workbook in Excel client, the connection will cannot use the .odc file to refresh from Excel client. The user can only make use of the .odc file by refreshing workbooks running on the server that uses the .odc file.

Typically, the user will have the same View Only permission restrictions on the workbook and the .odc file. If users are intended to open the workbook in Excel client but not the .odc file, the embedded connection in the workbook should not expose the content s of the .odc file. In this case, the embedded connection should not be a copy of the .odc file. Ensure the cached data in the workbook is not exposing data from the data source to which the .odc file points.

Download this book

This topic is included in the following downloadable book for easier reading and printing:

• Plan Sites and Features, Part 1

See the full list of available books at Downloadable content for Office SharePoint Server 2007.

See Also

Concepts

Plan Excel Services security
SharePoint Server 2007
Determine resource requirements to support Excel Services

Other Resources

Excel Services
Excel Services Architecture
Single Sign-on Overview
Implementing a Pluggable SSO Provider
Creating Custom Solutions with Excel Services
Kerberos Authentication in Windows Server 2003
Demo: Enable Excel Services and data connections for a SharePoint team site