Duplicate User Profiles in User Profile Service Application in SharePoint 2013

Hello Everyone !!

I recently encountered this weird of duplicate user profiles in UPS (user profile service application) in my SharePoint 2013 farm, where I noticed around 42,000 user profiles in my UPS whereas the actual user base is 21.000 only.

This all started when we migrated our SharePoint farm from 2010 to SharePoint 2013, prior to this everything was working good.

Lets see how to resolve this issue.

The Problem:

This problem occurs if the ObjectSid (AD attribute) to SID ( SharePoint attribute ) mapping  is missing from MOSS management agent (FIM). A typical situation would be if this mapping is removed  from the Manage User Properties page in Manage User Profile Service Application page or via SharePoint management shell. 

This mapping is created while we provision the AD synchronization connection by the  Rules Extension DLL, as seen in Configure Attribute Flow section of AD Management Agent, and is stored in the synchronization DB.
So once this is removed it is permanently deleted. (Unreversable)

While this is removed from UI, there is no warning message shown to the end user indicating that this will cause issues and should not be removed.

How we verified this is the issue ;

1)      I have checked the MIISClient for ObjectSID -> SID flow was missing.

2)      In SharePoint -> CA -> User Profile Sync Service Application -> Manage User Properties .

Mapped Attribute “objectSid” was not there !.

Resolution is:

The way out in this situation is to Reset the Sync DB and then re-create the sync cnnections. So it is highly recommend to note down the following details before recreating  the UPA.

Sync connection details as

1)      Selected OU Details

2)      AccountName used to Query AD sources

3)      Any User / Group connection filters specified in the sync connection

4)      Custom Property Mappings for additional attributes

5)      Configuration settings for any Business Connectivity Services (BCS connector) etc. along with the attribute mappings

Manual creation of mappings is not supported due to restriction of using FIM UI for any configuration changes as described in KB 2517937

It is highly suggested to get full database backup for all User Profile Databases (ProfileDB, SocialDB,SyncDB) doing a “Reset a Sync DB” operation.

Possible Action Plan is “Reset the Sync DB” 

===================

1) Login as farm account

2) Backup the User Profile DB , Social DB and the User Profile Sync DB

3) Stop the SharePoint 2013 Timer service:

PS D:\> net stop sptimerv4

4) Delete the data in the Sync DB using the following PowerShell script:
PS D:\> Get-SPDatabase

5) Copy the GUID associated with the User Profile Sync DB in the command line below
PS D:\> $syncdb=Get-SPDatabase -Id

6) Execute these commands, in exactly the following order. This is not a script. So please cut and paste each of these commands one by one.
PS D:\> $syncdb.Unprovision()
PS D:\> $syncdb.Status=’Offline’
PS D:\> Get-SPServiceApplication
#Copy the GUID associated with the User Profile Service and paste it after “Id” in the next command:
PS D:\> $upa=Get-SPServiceApplication -Id

$upa.ResetSynchronizationMachine()
PS D:\> $upa.ResetSynchronizationDatabase()

7) Provision the Sync DB:
PS D:\> $syncdb.Provision( )

8 ) Add the User Profile Synchronization service account (farm account) as the dbowner on the Sync DB (using SQL Server Management Studio).

9 ) Start the SharePoint 2013 Timer service
PS D:\> net start sptimerv4

10) Start the User Profile Synchronization Service in the Central Administration UI.

11) After the User Profile Synchronization Service is started, reset IIS.
PS D:\> iisreset
12) Create connections to data sources in the Central Administration UI.

13) Run full user profile synchronization.

*You may need more than once to run Full User profile syncronization. At least 2 times.

Post the synchronisation was complete we were able to see the SID in manage user properties.

For Root Cause;

Unfortunately it is hard to say who or how it is broken or deleted.

Possibilities

-          It may be deleted from FIM Client

-          It may be deleted from SharePoint UI

-ObjectSid may be mapped twice more than once for another property.