Excel Services supports displaying data from external locations if those locations were configured as “Trusted”. Try creating a simple excel document with a Table that displays data from a SQL server table . Now try to view it in Excel Web Access. You will get the following error:

After some investigations, I found out that Excel services supports displaying data from external data sources, but not in a table format, only in a pivot table!

To work around this limitation, you need to convert your tables to pivot ones? How ? Download this tool and you are good to go !

For more info : http://blogs.msdn.com/b/cumgranosalis/archive/2006/11/03/query-tables-work-around-for-excel-services.aspx

http://msdn.microsoft.com/en-us/library/cc514223(office.12).aspx#MOSS2007TenTips_ConfiguringExternalDataConnections