Business Data Catalog – First Attempts

May 14, 2008

I’ve been playing around with BDC today as part of my revision for 70-630 exam.

After successfully installing the AdventureWorks BDC application using the sample ADF (Application Definition File) I hit a bit of a wall using the Business Item Webpart.

I get the following error when selecting an item to view:
Unable to connect to AdventureWorksInstance

The ADF LOBSystemInstance was originally setup with PassThrough Authentication, which means the currently logged on users credentials are passed to the LOB system (In this case a SQL Server 2005 database). Although this method does bring the obvious benefit of being able to take advantage of the LOB security provision, there are two issues:

a) This method does not work across servers – e.g. If you are connecting to a LOB on another server, you will encounter the double hop issue, which means the credentials are lost (Not an issue for my development environment, where everything is hosted on one box)

b) Performance is likely to suffer due to the way connection pooling is handled in this scenario –

I next tried using ‘RevertToSelf’ authentication method, which causes the BDC to use the identity of whatever application pool is running the web app – I added this login to SQL Server, verified that it had correct permissions by configuring an ODBC connection (Nice quick way of testing connectivity) and tried again – Same error! It was at this point that I realized my mistake – The ADF file I had downloaded was for the SQL 2000 AdventureWorks example database, which is normally called ‘AdventureWorks2000’, I was using the SQL 2005 example, which is called ‘AdventureWorks.

So, having deleted the application from BDC, renamed the default catalog in the ADF and reimported, I was able to authenticate.

I then got this error:
An error occurred while retrieving data from AdventureWorksSampleInstance. Administrators, see the server log for more information

I then tried to find the logs for BDC – Apparently these reside in 12 hive/logs, and are gererated every ½ hour. I didnt have any luck finding anything useful, so considering my earlier problem, wondered if the schema for AdventureWorks2000 was different from AdventureWorks2005 – Which I proved to be the case having examined some of the property queries in the ADF file.

So, a quick Google found the updated AdventureWorks2005 ADF file which I downloaded, changed the LOBSystemInstance ‘RdbConnection Data Source’ property value to my SQL Server name, and the AuthenticationMode to ‘RevertToSelf’ – After this, it worked!


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: