Access denied by Business Data Connectivity

Scenario: You are trying to view an External List.

​You have created an External Content Type, probably in SharePoint Designer. You have finally onfigured authentication correctly (after a couple of false starts - you have figured which account it is using to connect to the database, and you have configured the database to allow that account access to the correct database). You have created a list based on that external content type.

And still you can't see the data. You still missed something. Sheesh.

Oh, yes, of course. You need to go into your Business Data Connectivity Service Application, and set the permissions on the object. First, go to Central Administration->Application Management->Manage service applications. Find your Business Data Connectivity service application and go to the Manage page. Select the external content type and go to Set Permissions on the ECB menu, or the Set Object Permissions on the ribbon. From the Set Object Permissions pop-up dialog page you can add accounts and set their permissions. You will need to give the user who is logging in to SharePoint at least Execute permission to be able to see the list items. Note that this is nothing to do with the account that will access the database - that's another issue.

 

ChangeBDCObjectPermissions.jpg

When you change these permissions it can take up to a minute before the behaviour changes as seen in the user's browser, particularly if the BCS service is running cross-farm. By the way, you only need to add the "Execute" permission to view the contents of the external list.