Cannot logon with credentials obtained from Secure Store Provider

Problem Description:

I'm using SPD 2010 to connect to an external SQL database hosted on a different server. External data source type selection is SQL Server. I then filled up the connection properties, choose "Connect with Impersonated Windows Identity" and Secure Store Application Id. But SPD 2010 returns this error: Cannot logon with credentials obtained from Secure Store Provider

Error message:

Cannot logon with credentials obtained from Secure Store Provider

We need to set up your Secure Store service application on the server, and add a Target Application. This is an arbitrary name you assign to the set of credential mappings that you will set up in the Secure Store but getting the same error.

Troubleshooting done:

 I made sure that the credentials that i set in the secure store have at least db_reader access to the database.

Also ensured the user making the request is a part of members group for the target application.

Checked the ULS logs and did not find anything relevant.

Suspecting that there is some sort of firewall or VPN issue but, I am not sure how to proceed?

Tried with different account rather than system account to ensure it reflects the same set of results.

Referred the following articles again n again to make sure everything set up correctly but did not yield any results!

http://technet.microsoft.com/en-us/library/ee806889.aspx

http://technet.microsoft.com/en-us/library/ee806866.aspx

Thanks to Fabian Williams and Marc Anderson for the valuable articles:

http://sympmarc.com/2013/04/10/setting-up-bcs-and-the-secure-store-service-in-sharepoint-2010

http://fabiangwilliams.wordpress.com/2010/04/16/how-to-create-configure-consume-sharepoint-2010-secure-store-in-business-connectivity-services

What Resolution we have applied:-

We have created a SQL Account with SQL authentication + 

Connect with impersonated custom identity and the issue has been resolved. See this useful post SharePoint 2010 using BCS with SQL Server database by Arjan Fraaij.

Reference:

 http://technet.microsoft.com/en-us/library/ee806889.aspx

  http://technet.microsoft.com/en-us/library/ee806866.aspx

Applied to:

·         SharePoint Server 2010

Recommended guidelines for lists and libraries

Limit	Maximum value	Limit type
List row size	8,000 bytes per row	Boundary
File size	2 GB	Boundary
Documents	30,000,000 per library	Supported
Major versions	400,000	Supported
Minor versions	511	Boundary
Items	30,000,000 per list	Supported
Rows size limit	6 table rows internal to the database used for a list or library item	Supported
Bulk operations	100 items per bulk operation	Boundary
List view lookup threshold	8 join operations per query	Threshold
List view threshold	5,000	Threshold
List view threshold for auditors and administrators	20,000	Threshold
Subsite	2,000 per site view	Threshold
Coauthoring in Word and PowerPoint for .docx, .pptx and .ppsx files	10 concurrent editors per document	Threshold
Security scope	50,000 per list	Threshold