Setting up External Content Type for SQL Server database using SQL Server authentication - SharePoint 2010 Foundation

This post is a follow up on the issues that I have got setting up External Content Type (ECT) on SharePoint 2010 Foundation that was going to connect to remote SQL Server database for information. I cannot use my SharePoint user accounts to access SQL Server.

According to the information I have discovered ECT and Business Connectivity Services are available in the SharePoint 2010 Foundation, but there are some issues if you want to use authentication methods in your external connections that are different from Windows Identity or Current User Identity. This is because there is no Secure Store Service in SharePoint 2010 Foundation which serves as an impersonation hub and is only available in SharePoint 2010 Server edition.
The issues are coming from the fact that you can actually create ECT in SharePoint Designer 2010 providing just Secure Store ID and system would ask you for credentials and here you go, but when you try to use your ECT in External Lists or as a lookup columns you would get errors, because Secure Store Service is missing as a module.
For more information about that issue please have a look here:
http://bit.ly/aWYlHn

In my scenario we have our own mighty CRM system called Wylde CRM where data is stored in SQL Server 2008 database and hosted on a remote server. Because I don't want to migrate our customers data to SharePoint I just want to leverage the new shiny functionality that SharePoint 2010 offers out of the box - Business Connectivity Services. Here I will show you how to set up ECT to work properly with remote SQL Server database.

1. Create an ECT in SharePoint Designer 2010.

1.1. Select SQL Server as a type and provide the SQL Server connection settings:

I provided WyldeCRMSS as a Secure Store Application ID, but it can be any name at this stage, because it will only be used on creation and will never work anymore.

1.2. Create all the necessary commands for your ECT to check that all works well from SharePoint Designer, save you ECT and then Export your BDC model to a .bdcm file:

2. Modify your .bdcm file. Open the file in an appropriate editor and find LobSystemInstances element and within it the LobSystemInstance element related to your ECT.

<LobSystemInstance name="SQL2008">
<Properties>
<Property Name="AuthenticationMode" Type="System.String">RdbCredentials</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></Properties>
</LobSystemInstance>
...

Whatever is conatined within the <Properties> element should be replaced with the following values:

<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>

Please pay attention to the elements in bold. They should be provided exactly as above.

Save the .bdcm file.

3. Delete the ECT you have created in SharePoint Designer.

4. Go to Central Administration or to Administration Web site if you have a multi-tenant environment and Import your updated .bdcm file:

5. If the file has been imported successfully - create an External List using the imported ECT: 

6. Enjoy working with your external SQL Server data via SharePoint 2010 Foundation:

Here we go.

If you have any questions in regard to External Content Types, you need assistance or if you think you don't know how you could benefit from using External Content Types for your business - contact us now for a consultation.

Stay tuned for more articles from Wylde Solutions - next article is about setting up ECT properly in SharePoint 2010 Server. :)