Violation of UNIQUE KEY constraint 'UQ_ActiveDirectoryUser'. Cannot insert duplicate key in object 'dbo.ActiveDirectoryUser_MT at Writer 'ImportActiveDirectoryUsers'

[#1]

Summary

Reconcile fails at Writer 'ImportActiveDirectoryUsers' with error "System.Data.SqlClient.SqlException (0x80131904): Violation of UNIQUE KEY constraint 'UQ_ActiveDirectoryUser'. Cannot insert duplicate key in object 'dbo.ActiveDirectoryUser_MT'".

Symptoms

During a Compliance Import, you may encounter the following error:

2016-09-09 22:01:43,267 [ERROR] System.Data.SqlClient.SqlException (0x80131904): Violation of UNIQUE KEY constraint 'UQ_ActiveDirectoryUser'. Cannot insert duplicate key in object 'dbo.ActiveDirectoryUser_MT'. The duplicate key value is (671f79bf-f918-9e4f-8ddb-ed3bd94c8c89).
Deleted 1168 Active Directory Users
Updated 71122 Active Directory Users
The statement has been terminated.
Added 0 Active Directory Users
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at ManageSoft.Compliance.Importer.Logic.XML.Writer.Execute(IExecutionContext context)
at ManageSoft.Compliance.Importer.Logic.ComplianceImporter.ExecuteWriters(ComplianceReader p_ComplianceReader, IExecutionContext context, String singleConnectionIdentifier)
ClientConnectionId:1eb080b7-9f29-457d-b78b-caf6af3892a0
Error Number:2627,State:1,Class:14

Cause

The duplicate key value in the error is due to a duplicate GUID (671f79bf-f918-9e4f-8ddb-ed3bd94c8c89) trying to be inserted into the #ActiveDirUser temp table during the writer stage but as the Domains do not match, they are treated as individual records. This can occur when there are 2 separate inventory sources that are importing Active Directory users and that user has moved from one domain to another. This would create two separate records in the ImportedActiveDirectoryUser table with different ComplianceConnectionIDs and different Domains yet the same GUID and SAMAccountName. This can be identified by running the following query:

SELECT * FROM ImportedActiveDirectoryUser
WHERE GUID = '671f79bf-f918-9e4f-8ddb-ed3bd94c8c89' --Change GUID here

Resolution

If this does occur, it is recommended to identify the invalid record (i.e the invalid domain for that user) and make a note of the ComplianceConnectionID for that record. You can then identify the source by running the following query:

SELECT * FROM ComplianceConnection
WHERE ComplianceConnectionID = '1' --Change ComplianceConnectionID here

Once you know the inventory source, please execute an Active Directory import to update that inventory source. You should then find that the record with the invalid domain should now be removed from that source and the next compliance import will be successful.