question

DieterTontsch-0908 avatar image
0 Votes"
DieterTontsch-0908 asked DieterTontsch-0908 answered

External Content Type in SharePoint

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

office-sharepoint-server-administrationoffice-sharepoint-server-itpro
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EmilyDu-MSFT avatar image
0 Votes"
EmilyDu-MSFT answered DieterTontsch-0908 commented

@DieterTontsch-0908

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.




· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks I will give it a try and let you know if ir solved my problem. But I have another question, since this will go to overwrite existing permissions I'd like to previously query for the actual permissions, is there a cmd command for this?
Because I have no idea what permissions are set now.

kind regards,
Dieter Tontsch

0 Votes 0 ·
DieterTontsch-0908 avatar image
0 Votes"
DieterTontsch-0908 answered

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://docs.microsoft.com/en-us/answers/questions/353312/reading-and-saving-as-template-of-a-list-created-f.html?

kr

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.