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">
<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">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=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c</Property>
</Properties>
</LobSystemInstance>
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;
http://myserver/ssp/admin/_layouts/MgrDSServer.aspx
Or
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
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.
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
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];
web.Groups["MyGroup"].AddUser(user);
}
}
}
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.
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