Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

‚ÄčThis is what happens if you try to use pass-through authentication (also known as "User's Identity" when configuring the external content type in SharePoint Designer), and the database is on a separate server, and you are using NTLM.

With pass-through you are asking BCS to use the identity of the currently logged-in user to talk to the database (or other external system). This is not a bad strategy if you are giving individual users access to your database. The problem arises if the database is on another machine, because NTLM doesn't have the ability to pass the identity on (a process called delegation) and will instead try to connect to the back-end system anonymously. This is commonly known as the "double-hop problem".

There are a couple of ways around this. One is to implement Kerberos, which is nothing like as difficult to configure as some people suggest. But it isn't trivial. The other option is to use impersonation by making use of the Secure Store Service. The Secure Store Service can be configured either to cache a user's credentials (which means they will have to enter them again at some point when prompted), or you can configure a database access account and allow a group of users to use this account. The drawback of this second method is that you lose the audit trail of who did what in the database.

I suppose a third option is to use the trusted subsystem model and let the account running BCS have access to the database (sometimes referred to as RevertToSelf). This is okay for test and development systems but is probably not sufficiently robust security for production use.