SharePoint Server 2010.
I set up a Site Workflow that reads some properties of an employee from an external SQL Database.
> created a dbreader user in SQL Server
> added the SQL credentials to Secure Store
> created External Content Type in Designer
> created External List based on the previously created ECT
> user is able to see data in the External List
> created Workflow that reads data from the External List.
> running the Workflow will return empty data for any query from the External List
> following error in the logs
w3wp.exe (0x029C) 0x3D4C Secure Store Service Secure Store efr5 High ValidateCredentialClaims - Access Denied: Claims stored in the credentials did not match with the group claim for a group app.
w3wp.exe (0x029C) 0x3D4C Secure Store Service Secure Store 7493 Critical The Microsoft Secure Store Service application Secure Store Service failed to retrieve credentials. The error returned was 'Access is denied.'. For more information, see the Microsoft SharePoint Products and Technologies Software Development Kit (SDK).
w3wp.exe (0x029C) 0x3D4C Secure Store Service Secure Store efp6 Monitorable GetRestrictedCredentials failed with the following exception: System.ServiceModel.FaultException`1[Microsoft.Office.SecureStoreService.Server.SecureStoreServiceFault]: Access is denied. (Fault Detail is equal to Microsoft.Office.SecureStoreService.Server.SecureStoreServiceFault).
w3wp.exe (0x43B8) 0x4320 SharePoint Foundation Workflow Infrastructure el2x Medium The workflow could not find the specified item in the external data source. Make sure the user has permissions to access the external data source and read items.
The System Account had no permission over the Secure Store Object to read the credentials to the SQL Server. The System Account is the account which is running the Application Pool .
Central Administration -> Manage Service Applications -> Secure Store Service : Manage -> Edit the application ID -> Next -> Next -> Add the AppPool user to the Members list.
A really nice, detailed guide on Business Connectivity Services can be found here.