"Access denied by Business Data connectivity" error while creating external content type using SPD on a SharePoint environment with minimal permission

Scenario: Consider a scenario of least permission installation of SharePoint. You have a site collection admin account which meets all the prerequisites given in below article.
Prerequisites for deploying a Business Connectivity Services on-premises solution in SharePoint 2013

  • You have an account that has permissions to administer the Business Data Connectivity Service Application
  • You have an account that has permissions to administer the Secure Store Service service application

When using the same account to connect to a SQL database using secure store SSO ID, you receive "Access denied by Business Data connectivity" error in SharePoint Designer.

In fiddler trace, we see below error:
<message>IEntity could not be found using criteria 'The requested Entity 'New external content type (2)' under Namespace 'https://<site url>' was not found.'.</message><metadataObjectTypeName>Microsoft.BusinessData.MetadataModel.IEntity, Microsoft.BusinessData, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c</metadataObjectTypeName><serverStackTrace> at Microsoft.SharePoint.BusinessData.SharedService.BdcServiceApplicationProxy.Execute[T](String operationName, UInt32 maxRunningTime, ExecuteDelegate`1 operation, Boolean performCanaryCheck, Boolean isChannelThatDelegatesIdentity)
at Microsoft.SharePoint.BusinessData.SharedService.BdcServiceApplicationProxy.GetEntityWithNameAndNamespace(String namespace, String name, Guid partitionId)
at Microsoft.SharePoint.BusinessData.SharedService.BusinessDataService.&lt;&gt;c__DisplayClass9c.&lt;GetEntityWithNameAndNamespace&gt;b__9b(BdcServiceApplicationProxy serviceApplication)
at Microsoft.SharePoint.BusinessData.SharedService.BusinessDataService.Execute[T](ExecuteDelegate`1 operation)</serverStackTrace><a:SearchCriteria>The requested Entity 'New external content type (2)' under Namespace 'https://<site URL>' was not found.</a:SearchCriteria></MetadataException></detail></s:Fault></s:Body></s:Envelope>

In ULS we see Access denier error
04/14/2017 12:43:46.73 w3wp.exe (SERVER:0x7728) 0x5D18 Business Connectivity Services Business Data 9f4c Unexpected 'Business Data Catalog' BdcServiceApplication logging server side AccessDeniedException before marshalling and rethrowing on client side: Access Denied for User '0#.w|domain\username', which may be an impersonation by 'domain\user'. Securable IMetadataCatalog with Name 'ApplicationRegistry' denied access. Stack Trace:    at Microsoft.SharePoint.BusinessData.SharedService.MetadataObjectAccessor.CheckCreateAccess(MetadataObjectStruct parentStruct, DbSessionWrapper dbSessionWrapper)     at Microsoft.SharePoint.BusinessData.SharedService.ModelAccessor.Create(MetadataObjectStruct rawValues, MetadataObjectStruct applicationRegistryStruct, DbSessionWrapper dbSessionWrapper)     at Microsoft.SharePoint.BusinessData.SharedService.BdcServiceApplication.Execute[T](String operationName, UInt32 maxRunningTime, ExecuteDelegate`1 operation) 41ffe79d-b797-90d3-dc0a-0c6f8d45c7af

Cause: The article given is causing the confusion as, the site collection admin account used to create the external content type has permission on BCS service application but, still gets the "Access denied by Business Data connectivity" error. Account used for creating the external content type must have "Edit, Execute, Selectable in Clients and Set permissions" at "Metadata Store Permission" of BCS service application.

Resolution:

Open BCS service application and click on "Set Metadata Store Permission". Add the user account (through which SPD is open) in there with Edit, Execute, Selectable in Clients and Set permissions added to the account.

1 2

After doing this external content type successfully gets created the using SharePoint Designer