Error when refreshing BCS external data

Recently I have been working with the Business Connectivity Service in SharePoint. Done a simple set up using SharePoint Designer, and used User’s Identity for the Authentication Mode. Once I have added my External column to the list and additional columns for it to pull through. I am able to add new items, I can update items. It appears my list is working correctly.

With the backend system changing, the columns in SharePoint do not automatically update, you have to click the refresh button next to the external column.

So you click the button, follow the steps and you get the following error message:

0 item(s) were refreshed.
An error occurred while retrieving data from Database. Administrators, see the server log for more information.

By checking the SharePoint logs the following messages are found:

The security configuration of the LobSystemInstance does not permit updating the external data column values. Use Secure Store credentials for this LobSystemInstance to enable this scenario.
Exception thrown while synchronizing business data System.NotSupportedException: Specified method is not supported. at Microsoft.SharePoint.WebControls.BusinessDataSynchronizerJob.DoWork()
System.NotSupportedException: Specified method is not supported. at Microsoft.SharePoint.WebControls.BusinessDataSynchronizerJob.DoWork()

After checking online, there are a few blogs out there saying that you should change your Authentication Mode from User’s Identity to BDC Identity also known as revert to self. This does work, however I would argue this isn’t the best recommended practice in a production environment. I recommend to use Impersonate Windows Identity, or Impersonate custom identity. For instructions how to set this up, please follow a blog by Sridhar on MSDN blogs. http://blogs.msdn.com/b/sridhara/archive/2010/01/27/setting-up-bcs-with-secure-store-application-impersonation.aspx

So why?

Question is why does it work for Revert To Self, and Impersonate Windows Identity? From reflecting on Microsoft code, at Microsoft.SharePoint.WebControls.BusinessDataSynchronizerJob.DoWork() found in the Microsoft.SharePoint.dll, there is a method call BdcClientUtil.CheckSupportedAuthenConfiguration()

   if (!BdcClientUtil.CheckSupportedAuthConfiguration(lobSystemInstance))
            {
                ULS.SendTraceTag(0, ULSCat.msoulscat_WSS_WebControls, ULSTraceLevel.Medium, "The security configuration of the LobSystemInstance does not permit updating the external data column values. Use Secure Store credentials for this LobSystemInstance to enable this scenario.");
                throw new NotSupportedException();
            }

As you can see there is our error message about the LobSystemInstance does not permit updating the external data column values. Reflecting through to CheckSupportedAuthConfiguration method you can see that if the lobsystem instance is Database, the only valid authentications are RevertToSelf, RdbCredentials, WindowsCredentials. Which is why User’s Idenitity (Passthrough) doesn’t work.

internal static bool CheckSupportedAuthConfiguration(ILobSystemInstance lobSystemInstance)
{
 bool flag = false;
 if (lobSystemInstance != null)
 {
 ILobSystem lobSystem = lobSystemInstance.GetLobSystem();
 if (lobSystem != null)
 {
 switch (lobSystem.SystemType)
 {
 case SystemType.Database:
 return CheckSupportedAuthConfiguration(lobSystemInstance, "AuthenticationMode", new string[] { DbAuthenticationMode.RevertToSelf.ToString(), DbAuthenticationMode.RdbCredentials.ToString(), DbAuthenticationMode.WindowsCredentials.ToString() });
 case SystemType.WebService:
 return CheckSupportedAuthConfiguration(lobSystemInstance, "WebServiceAuthenticationMode", new string[] { HttpAuthenticationMode.RevertToSelf.ToString(), HttpAuthenticationMode.Credentials.ToString(), HttpAuthenticationMode.WindowsCredentials.ToString() });
 case (SystemType.WebService | SystemType.Database):
 case ((SystemType) 4):
 case ((SystemType) 5):
 case (SystemType.Custom | SystemType.Database):
 return flag;
 case SystemType.Custom:
 return true;
 case SystemType.Wcf:
 return CheckSupportedAuthConfiguration(lobSystemInstance, "WcfAuthenticationMode", new string[] { WcfAuthenticationMode.RevertToSelf.ToString(), WcfAuthenticationMode.Credentials.ToString(), WcfAuthenticationMode.WindowsCredentials.ToString(), WcfAuthenticationMode.DigestCredentials.ToString() });
 case SystemType.DotNetAssembly:
 return true;
 }
 }
 }
 return flag;
}