INTRODUCTION

I have some Excel files in SharePoint that connect to few external Data Sources such as SQL and other Excel files using Power Query.

Upon clicking the “Refresh Data Connection” in Excel Service ie. web browser, I get a pop-up error saying that my data sources can’t be refreshed.

Then I went to the SharePoint server logs and found the following:

For the SQL Data Source, I was trying to retrieve data from SQL Server INTRANET from a database called SharedServices and table tbl_BatchJob.

12/08/2015 14:00:00.87 w3wp.exe (0x1554) 0x4B10 Excel Services Application External Data ai2vp Information Unable to use Integrated authentication. [Session: 1.V24.2184MuVQ+os7CXPhW5yu8ZsK14.5.en-US5.en-AU36.a69e74de-d02f-412c-ae23-26090e096edc1.A1.N User: 0#.w|tfs\segorot] 9a25499d-86cc-30ab-bad7-2437e19f6f3f
12/08/2015 14:00:00.87 w3wp.exe (0x1554) 0x4B10 Excel Services Application External Data ai2v2 Information Refresh failed for ‘INTRANET SharedServices tbl_BatchJob’ in the workbook ‘http://intranet/sites/PowerPivot/PowerPivot Gallery/Tommy Test.xlsx’. [Session: 1.V24.2184MuVQ+os7CXPhW5yu8ZsK14.5.en-US5.en-AU36.a69e74de-d02f-412c-ae23-26090e096edc1.A1.N User: 0#.w|tfs\segorot] 9a25499d-86cc-30ab-bad7-2437e19f6f3f

Then for the Power Query I get the following:

The following system error occurred:  Class not registered   The provider ‘Microsoft.Mashup.OleDb.1’ is not registered.

For both Data Sources they refreshed OK if I open the workbook in Excel desktop application.

RESOLUTION

SQL Issue

With the SQL Issue, it was actually an authentication issue. To fix this:
1. Open your workbook in Excel Desktop
2. Click Data > “Connections”
3. Select your data source then click “Properties” > “Definitions”
4. Click “Authentication Settings”
5. Select “None”. By doing this, it will force Excel to try to authenticate using the “Excel Services Application Unattended Service Account” specified in Secure Store Service application.
6. Go to your Secure Store Service Application and click “Set Credentials” on “Excel Services Application Unattended Service Account”. Specify a Windows user.
7. Ensure that Windows user has DBO access to your database.

Done.

Power Query Issue

With the Power Query Issue, unfortunately SharePoint 2013 On-Premise does not support Power Query at this stage. It’s only supported in SharePoint Online. So the only resolution is to change your Power Query to use PowerPivot.

And, you’ll also need to install the following files on your SharePoint server:

http://www.microsoft.com/download/en/details.aspx?id=13255

http://www.microsoft.com/en-us/download/details.aspx?id=23734

Otherwise, it will then give you this error when it tries to refresh PowerPivot data source:

The following system error occurred:  Class not registered

The provider 'Microsoft.ACE.OLEDB.12.0' is not registered.

After you install the drivers above ensure you do IISRESET.

Hope this helps,

Tommy