I kept getting this error when setting up a demo with Excel Web Services in MOSS 2007 with the Adventure Works OLAP sample cubes.
Data Refresh Failed
Unable to retrieve external data for the following connections: [Date source name]
The data source may be unreachable, may not be responding, or has denied access. The workbook location may also prevent data refresh.
I was finally able to get it to work, but it was a bit of a hack. While creating the data connection, specify the connection type as None and specify the credentials in Shared Services -> Excel Settings, and specify an Unattended Service Account that has permissions to the cubes.
Otherwise it tries to delegate credentials from IIS to Analysis Services to SQL and it doesn’t work. There is probably a better way, but I haven’t found it yet.