I had a lot of trouble with Excel Services. I wanted my Excel file to refresh data from SQL database. However, the following error appeared constantly:
Unable to retrieve external data for the following connections:
Connection_Name
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.
Here are a few tips you should consider to fix this:
1) Make sure you have created Excel files as in tutorials on: http://office.microsoft.com/en-us/excel/HA102203821033.aspx
2) Make sure you are using SSO authentication, this is the best way to retrieve data. To learn how to setup SSO for Excel services check article above
3) If you have changed the URL of your site, make sure that you make proper adjustments to your trusted locations list (URL of your site will not be changed automatically)
I did everything (as described above) and my Excel files still reported error. I finally gave up on SSO and reverted back to hardcoded connection string with SQL authentication. I know my solution is not the best one, but it works. I was not able to solve this problem by the book because there is no information in SharePoint logs (Event log, SharePoint logs) that would tell me a little bit more about the error causes.
I know my SSO configuration works since I am using the same SSO application for BDC. Excel services are configured properly since data is refreshed when connection string is hardcoded.
Did you encounter similar error and how did you solve it?