Home > Uncategorized > Using the BDC to populate user profiles from a SQL Server database – Part Two

Using the BDC to populate user profiles from a SQL Server database – Part Two

This is the second part of a two part post. The first part can be seen here

Now that we have SSO setup and working, we now need to create and import the application definition.

I’m not going to go into to much detail on how to create a application definition, but there are various tools available that you can use (BDC Meta Manager, Microsoft Business Data Catalog Definition Editor which is part of the SharePoint Server 2007 SDK)

Here are the key parts of the application definition you need to be aware of.

I have added the XML to a Word document which can be downloaded here (Sorry WordPress doesn’t let me upload XML files)

Below are the properties used by SharePoint to connect to the database. You need to  use the SSO ID specified in Part One when you setup SSO

<LobSystemInstance Name="Persons">
<Property Name="AuthenticationMode" Type="System.String">RdbCredentials</Property>
<Property Name="DatabaseAccessProvider" Type="System.String">SqlServer</Property>
<Property Name="RdbConnection Data Source" Type="System.String">MyServerName</Property>
<Property Name="RdbConnection Initial Catalog" Type="System.String">MyDatabaseName</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">SSOAppId</Property>
<Property Name="SsoProviderImplementation" Type="System.String">Microsoft.SharePoint.Portal.SingleSignon.SpsSsoProvider, Microsoft.SharePoint.Portal.SingleSignon, Version=, Culture=neutral, PublicKeyToken=71e9bce111e9429c</Property>

If your primary source is going to be from your Active Directory (In most cases it will be). Your identifier needs to be in the format of DOMAIN\UserName

Once you have loaded your application you need to add both the search crawl account and the user crawl account and give them execute permission . In most cases this will be the same account, but if you have followed the least privilege configuration, these will be separate accounts and both will need to be added.

To add the BDC as secondary connection, go to the following link in SSP admin;



Shared Services Administration: My SSP > User Profile and Properties > Manage Connections    

Click on the “Create New Connection” button and enter the information as below

You need to match your UserNameFilter specified in the application definition file to the AccountName User Profile field 

User Profile Import Settings

Click OK and then schedule a full profile import.

Once the import has finished, check the import logs for any errors. This should always be your first port of call if it does not work.

Look for errors under the PEOPLE_DL_IMPORT content source and beginning with spsimport://$$nonmaster$$

Once the import has been run successfully, you will now be able to map the fields with the user profile properties in the “View Profile Properties” page.

Categories: Uncategorized Tags: , , ,
  1. 16 February 2008 at 6:51 am

    Hi Toby!
    Great article(s)! I need to use the BDC to import users inte different Sharepoint Groups depending on value of fields in a SQL Server database table. Any idea how to do that?

    Best regards, Tomas

  2. 21 February 2008 at 9:54 am

    Hi Tomas, You could try something like the code below. If you put this in a timer job and schedule it to run after your user profile crawls have run. If you have done as above the extra properties from the database will be available through the UserProfile object.

    I’ll post some proper examples on how to do this soon.

    string url = “http://lonms01406:8003″;
    string accountName = @”domain\user”;

    using (SPSite site = new SPSite(url))

    ServerContext context = ServerContext.GetContext(site);
    UserProfileManager manager = new UserProfileManager(context);
    UserProfile profile = manager.GetUserProfile(accountName);

    if (profile[“Manager”].Value.ToString() == “Henry”)
    using (SPWeb web = site.OpenWeb())
    SPUser user = web.Users[accountName];


  3. Jason
    13 March 2008 at 2:20 pm

    I am trying to use a BDC web service to populate properties on the user profile. The web method that’s used by the specific finder requires an employee ID parameter. So I would assume that I’d set up a 1:1 mapping and use the custom property for employee ID that I’ve set up? Account name has no context in the web service.

  4. 13 March 2008 at 3:02 pm

    Hi Jason, you can use your own field to use as an identifier as long as it is an user profile property. We just happened to have the windows account name stored and therefore used this

  5. Pallavi
    13 September 2011 at 5:04 pm

    Hi All,

    I am working in a project where user profile properties in MOSS 2007 has be populated and Changes must be Updated back to the database (MSSQL).

    I am done with popoulating the properties from Database .

    Any idea on how to update the changes made by the user back to the database.

    Thanks in advance 🙂


    Pallavi G.L

    • 25 October 2011 at 9:06 am

      There are two approaches you can take.

      1. Create a custom form on the users ‘My Site’ that updates the details directly.

      2. Create a custom job that scans the user profiles and updates the database.

      Number 2. will involve less customization to the SharePoint sites, but you are not going to get immediate updates to user profile information in your database.

  6. Kayleigh
    5 October 2012 at 5:25 pm

    Hmm is anyone else encountering problems with the images on this blog loading?
    I’m trying to determine if its a problem on my end or if it’s the blog.
    Any responses would be greatly appreciated.

    • 5 October 2012 at 5:29 pm

      Hi, I haven’t had any problems. What browser and OS are you using? Will do some further investigation.

  7. 17 October 2012 at 11:10 am
  1. No trackbacks yet.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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: