Elon's Blog

Stuff that may be of use to someone…

Data Refresh Error using Excel Services in MOSS 2007

Posted by Elon B. on October 13, 2008

I created an Excel Report in MOSS 2007, which received data from a data connection to a MS Analysis Services Cube, stored locally on the same machine as the SharePoint Server. Everything worked fine until I tried to select an item from the PivotTable defined in the report. At that point I received the following “Data Refresh” problem.

Data Refresh Error

Data Refresh Error

To get the Report to refresh, I first had to ensure that the Data Connection Library that I had created and referenced in my Excel Report, appeared in my “Trusted Data Connection Libraries”.
  1. Go to the Sharepoint Central Administration Site.
  2. Under the “Shared Services Administartion” link, select your Shared Services Provider link.
  3. Under “Excel Services Settings“, select “Trusted data connection Libraries
  4. Make sure your Data Connection Library appears in the list, otherwise add the location.
Now, this didn’t solve the data refresh problem on its own. My data connection used “Windows Authentication” to connect to the SSAS Cube. When using this mode you need to be aware of the following.
Ensure Excel Services is configured to use the Delegation access model rather than the Trusted Subsystem model. You must set this configuration even if Excel Services and the back-end data source are running on the same server. To configure Excel Services to use the Delegation access model, use the Stsadm command-line tool. Type the following commands at a command prompt on the server, replacing sspname with the name of your Shared Services Provider (SSP):
stsadm -o set-ecssecurity -accessmodel delegation -ssp sspname
                e.g. stsadm -o set-ecssecurity -accessmodel delegation -ssp "Shared Services"

stsadm -o execsvcadmjobs
I can’t claim full credit for this nugget of information, and must direct you to the following link entitled “Ten Tips for Using SharePoint Server 2007 with Excel Services“, which highlighted me to the Windows Authentication issue, plus other interesting notes.

Please Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: