Wednesday, July 26, 2017

Businesss Data Connectivity - Access denied!

I recently stood up a new SharePoint 2016 environment complete with patching and configuring to ensure it is as healthy as possible.  Once complete, one of the first actions I wanted to do was create an External Content Type and connect to a MSSQL database.  Done it before, should be easy enough...Nope.

Following Microsoft's steps to configure an External Content Type, I ran into the access denied issue right after trying to Add Connection.  The message returned was 'Access denied by Business Data Connectivity'.

Quick search shows I did not have permission to create new connections in the Business Data Connectivity service.  Alright, simple enough to add myself.  I launch Central Administration, click on Application Management, click on Business Data Connectivity Service and in the ribbon I click on Set Metadata Store Permissions.  New permission window, find my name and add to the group box, check the permissions and click the OK button just to be greeted by "Something went wrong..." message that once again tells me 'Access denied by Business Data Connectivity'.  This makes no sense as I am a farm admin, I should be able to make this change.

A few hours of searching and I tried several suggestions, all to no avail.  At this time I am really thinking outside the box and a thought occurred to me.  I was attempting to make these changes on my local client.  I have experienced some wackiness where the client browser and machine does not always play nicely with Central Administration, mostly not showing all the links.  So, I decided to RDP into the App server (using an install account that has the same permissions as my account) and add my user account to BCS Metadata Store Permissions, sure enough it worked.  In retrospect, I should have tried running the browser with administrative rights to see if that would have worked but hey, this way worked as well.

So, if you are having trouble adding a user or a group to the BCS Metadata Store Permissions, either RDP into one of the farm servers or try running your local browser as administrator and see if that works.