External Data Source vs. External Content Type

Question

I was able to create an external data source (connected to an external SQL) database. However, when I try to create a column that is a lookup field, this data source and it isn't available to me

So then I thought well, I'll create it as an external content type (as a SQL connection) but get this error: Access denied by Business Data Connectivity

Does BCS have to be enabled somewhere is site collection settings? Also, in just general terms, what is the difference between an external data source and an external content type? When do you use one vs. the other? Will I be able create a lookup field to an external content type?

Thanks for any help you can give

Answer 1

A common cause of Access denied from BCS is not having permissions on the External Entity.

To check this navigate to the Business Data Connectivity Service Settings (Central Admin, Manage Service Applications)

You should see the list of external content types.

Check the permissions on the External Entity that displayed an Access Denied error by selecting Set Permissions on the popup menu for that entity.

Verify your user is present.

If not you can add the user directly to that object or add your user globally to the BCS using the "Set Metadata Store Permissions" command in the ribbon bar.

If this is not the cause of the problem then I would need to see a log file and your bdc model file.

Answer 2

I just came across this issue today and solved it as follows:

I did what Steve P said above, giving my user account access to the External Entity in the usiness Data Connectivity Service Settings. However, that was not enough for me. I had to add the SharePoint service account to the database that the External Content Type is referencing as well. That did the trick for me.

I'm not so sure about the difference between External Content Types and Data Sources. I'm still pretty new to SharePoint, so I can't answer that part.