There are hundreds of posts on how to do this. E.G http://www.dotnetcurry.com/sharepoint/794/sharepoint-2010-connect-sql-server-external-content-type
However, many of these posts don't talk in detail of a problem that I encountered after setting up the the external content type.
However, the post below summarised my problem.
http://spdoctor.net/Pages/message.aspx?name=login-failed-for-user-bdc
"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."
I kept of getting the message shown above no matter how I enable the permission for the BCS as described by the posts.
Then later on curtesy of Tobias Zimmergren's blog, I discovered there was a way to turn this on via the SharePoint Management Shell.
Refer to http://zimmergren.net/technical/sp-2010-bcs-problem-with-authenticationmode-and-reverttoself
To enable the BCS to authen "RevertToSelf"
Open SharePoint Management Shell
$bcsServiceApp = Get-SPServiceApplication | where {$_ -match "Business Data Connectivity Service"}
$bcsServiceApp.RevertToSelfAllowed = $true;
$bcsServiceApp.Update();
Then we are able to set this setting in the External Content Type's Connection. The Error goes away.
