Access Denied by Business Data Connectivity

Problem

You have successfully created both a new Secure Store Service application and a new Business Data Connectivity Service application.  The new BCS application connects to an external line of business database hosted on SQL Server and located within the same Active Directory domain as the SharePoint Server 2010 farm.  You successfully created an application page for a list within this LOB database.  You then navigate to this application page and experience the following error:

Resolution

This likely involves a permissions issue.  If this is a new BCS application, verify that you have set the appropriate object permissions on the new BCS application.  Troubleshooting checklist is provided in the next section.

Troubleshooting Checklist

• In SharePoint 2010 Central Administration: Verify that you and other users and security groups have been granted appropriate BCS object permissions:
1. Go: Application Management > Manage Service Applications > [your business data service application]:
   
2. Next, select the appropriate list item (check it), and then click the Set Object Permissions button on the Edit ribbon.  You can also hover the cursor over the name of the list item to expose the dropdown, and then select Set Permissions.:
   
3. In the Set Object Permissions dialog, verify that all necessary accounts and groups are listed here and that they have been assigned all of the permissions that you see in the box below.
• In SQL Server Management Studio: Verify that the impersonation account has been added to the target database logins and that it has been granted the appropriate database roles:
1. Open SQL Server Management Studio.
2. Expand [server name] > Security > Logins, and then verify that the impersonation account is listed:
   
3. Double-click the impersonation account to view its properties.
4. Select the User Mapping page.  Verify that the impersonation account has been mapped to the appropriate database and assigned the appropriate roles:
   
• In Active Directory Users and Computers: Verify that users and security groups have been created and that users needing access to the LOB database have been made members of the appropriate security group:
1. On the Domain Controller, go: Start > Administrative Tasks > Active Directory Users and Computers.
2. Expand the console tree: Active Directory Users and Computers > [yourdomain] > Users.
3. Verify that the impersonation account is listed:
   
4. Verify that the security group to be used for LOB database access is listed:
   
5. Double-click on any account experiencing problems trying to view LOB data in SharePoint.
6. Select the Member Of tab and verify that the user account has been made a member of the appropriate security group dedicated to this LOB database access:
   

References

• MSDN Blogs
• Access denied by Business Data Connectivity
• Microsoft Support
• "Access denied by Business Data Connectivity" error message when you discover, delete, or save a new LOB system in SharePoint Designer 2010
• SharePoint TechCenter
• Access denied by Business Data Connectivity when trying to add myself to the BDC Metadata Store
• TechNet Wiki
• SharePoint 2010: Access Denied By Business Data Connectivity Error in Using External Content Type List
• StackOverflow
• Access denied by business data connectivity error
• SharePoint BCS SSS Help Needed
• Other Blogs
• Access denied by Business Data Connectivity – Solution