The business data catalog is nice and easy to get a proof of concept going on a single server with BDC Meta Man. Many people however come across the ‘Unable to connect to [Instance Name]’ problem when they move to a multiple server setup and the Line of Business system is no longer on the same server as SharePoint. The error that gets reported in the 12LOGS file is “NT AUTHORITYANONYMOUS LOGIN” is denied.
BDC Meta Man will generate your application definition file with Pass Through authentication by default and because you are using PassThrough authentication and SQL is on a different server you’ll be suffering something called the Double Hop Issue. You can find out some more information about it here:
There are a number of options
1, Setup Kerberos within your organization
2, Setup and use Single Sign On
3, Change the BDC authentication to RevertToSelf
The third option will definitely be the easiest to do. Using RevertToSelf means that SharePoint will use the application pool identity to attempt to access data as. To do this you’ll need to ensure the application pool identity is a domain account, and that it has the necessary permissions in SQL Server to retrive data.
To setup RevertToSelf in your application definition file:
1, open it in BDC Meta Man.
2, in the bottom left hand window, right click on [NameofYourDataSource]LOBSystem and choose Edit
3, in the window that opens choose RevertToSelf as the AuthenticationMode. Save the settings
4, Regenerate the XML file, re-import and try it out.
If you have any questions on this please email email@example.com