Extend SharePoint 2013 People Search Using Business Connectivity Services Part 3

In this four part series I will describe the process of extending the SharePoint 2013 user profile from Active Directory with additional data from a SQL Server table.  The additional data will then be displayed in users’ My Sites and SharePoint people search.

Part 1 will show the process to create the SQL Server table.  Part 2 will show the process to make the data in the SQL Server table accessible to SharePoint.  Part 3 will show the process to extend user profiles and My Sites to include the additional user data.  Part 4 will show the process to make the new user profile properties accessible in SharePoint Search.

The SharePoint environment is SharePoint 2013 Standard, with SQL Server 2008 R2 and SharePoint Designer 2013.  The steps are based on an environment where the User Profile Service has already been setup, users have been imported from Active Directory and My Sites have been configured.  It is assumed that SharePoint Search has been configured and a search site using the Enterprise Search Center template has been created.

In this post I’ll describe the process of extending the Active Directory user profiles with information from SQL Server and how to display this data in SharePoint people search and My Sites.

Open Central Administration -> Application Management -> Manage service applications and open your User Profile Service application.  Click on Configure Synchronization Connections.

BCS - AD1

On the Synchronization Connections page click on Create New Connection.  Enter a name for the connection, change the connection type to Business Data Connectivity.  In the Business Data Connectivity Entity section, select your External Content Type.  Choose to connect using a 1:1 mapping and return items based on the WorkEmail property.  Click Ok.

BCS - AD2

We now need to map the fields returned by the connection to SQL Server to fields in user profiles.  Return to your User Profile Service application in Central Administration and click on Manage User Properties.

BCS - AD3

On the Manage User Properties screen click on New Property.  I’m going to show the process to create a property called jurisdiction, which will be mapped to the jurisdiction field in SQL Server.  The process needs to be repeated for all fields you wish to add to the user profile.

Enter a name, display name and change the data type and field length to match the field you’re mapping.

BCS - AD4

Change the Policy Setting to optional, and the Default Privacy Setting to Everyone.  This allows the field to be displayed in the profile properties section of the user’s profile.  I don’t want users to be able to change the jurisdiction value, so update the Edit Settings as appropriate.  In order to display the value on the user’s profile page tick the box under Display Settings.  Ensure the Indexed box is ticked to make the property searchable.

BCS - AD5

You now need to map the user profile property to the field in the External Content Type.  Choose the Employees data source and the appropriate field to map to the user property.  Leave the direction as import and click Add, followed by Ok to save the new property.

BCS - AD6

Repeat this process for any other properties you wish to map to user profile properties.  Once you’ve mapped all the user properties run a full user profile synchronization process from Start Profile Synchronization.

BCS - AD7

Once the profile synchronization has finished, click on Manage User Profiles, then search for a user that should have been populated with data.

BCS - AD8

If you scroll through the user profile you should see your custom user profile properties have been populated.

BCS - AD9

Visit a user’s My Site and click on SHOW MORE under the user’s details to see your custom user profile properties.

BCS - AD10

In the final part of this series I’ll show you how to make the custom properties visible within SharePoint Search.

Advertisements

One thought on “Extend SharePoint 2013 People Search Using Business Connectivity Services Part 3

  1. Lucas

    Hi,

    First, thanks for your great article with detailed steps. I have followed you step by step (exactly used your database + stored procedure) but when viewing my user profile (with an email address matches my data in the database table), I didn’t see my Jurisdiction displayed, just blank. So I took a look at SP logs and found this entry “ValidateCredentialClaims – Access Denied: Claims stored in the credentials did not match with the group claim for a group app” which appeared to me that there is something incorrect with my Secure Store Service setting but I’m not sure what I did wrong.

    Please can you help me out?

    Reply

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s