Issue: You get the following error:

An error occurred while working on the Data Model in the workbook. Please try again. We are unable to refresh one or more data connections in this workbook.

Depending on what data provider you are using for your external data connection you will get a different error:

For SQL Native Client:

Failed to create an external connection or execute a query. Provider message: Out of line object 'DataSource', referring to ID(s) '20102481-39c8-4d21-bf63-68f583ad22bb', has been specified but has not been used.  OLE DB or ODBC error: A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.; 08001; SSL Provider: The requested security package does not exist ; 08001; Client unable to establish connection; 08001; Encryption not supported on the client.; 08001.  , ConnectionName: ThisWorkbookDataModel, Workbook: book1.xlsx.

For Microsoft OLE DB Provider for SQL Server:

Failed to create an external connection or execute a query. Provider message: Out of line object 'DataSource', referring to ID(s) '6e711bfa-b62f-4879-a177-c5dd61d9c242', has been specified but has not been used. OLE DB or ODBC error. , ConnectionName: ThisWorkbookDataModel, Workbook: OLEDB Provider.xlsx.

For .NET Framework Data Provider for SQL Server:

Failed to create an external connection or execute a query. Provider message: Out of line object 'DataSource', referring to ID(s) 'f5fb916c-3eac-4d07-a542-531524c0d44a', has been specified but has not been used.  Errors in the high-level relational engine. The following exception occurred while the managed IDbConnection interface was being used: Could not load file or assembly 'System.Transactions, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies. Either a required impersonation level was not provided, or the provided impersonation level is invalid. (Exception from HRESULT: 0x80070542).  , ConnectionName: ThisWorkbookDataModel, Workbook: NETProvider.xlsx.

Resolution: Configure Analysis Services Server in SharePoint mode service account to “Act as operating system” as follows:

1. On the Analysis Services Server running in SharePoint mode, Add the Analysis Services service account to the "Act as part of the operating system" privilege:

a. Run “secpol.msc”

b. Click Local Security Policy, then click Local policies, and then click User rights assignment.

c. Add the service account.

2. Restart Excel Services and reboot the Analysis Services server.

2 thoughts on “External data refresh failed error for PowerPivotExcel Services

    1. What error do you get in ULS logs? Are you getting any one of the three errors in logs mentioned above? I was getting one of them in logs which got resolved by this. Also do not forget to restart excel services and PowerPivot analysis service at the end.

Leave a Reply

Your email address will not be published. Required fields are marked *