Problem:
As talked about on http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1104110&SiteID=17 :
<-- cut [ When accessing the sheet via Excel Services Web access, it displays properly, but refuses to refresh, errorring with : "Unable to retrieve external data for the following connections: xxxxx
The data sources may be unreachable, may not be responding, or may have denied you access.
Verify that data refresh is enabled for the trusted file location and that the workbook data authentication is correctly set" <-- paste ]
Solution:
I had the same problem when I was trying to refresh data in a pivot table based on sample data in the Adventure Works analysis cube, even though I could refresh standard SQL data (I pulled data from the MOSS databases).
This got me to thinking it might have something to do with access accounts (had a similar problem with PWA) and found that this ... highly unusual method worked:
- Even though you may not have anonymous access enabled on your "Office Web Server Services" web site in IIS you still need to make sure that the account you would use is not a network service account, but rather a domain account - i know, it makes no sense! (well to me anyways).
- So, go to IIS (on all servers running excel services) and enable IIS anoymous access.
- Set the anonymous access account to use a domain account.
- Save and close.
- Check your workbook again, it should be working now, if it is UNCHECK the IIS anonymous access for the "Office Web Server Services".
All should be happy once again in the land of MOSS.
Regards,
just doug (formerly bobthebuilder)
"I may not know what fixed it, all I know is that I have put the hammer down" - just doug 1998