Data Refreshing issue Access Denied to Analysis services Database. Contact Administrator SharePoint Business Intelligence (BI) services

manage powerpivot data refresh sharepoint 2013 error tutorial
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
powerpivot data refresh error
Reason for this issue:
In SharePoint BI application service this issue is called “Double Hopping Issue” because when we perform refresh all connections first hope is to SharePoint Server and the second hop is to SQL DB. During these hopes the authentication will not be carry forwarded and hence we get this error.



powerpivot data refresh error access is denied
powerpivot data refresh error access is denied
Solution:
To address this issue we need to associate “Excel Services” with a dedicated “Target Application” in Secure Store service. Follow the below steps to create “Target Application”.
1. Log on to SharePoint application server and open Central Administration as administrator.
2. Click on “Secure Store Service Application” by following the below path
Central Admin -> Application Management -> Manage Service applications (under Service Applications)
sharepoint 2013 powerpivot data refresh error
sharepoint 2013 powerpivot data refresh error

3. Click on “New” to create a new Target Application

powerpivot pivot table not refreshing
powerpivot pivot table not refreshing

4. Enter all the mandatory information as advised in the below screen shot

manage powerpivot data refresh sharepoint 2013 error
manage powerpivot data refresh sharepoint 2013 error

5. Ensure that password is selected with masked before you click on next


sharepoint 2016 powerpivot data refresh error
sharepoint 2016 powerpivot data refresh error

6. Add all the required accounts in administrators section and add “Everyone” for member so that anyone can connect and get the data. Once you are confident with the details click OK to create the “Target Application”.

Note: you can add new admins and members anytime even after you create the target application.

powerpivot data refresh error tutorial
powerpivot data refresh error tutorial
7. Now we have created Target Application successfully let us associate this to Excel services to avoid the authentication issue.
8. Select “Excel Services” service application by following the below path
Central Admin -> Application Management -> Manage Service applications (under Service Applications)
powerpivot data refresh error access is denied tutorial
powerpivot data refresh error access is denied tutorial

9. Select “Global Settings” as part of Manage Excel Service Application

sharepoint 2013 powerpivot data refresh error tutorial
sharepoint 2013 powerpivot data refresh error tutorial

10. Now SharePoint will show all the properties associated with the application service. Go all the way to the end of the page and enter “Excel Services” (the Target Application we created just now) and click OK.

powerpivot pivot table not refreshing tutorial
powerpivot pivot table not refreshing tutorial

11. Ensure that you are this target application while creating “Data Connection” in excel.

manage powerpivot data refresh sharepoint 2013 error tutorial
manage powerpivot data refresh sharepoint 2013 error tutorial

Hope this helps happy administrations and BI in SharePoint !!!


You May Also like the Following SharePoint Online Tutorials:

About Krishna Vandanapu

I am Krishna.Vandanapu a SharePoint architect working in IT from last 12 years, I worked in SharePoint 2007, 2010, 2013, 2016 and Office 365. I have extensive hands on experience in customizing SharePoint sites from end to end. Expertise in SharePoint migration tools like Sharegate, Doc Ave and Metalogix. Migrated SharePoint sites from SharePoint 2007 to 2010 and 2010 to 2013 several times seamlessly. Implementing CSOM with Microsoft best practices. Spent quality time in configuring SharePoint application services like User Profile, Search, Managed Meta data services etc. Now exploring SharePoint Framework and SharePoint 2019

View all posts by Krishna Vandanapu →