Unable to synchronize a monitored employee due to a duplicate entry in index 'IX_tblAddressUser_EmployeeID'.
Article ID: 100023660
Updated On:
Description
Error Message
Dtrace log shows this error:
Cannot insert duplicate key row in object 'dbo.tblAddressUser' with unique index 'IX_tblAddressUser_EmployeeID'. The duplicate key value is (###)
Enterprise Vault Event Log on the Compliance Accelerator server entries are:
Log Name: Symantec Enterprise Vault
Source: Accelerator AD Synchronizer
Event ID: 35
Task Category: None
Level: Error
Keywords: Classic
Description:
APP AT - Customer ID: 3 - An error occured while synchronising employee details for '{Domain}\}UserID}'. System.Data.SqlClient.SqlException: Cannot insert duplicate key row in object 'dbo.tblAddressUser' with unique index 'IX_tblAddressUser_EmployeeID'. The duplicate key value is ({UserID}).
at KVS.Accelerator.ActiveDirectory.Profile.UpdateDS(SyncProfileDS ds)
at KVS.Accelerator.Server.CentralProfileSynchroniser.SynchroniseEmployeeProfile(SyncProfileDS profileDS, Dictionary`2 adPropValues)
Log Name: Symantec Enterprise Vault
Source: Accelerator AD Synchronizer
Event ID: 26
Task Category: None
Level: Error
Keywords: Classic
Description:
APP AT - Customer ID: 3 - an error occured when updating the Profiles. System.Data.SqlClient.SqlException: Cannot insert duplicate key row in object 'dbo.tblAddressUser' with unique index 'IX_tblAddressUser_EmployeeID'. The duplicate key value is ({UserID}).
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
at KVS.Accelerator.ActiveDirectory.Profile.UpdateDS(SyncProfileDS ds)
Log Name: Symantec Enterprise Vault
Source: Accelerator AD Synchronizer
Event ID: 35
Task Category: None
Level: Error
Keywords: Classic
Description:
APP AT - Customer ID: 3 - An error occured while synchronising employee details for '{Domain}\{UserID}'. System.Data.SqlClient.SqlException: Cannot insert duplicate key row in object 'dbo.tblAddressUser' with unique index 'IX_tblAddressUser_EmployeeID'. The duplicate key value is ({UserID}).
at KVS.Accelerator.ActiveDirectory.Profile.UpdateDS(SyncProfileDS ds)
at KVS.Accelerator.Server.CentralProfileSynchroniser.SynchroniseEmployeeProfile(SyncProfileDS profileDS, Dictionary`2 adPropValues)
Cause
A monitored employee that is to synchronize with an Active Directory (AD) account already exists in Compliance Accelerator (CA) customer database. This can occur when an AD user account is set to synchronize as a Monitored Employee within CA, that account is deleted and a new account is created using the same or different AD SID but the same domain and UserID values.
When an AD user account is configured to synchronize with a CA Monitored Employee, a CA value named EmployeeID is created for that Monitored Employee entry. When the AD user account is deleted, the Monitored Employee is automatically deactivated after a number of failed synchronization attempts within a number of days. When a new AD user account is created using the same AD UserID and is configured within CA to synchronize with a new Monitored Employee entry, the EmployeeID value that CA wants to create for the new entry is found to already exist with the old entry and causes the Event ID 26 and 35 errors to be thrown into the CA server's Enterprise Vault Event Log as well as fails to populate all of the data needed for the new Monitored Employee entry.
Resolution
In the case where the ADSynchroniser process fails to add the new monitored employee due to a deactivated pre-existing Monitored Employee entry, a manual process of ensuring unique relevant information in the database is required. Follow the steps below to obtain information needed to make the required change.
1. On the CA server1.2 Select the Event ID 26 or 35 error to view its details.
1.3 In the Description field of the event, note the entry in parentheses in the line ' The duplicate key value is ({UserID}) '.
1.4 Repeat Steps 2 and 3 for each Event ID 26 or 35 entry for the latest synchronization run which is determined by looking at the date and time of the event entries.
2. Launch SQL Server Management Studio on the SQL Server hosting the CA customer database or on a workstation using an account that has SQL permissions to read the CA Customer database.
3. Connect to the SQL Server hosting the CA customer database.
4. Open a New Query window focused on the CA customer database.
5. Run one of the following SQL queries:
, EmployeeID
FROM tblAddressUser
WHERE EmployeeID = '#EmployeeID';
SELECT AddressOwnerID
, EmployeeID
FROM tblAddressUser
WHERE EmployeeID = 'auser';
5.2 If multiple Event ID 35 entries are present, replace and add to if needed #EmployeeID1, #EmployeeID2, #EmployeeeID3 with the entries noted in the repeated Step 1.3 above and then execute the query:
, EmployeeID
FROM tblAddressUser
WHERE EmployeeID IN ('#EmployeeID1'
, '#EmployeeID2'
, '#EmployeeID3'
);
SELECT AddressOwnerID
, EmployeeID
FROM tblAddressUser
WHERE EmployeeID IN ('auser'
, 'buser'
, 'cuser'
, 'duser'
, 'euser'
);
Issue/Introduction
