External Content Type in SharePoint

Question

We try to create an ECT for our on-prem SharePoint 2019 farm via SharePoint designer. The ECT is of type sql (sql2016). As a Farm Administrator I can do so, but a regular Domain User (with full access on the SharePoint site in charge) cannot do so. He can connect via SharePoint designer, kann start with the process of creating a sql ECT connection. But whatever type of authentication he chooses, he never succeeds.
Beside this, he doesn't even see (and edit) existing ECT connections created by me as Farm (and Windows Domain) admin.

What permissions are required in order to be able to see existing, create new and modify ECT connections? Let's assume the user has enough permissions on SharePoint, because me as the one who can create a ECT, can use his account in order to connect to the SQL database.

Also we only know little about this topic, our goal is to retrieve SQL table content from a remote SQL DB and show this in a list. That works so far, but the point is, this user who should deal with it might need to modify or create additional ECTs, but he can't yet. The user is defined as an Admin (Full Control) on "Business Data Connectivity Service" service application, and he has all permissions on the by me created ECT

kind regards,
Dieter

Answer 1

@Anonymous

Go to central administration -> Manage service applications -> Business data connectivity service application -> Set metadata store permission -> Find the user, select permission -> Click "Propagate permissions to all BDC Models, External Systems and External Content Types in the BDC Metadata Store. Doing so will overwrite existing permissions.".

---

If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Looks like this works. At least, the user still can see the external content type connection after I removed him from the Farm Administrators group, but assigning Metadata Store Permissions Edit, Execute and Selectable in Clients to him.

Could you also give me some details on this topic https://learn.microsoft.com/en-us/answers/questions/353312/reading-and-saving-as-template-of-a-list-created-f.html?

kr