Data Refreshing issue Access Denied to Analysis Services Database Contact Administrator in PowerPivot in SharePoint BI services

Issue:

As part of SharePoint 2013 reporting services, I was trying to render SSAS cube data in SharePoint BI (Business Intelligence) site library. But, when click on “Refresh All Connections” to fetch the latest data from SSAS cube I was experiencing below issue:

powerpivot data refresh error

Diagnosis this issue:

To know the issue in Excel Services we need to enable “Diagnostic Logging” which will log the connection / authentication as part log file.

 

Steps to enable Diagnostic Logging:

1. Log on to WFE with FARM account

2. Launch Central administration

3. Navigate to Diagnostic Logging 

Central Administration -> Monitoring -> Configure Diagnostic Logging

powerpivot data refresh error access is denied

4. Select “Excel Services Application” as part of Diagnostics Logging page and click “OK” at the end of the page.

sharepoint 2013 powerpivot data refresh error

5. By enabling this SharePoint will log the actual error message in log file as part of 15 Hives, the default path for log files is:

%CommonProgramFiles%\Microsoft Shared\Web Server Extensions\15\LOGS\

 

6. Soon you enable diagnostics logging from client/user machine “refresh all connections” in excel (as shown in picture-1)

 

Note: This will give the same error but SharePoint will log the detailed error in log file

 

7. Navigate to log folder and open the latest log file and search for “Excel Services Application External Data” when I do it I found the below error message.

05/14/2018 18:07:55.84    w3wp.exe (0x37BC)    0x552C    Excel Services Application    External Data    ai2vl    Information    The workbook ‘http://SharePoint13:9535/PowerPivot Gallery/SaleOrders.xlsx’ attempted to access external data using the unsupported provider ‘Provider=MSOLAP.7;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=SaleOrders;Data Source=SharePoint13\tab;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Update Isolation Level=2’. [Session: 1.V23.108d20qHa37P9g/cpouEEz514.5.en-US5.en-US36.94632022-8f93-4af2-9a9a-fd10c6df64721.A1.N User: 0#.w|sharepoint13\spadmin]    f291659e-4995-e0b2-9f14-af19942f9e12

 

Error message clearly says that “attempted to access external data using the unsupported provider ‘Provider=MSOLAP.7”

 

Solution:

As the error message shows that excel services using a unsupported provider we need to ensure that, MSOLAP.7 provider is part of trusted data providers.

 

Follow the below steps to add the new trusted data provider:

1. Log on to WFE with FARM admin account

2. Open “Excel service application” as part of “Manage Service application”.

Central Administration -> Application Management -> Service Applications -> Manage Service Applications

powerpivot pivot table not refreshing

manage powerpivot data refresh sharepoint 2013 error

3. Select “Trusted Data Provider” to add the new provider

sharepoint 2016 powerpivot data refresh error

4. Validate the list of trusted providers for “MSOLAP.7” and then click on “Add Trusted Data Provider”

manage powerpivot data refresh sharepoint 2013 error Tutorial

5. Enter the provider details as shown below and click on “OK” button

sharepoint 2016 powerpivot data refresh error Tutorial

6. Once the provider is added go back to user / client machine it will work seamlessly.