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