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 connecting SharePoint 2013 to the SQL Server table we created in part 1.

The first step is to create a Windows domain user account, which will be used to connect to SQL Server and execute the stored procedure we created to return the user data.  Once you’ve created the user account, grant it read permission to your test database and execute permission on the stored procedure.  You will also need to grant the same permissions to the user account you will use to create the External Content Type later.

Now we will use the Secure Store to configure the user credentials Business Connectivity Services (BCS) will use to connect to SQL Server.  Open Central Administration on your SharePoint server and navigate to Application Management -> Manage service applications -> Secure Store Service.  Click on New in Manage Target Applications.

Enter a target application ID, display name and change the target application type to Group.

Leave the fields in the next screen as they are and click next.

Enter the administrators of this application into Target Application Administrators and in Members, enter the AD user group, or AD user that you want to be able to use the saved credentials to connect to SQL Server.

You now need to configure the AD user account details for this application which BCS will use to connect to SQL Server.  From the Secure Store Service, click on the drop down arrow next to your application and click Set Credentials.  Enter the AD user account details you configured within SQL Server with permission to execute the stored procedure to return user data.

You now need to open SharePoint Designer, connect to your SharePoint site and click on External Content Types and create a new External Content Type.

Enter a Name for the External Content Type and click on “Click here to discover external data sources and define operations.

Click Add Connection and choose SQL Server as the data source type.

Enter your SQL Server name, database name and choose “Connect with Impersonated Windows Identity”  Enter the name of the application you created in the Secure Store Service.

Expand Routines under your datasource, right-click on the stored procedure you created to return user data and choose New Read Item Operation.

Leave the first screen as it is and click Next.

On the next screen ensure Map to Identifier is ticked and click Next.

Ensure Email is mapped to Email and click Finish.

Close and save the External Content Type.

Navigate to Central Administration, then Application Management -> Manage service applications -> Business Data Connectivity Service.  Click on the drop down next to your External Content Type.  Add the default content access account for your Search Service Application, set the permissions and click Ok.

In part three of the series I’ll show you how to extend the Active Directory user profiles with information from SQL Server and how to display this data in SharePoint My Sites.