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 making the custom user profile properties you populated in the last post visible within SharePoint Search.

Open Central Administration -> Application Management -> Manage service applications and open your Search Service Application.  Click on Content Sources, then click on the drop down for your user profile content source and run start an incremental crawl.

Once the crawl has completed click on Search Schema under Queries and Results.

Click on Crawled Properties, then in the managed property box search for one of your custom user profile properties.

Your custom property should be listed as People:XXXX as the property was added as a user property.  Click on the custom property and check the Include in full-text index box is ticked.

Return to the Managed Properties screen and click on New Managed Property.

Enter a property name, e.g. Jurisdiction and select the correct data type.

There are many options to experiment in this screen, but as a minimum for this example tick Queryable (this appears to be a requirement for the refiner option), Retrievable, which allows us to display the property in search results and set Refinable to Yes – active as I want to be able to refine search results using this custom property.  Finally, map this new managed property to the custom user profile property and click Ok.

Repeat these steps for all the other custom properties you wish to use in search.  Once you’ve created all your managed properties run a full crawl of your content source.

Once the full crawl has completed we need to add the custom properties to the list of refiners and search result.  Navigate to your search site and search for a user that will have the custom properties populated.

Edit the search results page, and click on Edit Web Part for the Refinement web part.

In the Properties for Search Refinement screen click on Choose Refiners.

From the screen that loads, select the custom property you wish to add as a refiner and click Add, then click Ok.

Click OK on the Properties for Search Refinement web part.  You should now see your new refiner.

If you’re happy check in and publish the page.

Now we I’m going to show you the process to display a custom property under the user’s details.  Edit the Search page and click on the display template link.

On the display template page choose to download a copy of the existing People Item template.

Create a copy of the downloaded template and open it in a text editor like Notepad++.  Update the title field to indicate this is your custom display template.

Add your custom user property name to the ManagedPropertyMapping section.

Create a variable for your custom property.

I’ve copied the department section and replaced the variable etc. with jurisdiction.  I’ve also added Jurisdiction:  in the value DIV as this will add a prefix to the value on the search result screen.

Open SharePoint Designer, connect to your SharePoint site and navigate to Page Layouts -> Display Templates -> Search.  Click on Import Files in the ribbon and upload your custom display template.

Navigate to the display template gallery and publish a major version of your template.

Using the link on the search results page, open the Manage Result Types page.  Use the drop down for the Person result type to create a copy.

Enter a name for the new result type and select your custom display template.

In my environment the updated display template worked straight way.  However, if you need to change the display template, on the people search results page edit the web part properties on the People Search Core Results.

Select your custom display template and save changes.

You should now see the custom property in the search results.

Troubleshooting

If you have problems getting this to work the following tools may help:

Monitor connections to the SQL Server during the user import task using SQL Server Profiler.  This will enable you to see if SharePoint is connecting to the SQL Server and the commands that are being run.

Monitor the user import process using Synchronization Manager Service, which can be found at “C:\Program Files\Microsoft Office Servers\15.0\Synchronization Service\UIShell\miisclient.exe”.

Monitor SharePoint logs using the ULSViewer.  You can create a filter to include results only from Business Data Connectivity Services. http://archive.msdn.microsoft.com/ULSViewer