Sharepoint MOSS 2007 - Working with the BDC (Business Data Catalog)

Having just started working with the BDC, here's what I've encountered so far.

Using BDCMetaMan

On advice from others I started using this tool however I found it to be a tad unwieldy in that I couldn't work out how to do everything I wanted – for example, use a Stored Proc as my source.

So I started with BDC MetaMan, took the output it gave me and then just screwed around with it to make it work with stored procs. In doing so, I found that writing application definitions is nowhere as complicated as others had lead me to believe. That said, it does make for an easy start point where I could just replace values and avoid the tedium of building up all the XML tags in the right order and syntax and so on…

Application Definition

The basic structure is this:

How to use SQL Authentication

Thanks to Nick Kellet for doing the hard slog and solving this problem….http://planetmoss.blogspot.com/2006/11/using-single-signon-with-database.html

The basic steps are:

1. Enable the Microsoft Single-sign-on service using a domain account (in windows Services)
   


2. Create an SSO Application
   Central Admin > Operations > Security Configuration > Manage Single Sign-on > Manage Server Settings
   Enter Display Name, Application Name (note: this field is what you will enter into your application definition in the LOB instance properties) and Email Address. They don't have to be anything in particular. Ensure the Account Type is Group.
   



3. Associate the SSO Application with the domain users group or some other desired group
   Central Admin > Operations > Security Configuration > Manage Single Sign-on > Manage Account Information. Select the SSO application and type in the "<domain>\domain users" group. Then key in the SQL username and password. Give the application SELECT and EXECUTE rights (assuming you are using Stored Procedures).
   



4. Configure your connection string in the application definition
   <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">SERVER NAME</Property>
   <Property
   Name="RdbConnection Initial Catalog"
   Type="System.String">DATABASE NAME</Property>
   <Property
   Name="RdbConnection Integrated Security"
   Type="System.String">false</Property>
   <Property
   Name="RdbConnection Pooling"
   Type="System.String">true</Property>
   <Property
   Name="SsoApplicationId"
   Type="System.String">SSO APPLICATION NAME</Property>
   <Property
   Name="SsoProviderImplementation"
   Type="System.String">Microsoft.SharePoint.Portal.SingleSignon.SpsSsoProvider, Microsoft.SharePoint.Portal.SingleSignon, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c</Property>
   </Properties>
   



5. Import the application definition
   Central Admin > SSP > Business Data Catalog > Import Application Definition
   



6. Configure permissions for the application definition
   Central Admin > SSP > Business Data Catalog > Permissions > Copy all to descendants (or apply the entity you want to set if not inheritable)
   



7. Add a BDC List web part to a page, configure it and fingers crossed, it will work!
   

One BIG caveat… getting this to work across multiple domains is going to be a problem. For now, I’m ignoring that issue and hoping it doesn’t rear it’s VERY ugly head…

How to use Windows Authentication

Haven’t tried it so I don’t know. But… if you are having problems my first suggestion would be to consider the Kerberos double-hop issue. If you don’t know what that is, google it… it’s a reasonably simple fix, if you are friendly with your AD administrator(s)!

Detailed Application Definition

It’s fairly self-explanatory. The bits in green are the bits you have to fill in.

Notes:
· The available data types are your standard .Net data types.
· Refer http://msdn2.microsoft.com/en-us/library/ms546541.aspx for a BDC overviewBelow is my first pass attempt so caveat emptor… it ain’t perfect folks! Feel free to add your comments with anything you can see that can be done better or anything that I’ve got wrong!

What’s Next - Got any ideas?

So next, I’ve been charged to come up with a BDC-based list where some manual info is also stored – bodgy explanation but allow me to demonstrate.

BDC list contains 20 columns. To this list, we want to add some manually entered data. The list will contain around 2000 records. The BDC data will be current and the data editor will update the manual fields when time permits. The web part should output like:

RequestNo Title DueDate Contact Type Owner OutageRequired OutageNotification

Where OutageRequired and OutageNotification are sourced from manual data and the rest are from the BDC.

So my initial thought was to have a BDC lookup in my manual list where the RequestNo, OutageRequired and OutageNotification are entered. I really hate this idea. It’s ugly and impractical. It means that the user has to know the Request number upfront rather than being able to immediately see which request numbers haven’t been manually updated because the 2 fields are empty . Haven’t had a second to think it through properly yet but if anyone has a better idea please speak up!