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.


SharePoint Error Tracing

Tracing the cause of errors in SharePoint can leave you wondering where to start. In this article I provide some tips on preparing your SharePoint farm with error tracing in mind.

In this example I will assume we have a SharePoint farm with two web front ends (with network load balancing) and an application server. The same method can be used on larger farms.

This method is useful for identifying problems that occur on one server in your farm but not others. The are usually intermittent issues that occur randomly for some users and not others (due to different Web Front Ends servicing the requests).

Configuring SharePoint and IIS 

Alternative Access Mappings

  • In Central Admin, configure alternative access maps for each server in the farm using the servers actual name e.g. SP_WFE1, SP_WFE2, SP_APP1
  • Test by connecting to your SharePoint servers using HTTP://servername

Configured IIS

  • Logon to each server and modify the IIS Bindings to include a host header with the servers actual name (matching the alternative access mapping)
Get ULSViewer

Download ULSviewer from MSDN and put a copy on each web front end.

Trapping errors

The trick to catching errors is to reproduce the error on a known host and capture the error with ULSviewer.

  • Choose a server e.g. SP_WFE1
  • Logon to the server and run ULSviewer
  • Reproduce the error using SP_WFE1 rather than the normal SharePoint URL
  • Copy the error correlation code from the error message
  • Create a filter in ULSviewer
  • Filter by Correlation and paste the code copied in the step above
  • If multiple errors are returned in the log, look for the words error or exception (usually)
  • Highlight the error and full details will appear in the top of ULSviewer pane.
I have used this method to identify issues with web-parts, CSS mappings and service applications.
Note that ULSviewer service can be installed on multiple servers and central monitored. Where is the fun in that…