How to troubleshoot errors encountered while configuring SQL SERVER with SharePoint 2010 BCS - TechNet Articles - United States (English) - TechNet Wiki

How to troubleshoot errors encountered while configuring SQL SERVER with SharePoint 2010 BCS

Brief

SharePoint has come a long way with Line-Of-Business Applications Integration.  Through the virtue of BCS in SharePoint 2010 it made integration handy and trouble-free. But even after this many of use fell into some issues and have to burn down time and efforts to get it resolved. One such issue is “Cannot connect to the LobSystem (External System).” which you may come across while integrating the Sql-Server with SharePoint 2010.

How-To Troubleshoot

Majority article you will get against this error speaks on the missing users/groups and enforcing permissions on them or on the types of authentication. While the users and groups are missing on the sql server end while the authentication is on BCS side.

Below solution/tips could be helpful for suppressing this issue/error

• BCS Side:
  • In case of Foundation Server one will not have much option and will have to set Authentication Mode to User’s Identity or BDC Identity (for Default and Client both).
  • In case of SharePoint Server one should make use of Impersonate Windows Identity or Impersonate Custom Identity which deals with LOB through Secure Store Service Application
  • RevertToSelf or BDC identity authentication is not enabled by default on a BDC Service application on a SharePoint farm.  To enable this use following powershell:

Grant-SPBusinessDataCatalogMetadataObject -Identity <MetadataObject> -Principal <SPClaim> -Right <Execute | Edit | SetPermissions | SelectableInClients> [-AssignmentCollection <SPAssignmentCollection>] [-Confirm [<SwitchParameter>]] [-SettingId <String>] [-WhatIf [<SwitchParameter>]]

It’s always better to rely on Secure Store Service for authentication. Since Foundation Server will not have Secure Store Service we are limit with few options.

Also use of secure store service relieves from mapping all users or groups on the sql server end

• User, groups and permissions:
  • In case of Authentication Mode set to User’s Identity or BDC Identity we will need to add all or specific user groups as listed below on the sql server end:
    • NT AUTHORITY\NETWORK SERVICE
    • NT AUTHORITY\IUSR
    • NT AUTHORITY\ANONYMOUS LOGON

• For these users/groups you may need to atleast provide dbreader permission on the database that is exposed through BCS