Excel Services Troubleshooting - Microsoft Download Center

36 downloads 171 Views 394KB Size Report
This workbook cannot be opened because ... Access was denied by the external data ... Send feedback to [email protected]. F .... If you have multiple users.
Excel Services

Version 2.0

START

August, 2011

Data Refresh Flowchart Excel Services in Microsoft SharePoint Server 2010

User clicks an Excel workbook in a document library.

A

About this diagram This diagram shows the sequence of events that occurs when Excel Services renders a data-connected Excel workbook. There is no user interaction with the processes shown. The path followed at each decision point is based on settings within the workbook or within Excel Services that were previously configured.

Error Message

Action

Access was denied by the external data source.

Confirm that the credentials used to access the data source have a login for the data source. Also confirm that any Windows credentials used by Secure Store or the Unattended Service Account are valid and the password is not expired.

An error occurred during an attempt to establish a connection to the external data source.

Confirm that the data source is available.

An error occurred while accessing .

Confirm that the Unattended Service Account has been configured for Excel Services.

An error occurred while accessing application id from .

Confirm that the Secure Store target application referenced in the workbook exists, that the user opening the workbook is a Member of the target application, and that credentials have been set for the target application. Confirm that the ODC file referenced in the workbook is in a trusted data connection library.

Excel

Workbook in a trusted location?

This workbook cannot be opened because it is not stored in an Excel Services Application trusted location.

No

To create an Excel Services Application trusted location, contact your system administrator. OK

For information about an error, see the error table in the upper right. For information about a particular setting, follow the reference callout associated with the screenshot for that setting.

Yes

Excel Services displays the workbook to the user.

This diagram can be used to get a better understanding of how Excel Services works or to troubleshoot specific issues.

Workbook contain OLAP cube functions?

No

Workbook set to refresh on open?

No

This diagram applies to Excel Services in SharePoint Server 2010.

B The Data Connection File http:/// / used in the workbook is not in a trusted location.

Yes

Excel Services checks the Allow External Data settings for the trusted data connection library containing workbook.

Yes

Legend User initiates data refresh.

Start or End

C

Yes

Allow External Data setting?

The trusted location where the workbook is stored does not allow external data connections. The following connections failed to refresh:

None



Decision point within the backend process

Learn more about data refresh OK

Trusted data connection libraries only

Backend process performed by services within SharePoint Server 2010.

Workbook specify a published ODC file?

Yes

The data connection uses Windows Authentication and user credentials could not be delegated.

Confirm that the Claims to Windows Token Service is running on each application server in the farm where Excel Calculation Services is running Confirm that the ODC file designated in the workbook exists and that the user opening the workbook has Read permissions to the trusted data connection library where the ODC file resides. Confirm that the Allow External Data settings for the trusted file location where the workbook resides allows external data connections. Confirm that the document library where the workbook resides is configured as a trusted file location in Excel Services.

The following data connection file does not exist or you do not have permissions to it: http:////

The trusted location where the workbook is stored does not allow external data connections.

Excel

R Does workbook specify Always use connection file?

Confirm that the ODC file specified in the workbook is in an accessible trusted data connection library, or configure the trusted file location where the workbook resides to allow embedded connections.

Excel

User input

Trusted data connection libraries and embedded

The data connection path in the workbook points to a file on the local drive or is an invalid URI. Verify that the path to the data connection file in the workbook points to a data conneciton file in a Data Connection Library. The data connection uses None as the external data authentication method and Unattended Service Account has not been configured.

The data connection path in the workbook points to a file on the local drive or is an invalid URI. Verify that the path to the data connection file in the workbook points to a data conneciton file in a Data Connection Library. The following connections failed to refresh:

No



Reference point connecting the flowchart in two different locations.

This workbook cannot be opened because it is not stored in an Excel Services Application trusted location. Unable to refresh data for a data connection in the workbook. Try again or contact your system administrator.

Learn more about data refresh

Confirm that the unattended service account is configured in Excel Services Global Settings, that the designated target application exists in Secure Store, and that the Excel Services application pool account is a Member of the target application.

Confirm that the login used to access the data source (the user’s identity in the case of Windows authentication, or the Secure Store stored credentials) has Read access to the data source.

OK

Reference point to information on how to configure the setting shown.

This error may also have other causes, such as an invalid query (for example, an incorrect table name). Check the ULS logs or the data source’s logs for further information.

D Yes

Related UI No Flow Direction

Excel The following data connection file does not exist or you do not have permissions to it:

Excel Services retrieves connection information from the workbook.

Does the ODC file exist and does the user have access to it?

http:////

No

The following connections failed to refresh: Learn more about data refresh OK

Yes Excel

Workbook specify a Trusted Data Provider?

Excel Unable to refresh data for a data connection in the workbook. Try again or contact your system administrator. The following connections failed to refresh:

No

ODC in a Trusted Data Connection library?



The Data Connection File http://// used in the workbook is not in a trusted location. The following connections failed to refresh:

No



Learn more about data refresh

Learn more about data refresh OK

OK

E Yes

S

Yes

Authentication method specified in workbook?

SSS

U

None

Excel Services retrieves connection information from the ODC file.

F

ODC file specify a Trusted Data Provider?

U

No

If embedded connections are allowed and Always use connection file is not selected, Excel Services will attempt to connect using the embedded connection information. If the connection fails, the appropriate error will display. Excel Services will then attempt to use the ODC file, resuming at . If the ODC connection succeeds, the data will refresh, but the embedded connection error will still display. If the ODC file fails, the embedded connection error will display, but no additional ODC connection error will display.

R

Windows

H

G

E

Yes

Excel Services requests Windows identity of the user from SharePoint Server 2010.

User logged in as Windows user?

Authentication method specified in ODC?

Windows

Unattended Service Account configured in Excel Services?

None The ODC file can specify SSS or None. If neither value is specified, Integrated Windows authentication is used.

No Yes

SSS

Yes

No

Excel The data connection uses Windows Authentication and user credentials could not be delegated. The following connections failed to refresh:

Excel Services retrieves the Secure Store Target Application ID from the ODC file and sends it to Secure Store.

Is Claims to Windows Token Service running?

No

Learn more about data refresh

Excel Services retrieves Secure Store Target Application ID from Global Settings and sends to Secure Store.

OK

Yes

I

Windows identity of the user passed to Excel Services.

Excel

Excel Services retrieves the Secure Store Target Application ID from the workbook and sends it to Secure Store.

S

Does Target Application exist?

Does Target Application exist?

No

The data connection uses None as the external data authentication method and Unattended Service Account has not been configured. The following connections failed to refresh: Learn more about data refresh OK

No Yes Excel

Is user opening the workbook a Member of the Target Application?

J

An error occurred while accessing application id from Secure Store Service. The following connections failed to refresh:

No

Yes



No

Learn more about data refresh OK

Yes No Is the Excel Services Application Pool account a Member of the Target Application?

Have credentials been set for the Target Application?

K

Yes

Secure Store retrieves the credentials for the target application.

Excel Services impersonates the user and attempts to connect to the data source.

Excel Services impersonates the credentials from Secure Store.

Yes

J

Windows credentials?

No Kerberos delegation is required for any data source that is on a different server than the server where the Excel Calculation Service is running, if that data source is using Integrated Windows authentication Is data source external?

Excel

Unattended Service Account configured?

Yes

An error occurred while accessing . The following connections failed to refresh:

No

Yes



A

Learn more about data refresh OK

Yes

Is Kerberos delegation configured?

Windows credentials valid?

Yes

B

Excel Services impersonates the Unattended Service Account and places the Secure Store credentials in the connection string.

C

D

Yes No E No

Excel Services attempts to access the data source.

No

F

G Excel

Does user have login for data source?

Access was denied by the external data source. The following connections failed to refresh:

No

No



Secure Store retrieves the credentials for the Unattended Service Account target application.

Do Secure Store credentials have login for data source?

Learn more about data refresh

H

OK

I Yes

L

Does login have Read access?

Yes

Excel

No

Unable to refresh data for a data connection in the workbook. Try again or contact your system administrator. The following connections failed to refresh:

No

M

Does login have Read access?

Learn more about data refresh OK

N

N Yes

This example shows a SQL Server data source. The exact sequence of events may vary depending on the data source used. In the event of an authentication failure, the error message displayed will vary depending on what error was sent to Excel Services by the data source. For data sources that do not return an Access Denied error (for example, OLAP providers), only the Unable to refresh data error will be displayed by Excel Services.

J

K

L

Yes

Excel Services refreshes the data and displays the report.

M

N

© 2011 Microsoft Corporation Send feedback to [email protected]

END

To configure a trusted file location, in Central Administration, click Manage Service Applications, click the Excel Services service application, and then click Trusted File Locations. To configure a workbook to refresh when it is opened, in Excel, on the Data tab, click Connections, select the connection, click Properties, and then on the Usage tab, select the Refresh data when opening the file check box. To configure external data settings, in Central Administration, click Manage Service Applications, click the Excel Services service application, click Trusted File Locations, click the trusted file location where your workbook resides, and then select a setting for Allow External Data. To set connection properties or specify an ODC file for an Excel workbook, in Excel, on the Data tab, click Connections, select the connection, and click Properties. To configure a trusted data provider, in Central Administration, click Manage Service Applications, click the Excel Services service application, and then click Trusted Data Providers. To configure a trusted data connection library, in Central Administration, click Manage Service Applications, click the Excel Services service application, and then click Trusted Data Connection Libraries. To configure Excel Services Authentication Settings for a data connection, in Excel, on the Data tab, click Connections, select the connection, click Properties, select the Definition tab, and then click Authentication Settings. To configure the unattended service account in Excel Services, in Central Administration, click Manage Service Applications, click the Excel Services service application, click Global Settings, and type the name of the Secure Store target application you want to use for the Unattended Service Account in the Application ID text box. To start the Claims to Windows Token Service, in Central Administration, click Manage Services on Server, select the Server where Excel Calculation Services is running, and click Start for Claims to Windows Token Service. You must start the Claims to Windows Token Service on each server that is running the Excel Calculation Service. To set the Members (Credential Owners) of a Group type Secure Store target application, in Central Administration, click Manage Service Applications, click the Secure Store Service service application, select the check box for the target application you want to modify, click Edit in the ribbon, and step through the wizard to the Membership Settings page. For an Individual type target application, you set the Credential Owner when you set the credentials. See I, below. To set the credentials for a Secure Store target application, in Central Administration, click Manage Service Applications, click the Secure Store Service service application, select the check box for the target application you want to modify, and click Set in the Credentials section of the ribbon. To create a login for the user accessing the workbook, in SQL Server Management Studio, expand Security, right click Logins, and click New Login. If you have multiple users accessing the same data, we recommend you use an Active Directory group for the SQL Server login rather than individual logins for each user. To create a login for the Secure Store credentials, in SQL Server Management Studio, expand Security, right click Logins, and click New Login. To set Read permissions for the data access account, in SQL Server Management Studio, expand Security, expand Logins, right click the login for the data access account, click Properties, click User Mapping, select the Map check box for the appropriate database, and then select the db_datareader check box.