Embed SQL DB connection ID & password into BDC Definition File

Overview

(From MSDN: BDC Overview) Business Data Catalog (BDC) is a new business integration feature in Microsoft Office SharePoint Server 2007, it enables Office SharePoint Server 2007 to surface business data from back-end server applications without any coding.

Business Data Catalog provides built-in support for displaying data from databases and Web services. That is, you can use Business Data Catalog to display data from your SAP, Siebel, or other line-of-business (LOB) application via Web services or databases.

Water-blowing

(By me) BDC is a very good tool in connecting SharePoint with other system (DB or Web Services), it is good because the information collected from BDC application can be displayed in some out-of-the-box (but not good looking...) webparts, and be searched just like other information in SharePoint (Yes~ you can search your BDC connected DB in SharePoint!), you can also make site columns based on BDC information and embed it into content type too! One example is to make a SharePoint generated Word Document able to crawl data from DB thru BDC, quite amazing....

Besides all these exciting features, writing a BDC application is quite a pain in the xxx (Sorry I can't control myself for this). The Microsoft claimed "NO CODE" experience is equivalent to writing a XML file with several hundred to thousands of lines. This infernal XML file is called (Application Definition File).

Connecting to DB in BDC - SSO

Writing the damn Definition File is one thing, getting your application to connect to remote DB is another mountain to climb. I used to get it work by using Single Sign-On in MOSS, which is VERY difficult to setup, and required the server to be connected to an Active Directory.

Recently I come across this method to embed connection UserID and Password into the Definition File, so no more SSO nightmare~~ (This method is obviously NOT the best practice and should be used in development environment ONLY)

Code Snippet

<Property Name="DatabaseAccessProvider" Type="Microsoft.Office.Server.ApplicationRegistry.SystemSpecific.Db.DbAccessProvider">SqlServer</Property>

<Property Name="AuthenticationMode" Type="Microsoft.Office.Server.ApplicationRegistry.SystemSpecific.Db.DbAuthenticationMode">PassThrough</Property>

<Property Name="rdbconnection Data Source" Type="System.String">ServerName_Here</Property>

<Property Name="rdbconnection Initial Catalog" Type="System.String">DataBaseName_Here</Property>

<Property Name="rdbconnection Integrated Security" Type="System.String">SSPI</Property>

<Property Name="RdbConnection Pooling" Type="System.String">false</Property>

<!--For Passing Login & Password Directly-->

<Property Name="RdbConnection User ID" Type="System.String">UserID Here</Property>

<Property Name="RdbConnection Password" Type="System.String">Password Here</Property>

<Property Name="RdbConnection Trusted_Connection" Type="System.String">false</Property>

In the above Code Snippet, actually what you have to customize is "ServerName", "DataBaseName", "UserID" & "Password", and the authentication should work.

Note that I am using SQL Authentication here, so make sure to SQL Server you are connecting to have SQL Authentication enabled and have a matching UserID and Password.