Hello,
Another day, another issue with the SharePoint UPSA and FIM.
Context
This time, it all began with an InfoPath which was fetching the manager of users to send an email. The form had worked correctly for a few months now and suddenly, it began to fail with some users only.
The issue of the form was related to empty Manager entries for those users. While it was correctly populated in the Active Directory, the field was empty in the SharePoint User Profile.
Troubleshooting
I opened the FIM client GUI to check why the Manager wouldn’t get updated correctly. During the last step where the profiles get exported in the SharePoint Profile DB, a lot of errors were saying “ma-extension-error“. Clicking on it for more details gave us this error message :
System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.AggregateException: One or more errors occurred. ---> System.InvalidOperationException: More than one DN specified for the same profile. at Microsoft.Office.Server.UserProfiles.ProfileImportExportService.AddDNLookupTable(UserProfileApplicationProxy upaProxy, Guid partitionID, Int64 recordId, String objectType, String distinguishedName)
The total errors listed for the export process reached 1153 :
The error message told me I had to check directly in the Profile database to review what was going on with those “duplicate DN”.
After a few queries in the DNLookup and UserProfile_Full table, I was able to make an initial assumption. Not so long ago, I configured the User Profile sync with the “SharePoint Active Directory Import” built-in engine by mistake. I then re-configured it using the “SharePoint Profile Synchronization” (FIM engine) but a few synchronizations had already been going on. I didn’t thought that the switch would leave any artifacts but it seems that the Profile DB kept some information of the light-weight import.
Some profiles were still in the “DNLookup” table but with a DN value not similar to the one used by the FIM engine : there is no “MVID=” characters in front of the ID. Interestingly, running the below TSQL, it retrieved 1155 results. A pretty close number to the sum of errors (1153) that the FIM client exposed 🙂
SELECT UPF.NTName, UPF.[PreferredName], DN.DN FROM [UPA-Profile-DB].[dbo].[UserProfile_Full] as UPF with (nolock) INNER JOIN [UPA-Profile-DB].[dbo].DNLookup as DN with (nolock) ON UPF.RecordID = DN.RecordId where DN.DN not like 'MVID=%'
Here are other SQL queries that helped me :
SELECT UPF.[RecordID], UPF.[UserID], UPF.[NTName], UPF.[PreferredName], DN.DN FROM [UPA-Profile-DB].[dbo].[UserProfile_Full] as UPF with (nolock) INNER JOIN [UPA-Profile-DB].[dbo].DNLookup as DN with (nolock) ON UPF.RecordID = DN.RecordId where Manager is NUll
and
SELECT UPF.[NTName], Count(UPF.NTName) FROM [UPA-Profile-DB].[dbo].[UserProfile_Full] as UPF with (nolock) INNER JOIN [UPA-Profile-DB].[dbo].DNLookup as DN with (nolock) ON UPF.RecordID = DN.RecordId Group BY UPF.NTName Having Count(UPF.NTName) > 1 Order by UPF.NTName
Solution
There is no magic solution here. Each profile that has an entry in the DNLookup table without the “MVID=” prefix must be deleted from the SharePoint User Profile. Then an incremental sync must be run to add back the users.
I scripted the solution as usual 🙂
Import-module dbatools
$SQL_Instance = ‘[your SQL server instance]’
$UPSA_DBName = ‘[your UPA Profile DB]’$UPQuery = “SELECT UPF.NTName, UPF.[PreferredName], DN.DN
FROM [$UPSA_DBName].[dbo].[UserProfile_Full] as UPF with (nolock)
INNER JOIN [$UPSA_DBName].[dbo].DNLookup as DN with (nolock)
ON UPF.RecordID = DN.RecordId
where DN.DN not like ‘MVID=%'”$SPUser_ToRemove = Invoke-Sqlcmd2 -ServerInstance $SQL_Instance -Database $UPSA_DBName -Query $UPQuery -As PSObject
$gc = Start-SPAssignment
$mySiteHost = ($gc | Get-SPSite ‘https://%5Byour mysite host url]’)
$spContext = ($gc | Get-SPServiceContext -Site $mySiteHost)
$UPM = new-object Microsoft.Office.Server.UserProfiles.UserProfileManager($spContext)Foreach ( $User in $SPUser_ToRemove ) {
Try {
Write-host “Removing user from Profile DB : ‘$($User.PreferredName)'”
$UPM.RemoveUserProfile($User.NTName)
} Catch {
Write-warning “An error occured while removing user ‘$($User.PreferredName)'”
}
}
Stop-SPAssignment $gc
Once the deletion process has finished, run an incremental sync to re-populate the users.
Resources
FIM Client GUI is located in ‘C:\Program Files\Microsoft Office Servers\15.0\Synchronization Service\UIShell\miisclient.exe‘.
It must be run as the same account declared in the Central admin.
$cred = Get-Credential ‘domain\[service account]’
Start-Process -FilePath ‘C:\Program Files\Microsoft Office Servers\15.0\Synchronization Service\UIShell\miisclient.exe’ -Credential $cred
The MMC executable found an extension with the IDÂ {EE7F2DDB-1319-4227-8FD4-4EB51615D34A} referenced as ‘SqlcmSnapin’.
Wes'Point 