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.
If I enter the credentials of the domain account which has access to database, I do not get error and it works fine but my question is why cannot I enter any of the authenticated users' credentials to gain access to database? I thought Secure Store maps the credentials of authenticated users to the account that has access to database. Isn't that correct?
I did enter "All Authenticated Users" in the members textbox while creating Secure Store Target Application.