Preface: Yes, I've read many, many articles related to each aspect of this issue, but still welcome any references.

Problem: I'm unable to merge BCS (SQL connection) data with user profiles pulled in from Active Directory Synchronization (Working perfectly).

Note: When monitoring via missclient, no records are imported

Environment Info: 2013 On-premise with January 2016 CU

BCS Connection Info (Setup via SPD):

  • External Content Type to local SQL database setup with Impersonate Windows Identity using Secure Store Application
  • Read Item Operation with identity of e-mail for filter returning all columns
  • Read List Operation with limit of 1000 records
  • I'm able to read the items no problem via SPD and after creating a list & form via SPD

Business Data Connectivity Service App Info:

  • Set Object permissions to Execute & Set Permissions for all service accounts (just to eliminate the possibility)

Secure Store Service App Info:

  • Using Id in connection for secure store application id
  • Type - tried both Group & Individual
  • Credentials - Set to account
  • Even generated a new key
  • Permissions - Set to all service accounts (just to eliminate the possibility)
  • Have you tested the ECT as an external list and accessed it with the User Profile Connection account to ensure access? – Matthew McDermott Jul 27 '16 at 10:58
  • I have and while I can access the data through the external list, I was never able to sync to the data store via the User Profile Sync process. I have since turned to using a Powershell script to merge the data. I would post the code that I used to get the SQL information and how I update the SP user profile, but it's too many characters. Just send me a private msg and I'll send it to you. – Ben Willett Sep 27 '16 at 12:18
  • That's OK, I know how. What are you seeing in the logs during the import. I still suspect that you are having a permissions issue. Did you access the external list as the UPS connection account? – Matthew McDermott Sep 27 '16 at 12:33

Here is the portion of Powershell script that I used to connect directly to the SQL database, query the data and update the SP user profile:

if($mcpUserProfile["WorkEmail"] -ne $null)
 $trimEmail = $mcpUserProfile["WorkEmail"].ToString().Trim().ToUpper()

#Use sql ado connection to connect directly to sql database
$SQLServer = "ServerName\InstanceName (if applicable)"
$SQLDBName = "DatabaseName"
$SqlQuery = "select * from viewName WHERE PropertyName = '$trimEmail'" 

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = TRUE"

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd

$DataSet = New-Object System.Data.DataSet


#iterate through HCM data to find match based on e-mail address
foreach($item in $DataSet.Tables[0].Rows) {

if ($item["Email"] -ne $null) {
$trimHcmEmail = $item["Email"].ToString().ToUpper().Trim()

if($trimHcmEmail -eq $trimEmail) {

if ($item['LocationName'] -ne $null) {
#update associated location user profile properties
$userDomainAccount["EmployeeOfficeLocation"].Value = $item['LocationName']
$userDomainAccount["SPS-Location"].Value = $item['LocationName']
$userDomainAccount["Office"].Value = $item['LocationName']
#save changes to the profile                              

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.