Now that you have configured BCS, you can create external content types and external lists. You can refer to my blog post – SharPoint 2010: BCS Walkthrough – to quickly learn how to create an external content type and an external list.
BDC Access Denied Error
Now that your external list is created, you will certainly like to view the list in SharePoint. When you visit the external list, don’t be surprised if you see the error below:
This is a very common error that many of you might face. This error is due to the current user not having enough permissions to access the BDC entity.
So, browse to your BDC Service Application page: Central Administration | Application Management | Manage service applications | Business Data Connectivity
You should be able to locate your BDC model. For our example, its the External Customers. In the dropdown ECB menu, select on Set Permissions:
In the Set Permissions dialog window, you can now choose your user(s)/group(s) who need access to this BDC entity. For our example, I am choosing Administrator user.
You can also set the type of permission you want to grant. For our example, I have granted all of the permissions available:
3) Selectable In Clients
4) Set Permissions
Query against the database error
Now if you refresh your external list page, you might get this error or similar error:
This means that the user has access to the BDC entity, but there is something wrong when the model is trying to fetch the external data. In this case, it is from the database. Our External Customers entity model connects to SQL Server database to retrieve the customers. So, this error clearly tells that there is some problem while fetching that data.
One other useful thing to do when you get this error is to check the Windows Event Viewer logs. BCS logs errors to Windows Event Viewer logs. Here is the cause for our error:
It is very clear from the logs that the user Administrator is not having access to the Customers database. So, its an easy fix – After granting the user Administrator rights to Customers database, here we are with all the customers!