SharePoint Workflow Cannot Access External List

SharePoint Server 2010

I set up a Site Workflow that reads some properties of an employee from an external SQL Database.

I did the following steps:

• 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.

Howver, running the Workflow will return empty data for any query from the External List. The following error can be observed 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). &nbsp  
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.

Solution

The System Account had no permission over the Secure Store Object to read the credentials for 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.