There are several step-by-step guides available on the internet – so why do I post another one here?
Most of what you find on the internet is just a test case where you will setup an external datasource with a SharePoint farm admin account. But will this be your production use case? In my projects it is not because users should be able to do this themselves without the help of a farm administrator (following SharePoint’s self-service philosophy).
The expected result is that a list of customers from northwind db is shown in your SharePoint site.
But when following the 23. steps of this guide you will struggle at step 13 – you will receive an Access Denied error message. It took me some time to figure out why because first I thought I had a mistake in the setup of the Secure Store target application or somewhere in the database permissions. When I finally found out I decided to post this:
You must configure BCS permissions for your test site collection admin account before this steps work out.