Business Connectivity Services (BCS) Using SQL Authentication in SharePoint 2010 Foundation

One of the most exciting features in SharePoint 2010 Foundation (the “free” one) is the inclusion of Business Connectivity Services (BCS). This feature allows you to access data outside of SharePoint, surface it and interact with it as though it were native SharePoint lists!
In SharePoint 2007 you had to upgrade to the server version (MOSS) to get access to this sort of functionality. Of course, nothing comes completely for free and there are some limitations. The main limitation comes with how you connect to SQL databases. With SharePoint Foundation, unless you want to give each user access to the SQL database, you will need to specify SQL credentials to use. This involves a little extra work, but is worth the effort.
This blog does a great job of walking you through the steps:
http://wyldesharepoint.blogspot.com/2010/06/setting-up-external-content-...
Just note that in his example of the modified .bdcm file, the elements and attributes need to have initial capital letters. Here is the corrected example:
<Properties>
<Property Name="AuthenticationMode" Type="System.String">PassThrough</Property>
<Property Name="DatabaseAccessProvider" Type="System.String">SqlServer</Property>
<Property Name="RdbConnection Data Source" Type="System.String">DB Server Name</Property>
<Property Name="RdbConnection Initial Catalog" Type="System.String">DB Name</Property>
<Property Name="RdbConnection Pooling" Type="System.String">True</Property>
<Property Name="RdbConnection User ID" Type="System.String">SQL User Name</Property>
<Property Name="RdbConnection Password" Type="System.String">SQL User Password</Property>
<Property Name="RdbConnection Integrated Security" Type="System.String">False</Property>
<Property Name="ShowInSearchUI" Type="System.String"></Property>
</Properties>