PowerPivot workbook with a PowerPivot connection to a SharePoint list cannot be refreshed manually in the browser (Data > Refresh All Connections) using Windows authentication in SharePoint 2013


Issue

As we all know 🙂 with SharePoint 2013, manual data refresh in the browser (Data > Refresh All Connections) for PowerPivot workbooks will refresh data all the way back from the original PowerPivot data source, which is specified in the PowerPivot workbook's PowerPivot connection. This functionality is different with SharePoint 2010, where manual data refresh in the browser (Data > Refresh All Connections) will refresh data only from the cube (in its current state) that exists for that PowerPivot workbook in the PowerPivot instance of Analysis Services.

The Issue: With SharePoint 2013, we have found that a PowerPivot workbook with a PowerPivot connection to a SharePoint list cannot be refreshed manually in the browser (Data > Refresh All Connections) using Windows authentication in SharePoint 2013.

Many have tried to get this refresh functionality working using a number of different Service Principle Name and constrained delegation settings and configurations, and so far efforts have been unsuccessful with getting delegation to work.

Workaround

In this scenario, the workaround is to use a Secure Store ID for the workbook list connection, instead of using Windows authentication.

Of course, the SSS ID (mine was PowerPivotUnattendedAccount) will need to have credentials set for it that have permissions to the SharePoint list that is being refreshed from.

Comments (1)

  1. pwadirector says:

    Finally I found this blog article, but I have error dialog after refreshing all connections on Excel online.

    Let me ask one question, does SSS ID contain Org ID(email address) and its password?
    thanks in advance.

Skip to main content