Cannot Insert Duplicate Key Row in Object 'dbo.tblEmployees'

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 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 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 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 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)

About this Article
  • Created: 05/16/2017 5:19 am EDT
  • Last updated: 08/20/2017 1:56 am EDT
In This Article