How to update existing SharePoint External Content Types (ECT)

The question is you have created several External Lists inside SharePoint using ECTs and now the database where you used to create ECTs is moved to a different server. Besides the name of the database is changed. However, table names haven’t changed.

Instead of recreating new ECTs and then new External lists, I want to update my existing ECTs with the new Database server name, Database name and new Target Application ID so that my existing External Lists continue working. That is the plan.

Below are main assumptions before venturing in updating existing SharePoint External Content Types:

1. Business Connectivity Service is configured in Central Admin.
2. Secure Store Service is configured in Central Admin and you had a Target Application ID created inside the secure store called OldTargetApplicationID. Below is how it was created.Next we chose Windows user Account as we are using an Active Directory Account which is already given to the external database and now the plan is to add that AD account’s credential in here so that our SharePoint External list can surface content on behalf of the AD account.

While you are in the list of Target Application ID page, go to your ID and choose Credentials

Add your AD service account and password. This is the ID that is already given access to your external database.

3. Using SharePoint Designer, you created an ECT before and was consuming inside SharePoint. However, IT and Business decided to move the external Database that ECT is consuming into a new SQL server database and also changed the database name. However, table names haven’t been changed.

Now you do the following to update your ECTs:

1. Create new Target Application ID called NewTargetApplicationID. Inside the secure store service application. Meaning new AD service account would need to be created and given access to the new database and using above steps new target application ID created.
2. Go to your SharePoint Designer, ECTs and Edit ECT. In the dialog box update the following: Database Server, Database name and Secure Store Application ID (the assumption is you are using Impersonate Windows Identity option for Authentication mode.
3. Click Ok.
4. Now we need to go to Business Connectivity (BCS) service application and make sure permissions are set as expected. These two Microsoft blog posts have valuable information on this: Create external content types for SQL Server in SharePoint and Manage Business Connectivity Service Applications.
5. Inside the BCS you see the list of ECTs. You click each one of them and configure permissions by choosing “Set Object Permissions”. Next you will add the right user who can have one of the 4 listed (see table below).
   

   Permission	Notes
   Edit	Allows the user or group to create External Systems and BDC Models, to import BDC Models, and to Export BDC Models. This setting should be reserved for highly privileged users.
   Execute	Allows the user or group to execute operations (create, read, update, delete, or query) on ECTs.
   Selectable in clients	Allows the user or group to create external lists for any ECTs, and to view the ECTs in the external item picker.
   Set permissions	Allows the user, group, or claim to set permissions on the Metadata Store. At least one user or group must have this permission on every BCS connection so that permissions management can occur. With this permission, a user can grant Edit permissions to the Metadata Store. This setting should be reserved for highly privileged users

Advertisement