On standalone configuration where ECS (Excel Calculation Services) and Microsoft SQL server reside on same machine, external resources such as tables and cubes should be accessible via Office Data connections (ODC) in Trusted Subsystem mode (Default settings) using SSO authentication (In farm scenarios where ECS resides on a dedicated machine Constraint Kerberos delegation is required for Integrated windows Auth and ECS access mode should be modified from Trusted Subsystem to Delegation via STSADM -o set-ecssecurity command).
In cases that separate accounts are being used for SSP and SSO service/admin/configuration, you will get the following error while trying to refresh a connection:
In order to resolve this issue, I usually create an AD group (e.g. SSOAdmin) and add the SSP and SSO admin accounts as members and set the group as SSO and Application definition administrators in SSO configuration page as follows:
More detail on this issue can be found on Ten Tips for Using SharePoint Server 2007 with Excel Services.