Following the technet articles on how to get external data into SharePoint 2013 (for testing purposes) I've created:
- Business Data Connectivity Service with Metadata Store Permissions
- Secure Store Service Application (SSS) with SQL Server credentials (and not windows credentials, since they fail too)
The credentials in SSS are owners of the database Northwind I'm trying to connect to. When signing in with SQL Server Credentials, the user can read and execute the database as expected.
In SharePoint Designer I'm adding the new External Content Type, providing it a name and database server, database and connecting with Impersonated custom identity referencing the SSS. When I provide the SQL Server Credentials, it fails to connect asking for windows credentials. But since windows credentials don't work on the SQL Server login either, I'm stuck in a loop I can't get out of.
Error from SharePoint Designer
Error from SQL Server Log
The Web Application has service connection to BDC, and in the Secure Store Service Application Everyone is member. Still - no connection made because of the access denied error. Why is that, and how do I overcome this issue?