PowerPivot for SharePoint Correlation Error on Manual Refresh

Here’s an interesting issue that resolved recently on a SharePoint 2010 Server running PowerPivot.

The symptoms of the issue was data not refreshing and when attempting to manually refresh the data from the PowerPivot Gallery resulted in a correlation error.

Using the ULS Viewer I found the following error, along with several other ULS log messages indicating permission issues.

Application error when access /_layouts/PowerPivot/ViewDataRefreshHistory.aspx, Error=Access Denied
at Microsoft.AnalysisServices.SharePoint.Integration.GeminiPageBase.CheckItemPermissions()  
at Microsoft.AnalysisServices.SharePoint.Integration.GeminiPageBase.OnLoad(EventArgs e)  
at System.Web.UI.Control.LoadRecursive()  
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

My first thoughts were that the problem was with the credentials being passed to the data source from Secure Store. After carefully checking Secure Store credentials and manually checking that the username and password could access the data source, I was happy this wasn’t the issue.

I had tried navigating to the /_layouts/PowerPivot URL but got 403 access denied message.

Humm, weird but then again strange things happen in SharePoint sometimes. Rebooted the server, again no change.

Resolving the issue:

The problem was the SharePoint Web Application lock state was read-only, preventing anything writing data to SharePoint.

This can happen for a number of reasons but in this case it was possibility caused by a failed backup with a SharePoint agent. The agent locks SharePoint at the web application level to prevent updates while it backs-up the web application. It should unlock the web application again when it’s finished, but perhaps it didn’t this time.

This Commvault article explains the root cause: http://forum.commvault.com/forums/thread/27583.aspx

The Fix:

Logged on to the SharePoint server as a Farm Administrator. Run the following commands:

stsadm -o getsitelock -url http://sharepoint
<SiteLock Lock="readonly" />
stsadm -o setsitelock -url http://sharepoint -lock none
Operation completed successfully.
stsadm -o getsitelock -url http://sharepoint
<SiteLock Lock="none" />

Bingo! Happiness restored and I can now manually refresh PowerPivot and scheduled updates run also.