Solving the “Data Refresh Failed” error message.
I’m pretty convenient with SharePoint and MOSS but sometimes get bitten in the rear if I try to do something on autopilot. While working on a presentation about the combination of SharePoint and BizTalk I wanted to insert an Excel sheet in SharePoint to show some BAM data. This is simply a cube on analysis services. After doing all the stuff I normally do I expected all to work but BOOM!:
Oh no! Let’s go through the steps again.
The standard steps to perform
- Use STSadm to configure the use of delegation
- Create a Data Connection Library and Report Library
- Configure the SharedServiceProvider to trust my Data Connection Library
- Configure the SharedServiceProvider to trust my Report Library
- Create a Data Connection with Excel
- Export the Data Connection
- Save the Data Connection File to our trusted library and approve it
- Create an Excel sheet using the connection file from the library
- Design and publish the sheet to our trusted file location
But still it doesn’t work. I can use the connection from the library in Excel. I can view the sheet, but I can’t update the connection!
How come it doesn’t work!
Viewed the SharePoint logs. Nothing in there. Viewed the event viewer. Nothing in there.
Hmmm… lets use SQL server profiler to see if we actually get to login to the database. Strange, I don’t see a trace for Audit login when I try to connect. Ok this tells us that it’s a SharePoint issue at first.
Ok, maybe it’s permissions. Allowed all authenticated users “Full control” to both the data connection library and the excel sheet but this is not the issue. Didn’t think it would be cause View rights are enough but ok. What else?
I forgot something!
Going through a walkthrough can sometimes lead you to a forgotten step. If you decide to do a walkthrough please don’t be ignorant and perform and check each and every step and don’t skip one because you think you’ve done it correctly. The walkthrough I used is the Plan external data connections for Excel Services its a great resource which explains a lot of the details of Excel Services. I almost decided to skip the first step but fortunately I didn’t.
The first step tells you to go to the ‘Trusted data providers’ section and add your provider. I never had to do that before because this library contains most of the standard data providers already.
This time however I was developing all this on Windows Server 2008 with SQL Server 2008 and as you can see in one of the images above(step 6 and 8) we use the MSOLAP.4 provider and that’s not in there!!!
After searching for all this time it was just a matter of adding the data provider name to the trusted data provider library and it al worked like a charm!
Conclusion
I do hope this story / walkthrough is of any help to y’all. I took me quite some time to figure out. Next time I’ll definitely take all steps in a walkthrough!
Cheers,
Wes