1168-How to use LDAP Import to correct duplication of User Records or to correct GUID of User Records after Domain Merges

Issue:
 
This article shows how to eliminate duplicate user records where customers had not been using ADSync initially, then started to use AD-Sync after running SES for a while. AD-Sync-sourced user records are treated as different users. but they have the same User ID - one being the original non-domain user (which has a NULL GUID column entry in the database), and the other imported from the Domain (which will have a non-NULL GUID column entry in the database).

This can also happen where customers merge the contents of one domain into another - ADSync will see the new version of user (say) JSmith as a new user within SES because its GUID value will not be the same as the GUID value originally imported from the old Domain, so SES sees them as distinct, even though the user name is common to both records.
 
Symptom:
 
Certain customers, having started the use of AD Synch and User/Device import AFTER they had begun use of SES without it, found that they now had duplicates of all their existing users instead of seeing the existing users updated with the AD information for the same User ID.
The existing users had been created manually, but the User IDs were identical to those in AD (e.g. JSmith manually created = JSmith from AD).
 
Probable Cause:
 
It turns out the AD Synchronization tool does not do any kind of work in fixing AD information for existing manually-created users, and treats those AD Users (that do have info in their GUID field) as fundamentally different from those that are created without ADSync (that have Null/Blank in their GUID field)
 
Product version affected:
 
All SES versions
 
Environment:
 
Windows 2012, 2016, 2019
SQL 2012, 2014, 2016, 2019
 
Resolution:
 
The old LDAP Import tool can be very useful in fixing this situation.
Please make sure to back up your SQL database and take a snapshot of your VM server before conducting these procedures.
Also, where you need to use SQL to alter or clear out information within the SES Database (e.g. duplicated Users).
 
Here are the steps to follow to clean up the customer's SES environment.
NOTE: Change the "use Securedoc" database clause to match the name of the database you're trying to fix (substitute the proper database name).
 
Clean existing AD-sync-imported Users
 
1.1 - First, verify that none of these users have any Key or Device relationships by running the following SQL
 
    // The following SQL will determine if there are any computer/Key relationships in existence
 
    // for users added to the User table by the AD Import utility.
 
    //
 
    // We want this to return 0 rows, indicating that there are no devices related to any of the
 
    // ADSync-added Users.
 
     USE securedoc ;
 
     SELECT * FROM compuserskeys WHERE dbk_index IN
 
    ( SELECT  dbk_index FROM tbldbks
 
    WHERE ADDistName IS NOT NULLl )
 
1.2 - Verify visually the records that will be deleted by running the following SQL:
 
    // The following SQL will list all the current AD-imported Users
 
    // We want this to return all AD-Sync'd users
 
    USE securedoc ;
 
    SELECT  *
 
    FROM tbldbks
 
    WHERE ADDistName IS NOT NULL
 
 
2 - Configure the LDAP Import by setting up values in LDAP_ImprtSettings.ini to suit the portion of AD to be imported. It may be desirable to run this several times with different subsets to avoid bringing in unnecessary aspects of the AD.
 
    [Section Settings]
 
    ;    Host = (enter Domain Controller server name)
       Host = enteryourDNSServerhostname_or_IP
 
    ;   Port = 636
    ;   Domain = dc=winmagic,dc=local
 
    ;   Login = (enter user ID with credentials suitable for AD read)
       Login = enteryourdomainname\enteryouradminuserid
 
    ;   Login = [email protected]
    ;   Login = cn=Alex Morris,ou=mississauga,dc=winmagic,dc=local
    ;   Login = AlexM
 
    ;   TLS/SSL = 1
    ;   SEC_WINNT_AUTH = 1
 
    [End of Section]
 
 
    [Section DirScan]
 
    ;   SearchBase = OU=Mississauga,DC=winmagic,DC=local
 
       SearchBase = DC=domainname,DC=corproot,DC=com
 
    ;   PageSize = 10
       DN2userID = 1
 
    ;   CertPurpose= 30
    ;   CertIssuer = CN=Winmagic
 
 
    [End of Section]
 
    [Section MatchRules]
       DBK_ID = sAMAccountName
       FirstName = givenName
       LastName = sn
       Phone = telephoneNumber
       Email = mail
    ;   DBK_Certificate = UserCertificate <-- NOTE: For one customer, I suppressed this by commenting the line because import was failing on one specific certificate).
       DBK_UserID = DistinguishedName
       GUID = objectGUID
 
    [End of Section]
 
    ; Please DO NOT touch anything below this line.
    ; The rest of the file is processed automaticaly
 
    [DirectoryAccess]
    ;Password=nAAAAAoApf8ABFUAAAD///9/////fwEAAAAAAAAAgQKFEIIQjV17BgnHePYAAAAAAAAAAIUBRwMAAAAgAAAAnHCPEhvJIhCV+k1ZOG5y8xMNUjHIUwdU82cS7zTcaSA9AAAA////f////38CAAAAAAAAAIcBAIMCYQCEIFFK327+ICq//RhmGJ6WYhCtMQnnPKb5/4nNF0J0Bhj9Ga25ZBhGRYzLvAbxChtkz997+ilrTti+hMmz0x33Lph=
 
 
3.1 - Stop SDConnex
 
3.2 - Stop ADSync, if running
 
4 - Take a snapshot of the VM server so there's a roll-back position (if using VMware), or do a full database backup.
 
5 - Run the following SQL to delete the unnecessary Delete_ADSyncUsers_Pre-Fix.txt
 
    // The following SQL will DELETE all the current AD-imported Users
 
    // THIS SHOULD ONLY BE RUN AFTER A FULL BACKUP HAS BEEN DONE OF THE VM SERVER or database
 
    USE securedoc ;
 
    DELETE FROM tbldbks
 
    WHERE ADDistName IS NOT NULL
 
6 - In the SES console, refresh the view, then review which users are left - ensure the expected number of devices are present
 
7 - Run the LDAP Import utility, using the LDAP_ImportSettings.ini file as the configuration. There's another document that covers how to use this tool.
 
8 - In the SES console, review that we now have more users, but no single user should be duplicated.
 
9 - In the SQL management tool, run an SQL to determine if we have the same user name more than once.
 
    // Find duplicate User IDs
 
    USE securedoc;
 
    SELECT COUNT(*)
 
    FROM tbldbks
 
    GROUP BY dbk_id
 
    HAVING COUNT(*) > 1
 
 
10 - Using the AD Sync Console, log into and choose the domain.
 
11 - Using the AD Sync Console, select the portion of the domain to be sync'd, save settings and run Full Sync to force re-import