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.
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.
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:
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,
You must be logged in to post a comment.
We provides you the best Services in our themes.
Click on the link below to see a full list of clients which we have developed solutions and provided consultancy for.
We are solution-centered and not application-centered.
Being creative and having fun and yet still delivering a fantastic service is the center of our values.
TFS Consulting Services guarantees delivery that is within budget and deadline or you engage us for free.
Implementing IT solution does not have to be difficult. TFS Consulting Services has a lot of resources on planning and methodologies that will ensure successful delivery of your IT solution. TFS Consulting Services has been around in the web industry for more than 10 years and has experienced all the successes and failures of various type of IT deployment.
Do you need a technical resource? TFS Consulting Services can also provide you with technical resource for developing ASP.NET (C# and VB.NET), SharePoint (2003, 2007, 2010, 2013) and MS CRM applications. Our resource is an Microsoft Certified Personnel (MVP) and Microsoft Certified Technology Specialist (MCTS) in all ASP.NET, SharePoint and CRM.
Make sure your IT implementation is robust and scalable. TFS Consulting Services can provide consulting and advice on industry’s best practice on various web-related areas such as website security, design and usability, application-specific (such as SharePoint)’s best practice, Search Engine Optimisation (SEO), coding standards and many others.
Finally TFS Consulting Services provides you with solution development service. We mainly work with Microsoft technologies (ie. .NET and SQL Server), however we are also capable of developing with PHP and MySQL. If you ever need any business process automation, integration and solution development work, we are the trusted expert you should go to.
For more detailed service offerings please visit our Solutions page.
+61 404 457 754
© TFS Consulting Services 2024. All rights reserved.