ADSync issue when removing Groups in V6.4SR1 "The DELETE statement conflicted with the REFERENCE constraint..."
In Version 6.4SR1, there's a potential ADSync issue that can occur when removing AD Groups.
Scenario:
Client runs Full ADsync or extensive ADSync, and ADSync synchronized all the selected objects (users, groups, OU's etc).
Then later on the client decided to de-select certain OUs and Groups.
ADSync service was started again.
ADSync service started up, was running and updating and removing objects, but threw the following exception Error:
Inside SDconnex Logs we would see the following Jira https://jira5.winmagic.local/browse/SD-10311
Begin Message
Exception in DeleteObseleteADGroups():
System.Data.SqlClient.SqlException: The DELETE statement conflicted with the REFERENCE constraint "FK_UserGroups_Groups". The conflict occurred in database "SECUREDOC", table "dbo.UserGroups", column 'GroupIndex'.
The DELETE statement conflicted with the REFERENCE constraint "FK_UserGroups_Groups". The conflict occurred in database "SECUREDOC", table "dbo.UserGroups", column 'GroupIndex'.
The DELETE statement conflicted with the REFERENCE constraint "FK_UserGroups_Groups". The conflict occurred in database "SECUREDOC", table "dbo.UserGroups", column 'GroupIndex'.
The statement has been terminated.
The statement has been terminated.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(SqlConnection connection, CommandType commandType, String commandText, SqlParameter[] commandParameters)
at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters)
at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(String connectionString, String spName, Object[] parameterValues)
at WinMagic.SecureDoc.ADSync.SqlDataAccess.GroupDAO.DeleteObseleteADGroups()
Why is this happening?
The reason this is happening is that there are other tables that have a foreign-key relationship to the Groups table, and by deleting the Group at this point, the other tables that rely upon that group's existence are forbidding the deletion of the group.
How to Fix
The Group records to be removed should be deleted last, after all foreign-key relationships to that group have been removed.
1) Ask customer to backup up his/her database
2) Stop SDConnex and ADSync services
3) Open SQL Server Management. Go to the SES database, and Expand programmability and get into the list of stored procedures
4) Locate stored procedure > DBO.DSSync_DeleteObsoleteADGroups > then right click on that Stored Procedure and select "Modify" from the pop-up menu.
5) In this stored procedure, select and cut into the clipboard the entire SQL statement that starts with:
DELETE FROM [dbo].[Groups] WHERE ...
6) Paste that command from the clipboard to become the new last statement in the list of DELETE statements > then click on the Execute icon in the top menu, to execute the change of this Stored Procedure. This effectively MOVES the "DELETE FROM [dbo].[Groups] WHERE ..." statement to be the LAST statement in list of delete statements.
7) Re-start the SDConnex\ADSync services.
This problem should be unique to V6.4SR1 and potentially earlier versions, but will have been repaired in V6.5
Custom Fields
Error message or code: System.Data.SqlClient.SqlException: The DELETE statement conflicted with the REFERENCE constraint