23 steps to create an External Content Type

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).

Step-by-Step Guide

1. Go to SQL Server, attach northwind.mdf in SQL Management Studio
2. Add windows login for database-user (the account you are going to use to access the database) and assign it db_reader role in northwind db and public role in SQL instance
3. Go to Central Administration > Secure Store Service with farm admin access (you could also give permissions so users can do this themselves – the drawback here is that this will not be possible without granting access to the CA port for end users… another story for another post maybe)
4. create new Target Application, type in ID, Display Name, ContactEmail, choose TargetApplicationType „Group“ and click next
5. click next and fill-in an admin (i.e. yourself). As member type in „everyone“and click Ok
6. Set credentials for db-user in the newly created target application
7. Create SiteCollection „Northwind“ (Template TeamSite 2013) and assign a test account as sc admin (but not with farm admin privileges)
8. Open the SiteCollection with SharePoint Designer 2013, login as your sc admin test account
9. click External Content Types in left-hand navigation and then in the New ribbon External Content Type
10. in „new external content type“ window, type in „Customer“ as name and change „office item type“ to „Contact“
11. Click discover external data sources
12. click „Add connection“and choose „SQL Server“ as data source type
13. fill-in db server and db name and choose „Connect with impersonated windows identity“ as authentication type
14. Open „Northwind“ – Tables – Customers, right click and choose „Create All Operations“
15. Click CustomersID and uncheck required
16. Click ContactName – Office Property „Fullname“ – Check Show in Picker – click Next
17. Add a filter – Type in name Limitfilter – Filter Type „Limit“ – click Ok
18. Add the value 1000 and click „Finish“
19. save changes in SPD (top left corner disk icon)
20. Go to the SiteCollection, click site actions -> Add an App -> External list
21. type in „Customers“ as name and choose „Customers“ from external content type list (use browse button to search for external content type)
22. click create
23. click tile „Customers“

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.

• So go to CA > Manage Service Applications > Business Data Connectivity Service
• Ribbon Permissions > Set Metedata Store Permissions
• add your sc test account (or rather an AD role group in a real production environment) and check Edit, Execute, Selectable In Clients and Set Permissions and click OK

Enjoy!