Excel Services & PowerPivot for SharePoint

Troubleshooting Excel Services and PowerPivot for SharePoint rendering and refreshing in the browser.

PowerPivot 2013 for SharePoint - "External Data Refresh Failed" using Interactive Data Refresh via "Use the authenticated user's account"

PowerPivot 2013 for SharePoint - "External Data Refresh Failed" using Interactive Data Refresh via "Use the authenticated user's account"

  • Comments 2
  • Likes

When refreshing in the browser you may come across this error:

"External Data Refresh Failed
An error occurred while working on the Data Model in the workbook. Please try again.
We were unable to refresh one or more data connections in this workbook.  The following connections failed to refresh:
<Connection>"

This happens when you choose Data > "Refresh Selected Connection" or "Refresh All Connections"

If the SharePoint user has permissions to the PowerPivot Data Source AND that data source accepts Windows Credentials (SQL 2012/SSAS 2012) you can pull real-time data!  This is achieved via the "Use the authenticated user's account" found in the Excel (PowerPivot) workbook > Data > Connections > Properties.

In "What is PowerPivot for SharePoint? Part 2".  I discuss the 3 types if Refresh in PowerPivot 2013.  With the "Interactive Data Refresh" you are passing the User's Windows Credentials to the backend data source (so make sure they have permissions to the data source.

Configuration Steps:

(*Disclaimer: This is meant to be a template to set up delegation and may not explicitly apply to your situation.  Modifications may be necessary.)

To get this to work, you need to first make sure you are not opening the workbook in the "Microsoft Excel Web App" (see our previous blog).

To refresh external data in PowerPivot 2013, you need to follow several steps to configure your environment & set up Delegation.

1. Verify that the PowerPivot instance is at least 11.0.3000 (SQL 2012 SP1) and that it is running in SharePoint mode (SQL Management Studio > Right Click the PowerPivot Instance > Properties):

2. Collect the account running "SQL Server Analysis Services (POWERPIVOT)":

To do this, log on to the server running PowerPivot > Start > Run > Services.msc and locate "SQL Server Analysis Services (POWERPIVOT)", you will see the account in the "Log On As" column (write that account down, you will need it shortly).

 

3. On the Analysis Services Server (PowerPivot Server) running in SharePoint mode, Add the Analysis Services service account (account running "SQL Server Analysis Services (POWERPIVOT)") to the "Act as part of the operating system" privilege:

a. Start > Run “secpol.msc”
b. Click Local Security Policy, then click Local policies, and then click User rights assignment.
c. Add the service account.
 

4. Restart Excel Services and Reboot the Analysis Services server (the Server running PowerPivot). 

 

If the backend data source is on the same server as the Analysis Services PowerPivot instance, delegation is not required and you are done at this point!

5. Collect the account running "SQL Server (MSSQLSERVER)" or "SQL Server Analysis Services (<Instance>)":

"SQL Server (MSSQLSERVER)": 

Log on to the server running SQL > Start > Run > Services.msc and locate "SQL Server (MSSQLSERVER)", you will see the account in the "Log On As" column (write that account down, you will need it shortly).

"SQL Server Analysis Services (<Instance>)":

Log on to the server running SQL > Start > Run > Services.msc and locate "SQL Server Analysis Services (<Instance>)", you will see the account in the "Log On As" column (write that account down, you will need it shortly).

6. Add these SPNs to the account running the data source your workbook is connecting to.  Either "SQL Server (MSSQLSERVER)" or "SQL Server Analysis Services (<Instance>)":

Account Running SQL Server:

MSSQLSVC/<SQLServer_Name>
MSSQLSVC/<SQLServer_Name.FQDN>

Account Running SQL Server Analysis Services:

MSOLAPSvc.3/<SQL_Server_Analysis_Services_Server_Name:Instance>
MSOLAPSvc.3/<SQL_Server_Analysis_Services_Server_Name.FQDN:Instance>

******Important Note:  If Analysis Services has a named instance other than ("MSSQLSERVER)" (this is the default Instance) you will need include that in the Service Principal Name.  In the below example, I will use the instance name "TABULAR".

SQL Server:

MSSQLSvc/<SQL_Server_Server_Name:Instance>
MSSQLSvc/<SQL_Server_Server_Name.FQDN:Instance>

Example:

MSSQLSvc/SQLSvr:TABULAR
MSSQLSvc/SQLSvr.contoso.com:TABULAR

SQL Server Analysis Server:

MSOLAPSvc.3/<SQL_Server_Analysis_Services_Server_Name:Instance>
MSOLAPSvc.3/<SQL_Server_Analysis_Services_Server_Name.FQDN:Instance>

Example:

MSOLAPSvc.3/SSASSvr:TABULAR
MSOLAPSvc.3/SSASSvr.contoso.com:TABULAR

******Important Note 2: If the Analysis Services Server has a Named Instance other than (MSSQLSERVER) (which is Default), like "TABULAR".  You will need to add MSOLAPDisco.3 SPNs to the account running the SQL Browser service (Example: Contoso\BrowserSvc). “Disco” is short for “Discovery”.  This needs to be set so the Browser Service can discover SSAS.

MSOLAPDisco.3/<SQL_Server_Analysis_Services_Server_Name>
MSOLAPDisco.3/<SQL_Server_Analysis_Services_Server_Name.FQDN>

Example:

MSOLAPDisco.3/SSASSvr
MSOLAPDisco.3/SSASSvr.contoso.com

Article: More Information regarding the MSOLAPDisco.3 SPNs can be found in the below article:

An SPN for the SQL Server Browser service is required when you establish a connection to a named instance of SQL Server Analysis Services or of SQL Server
http://support.microsoft.com/kb/950599

7. Constrain Delegation between the account running "SQL Server Analysis Services (POWERPIVOT)" and "SQL Server (MSSQLSERVER)" or "SQL Server Analysis Services (<Instance>)":

In Active Directory Users and Computers > Account 1> Properties > Delegation Tab > Trust this user for delegation to specified services only > Use any authentication protocol > Add… > Users or Computers… > Account 2 > Check Names > OK > “Select All” Available Services > OK > OK

Example:

Additional Notes:

Excel Services sends the Analysis Services server a process command that instructs the server to impersonate a user account. To obtain system rights sufficient to perform the user impersonation-delegation process, the Analysis Services service account, requires Act as part of the operating system privilege on the local server. The Analysis Services server also needs to be able to delegate the user's credentials to data sources. The query result is sent to Excel Services.

Delegation from the Excel Services service account or from Claims to Windows Token Service (C2WTS) to the Analysis services instance is not required.  Therefore no configuration for KCD from Excel Service or C2WTS to PowerPivot Analysis Services service is necessary. 

If the backend data source is on the same server as the Analysis Services PowerPivot instance, delegation is not required. 

 

 

If you do not have a SPN set for the account running the "SQL Server Analysis Services (POWERPIVOT)", the Delegation tab will not be present for that Account in Active Directory.  I propose adding the dummy SPN, Http/dummy.

Delegation from the Excel Services service account or from Claims to Windows token service (C2WTS) to the Analysis services instance is not required. Therefore no configuration for KCD from Excel Services or C2WTS to PowerPivot AS service is necessary. 

Comments
  • Hi, i got the error External Data Refresh Failed. and i'm new in this field. does this post applicable to Sharepoint Online or online for Sharepoint on-premise?

  • Adibah, this is for On Prem only and does not apply for Sharepoint Online.

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment