I am trying to create an external content type from within sharepoint designer.
When I click "Add New Connection" and enter the required information like Database Server, Database Name, * Connect with Impersonated Windows Identity *, Secure Store Application ID, I get prompted for Windows Credentials. However, after entering
credentials, I get "Cannot connect to SQL Server database defined in data source connection XXXX" Login failed for user XYZ.
I set credentials for Secure Store based on a domain account and that domain account has dbreader access to the database. Members of secure store are all authenticated users. My question is why is not secure store mapping the user credentials to the account
that I set. It looks to me that whose credentials I enter in the windows prompt are being passed to database server.
Any pointers?