Description
You cannot insert duplicate key row in object dbo.tblEmployees with the unique index IX_tblEmployees_fldEmpUserName_fldGroup.
The following error message displays in the PANAM event log.
PNMSoft Sequence Active Directory Synchronization Service.
Cannot insert duplicate key row in object 'dbo.tblEmployees' with unique index 'IX_tblEmployees_fldEmpUserName_fldGroup'. The duplicate key value is (XXX, YYY, <NULL>).
The statement has been terminated.
User object
Username: XXX
objectGUID: xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
distinguishedName: CN=CN,OU=OU,DC=DC,DC=com
Domain: YYY
Affected Versions
All versions.
Cause
The error indicates that ADSS is attempting to insert into Cora SeQuence a Domain\user name combination that already exists in the system. The Domain\user name combination is unique in tblEmployees, which is why the insert fails. Since the combination was added manually, ADSS does not "see" it in Cora SeQuence, and tries to insert it when picked up in AD. It is critical to identify the actual cause before you implement a solution. There are two possible causes for this error.
Cause 1
A single person has two different object guid values, for one of the following reasons.
- The user was manually added to Cora SeQuence, so the manual record has
Objectguid=null, and now ADSS tries to insert another record from AD with a real object guid value. - The user was already synced from AD with a different object guid than the value that is has now. For example, the user was removed from AD, and then was recreated with a new object guid.
Cause 2
A different person in AD has the same domain/user name as a user that already exists in Cora SeQuence. This is common when user names are reused. For example, a user was deleted from AD, and a new user was created (for a different person), with the same domain/user name.
Solution
There is a solution for each of the causes.
| Solution Parameter | Description |
|---|---|
| X1 | The user's objectGuid from AD that you see in the error. |
| X2 | The user's distinguishedName from AD that you see in the error. |
| X3 | The filter id (fldId) from tblADFilters that returns the user from AD. If you have more than one filter, we recommend that you validate which filter returns the user by directly querying AD. |
| XXX | The user name (as in the error). |
| YYY | The domain NetBIOS name the user belongs to (as in the error). |
Solution for Cause 1
Update the user properties in tblEmployees using the following query.
UPDATE
tblEmployees
SET
objectGUID='X1',
distinguishedName='X2',
whenChanged='1970-01-01',
fldIsFromAD=1,
fldADFilterId=X3
WHERE
fldEmpUseName='XXX'
AND fldGroup='YYY'
If the user does not sync after ADSS completes the following cycle, run the following query and restart ADSS when the system is idle.
UPDATE tblADFilters SET fldLastSyncTime = '1970-01-01'
Solution for Cause 2
Use the following query to rename the user name in tblEmployees of the old user that was deleted from AD.
UPDATE
tblEmployees
SET
fldEmpUseName=fldEmpUseName + '_deleted'
WHERE
fldEmpUseName='XXX'
AND fldGroup='YYY'
where:
XXX is the username (as in the error)
YYY is the domain NetBIOS name the user belongs to (as in the error)
