9 Apr 2010

Configuring Excel Services & PowerPivot on multi server Topology

I have been working with a couple of colleagues, James Dawson and Russell Seymour , on installing and configuring PowerPivot within a SharePoint 2010 beta 2 farm.

We used the following instructions to install PowerPivot on one of the application tier server: http://msdn.microsoft.com/en-us/library/ee210616(SQL.105).aspx.

After following the instructions we were able to verify the installation as we had a SharePoint site with PowerPivot gallery.  We managed to uploaded and open a couple of Excel workbooks, with PowerPivot embedded, within the web browser.  Also we were able to connect to the PowerPivot service and could see the PowerPivot data files loaded. When I tested the slicers I got greeted with the following error:

The data connection uses Windows Authentication and Excel Services is unable to delegate user credentials. The following connections failed to refresh: Sandbox

We did some research and found the following blog post: http://powerpivotgeek.com/2009/12/11/excel-services-delegation/.  I implemented the changes it recommended to our Excel workbooks and tried again this time getting the following error:

The data connection uses None as the external data authentication method and Unattended Services has not been configured on Excel Services. The following connections failed to refresh: SandboxHowever at the time of this error we had already configured the Excel Services with the Secure Store Service application identity that we had created. 

The problem proved to be with the configuration of all the service applications. At the start all the services applications had their own application pools with there own domain users. 

The solution in the end was to run all the services applications under the following app pool configuration: SharePoint Web Services System.