Active Directory Error - Duplicate Key Violates Unique Constraint

[aaroncouronsolv]

I am attempting to connect Qlik Sense server to an Active Directory to load the users. I am getting an error in the logs referencing "synchronizing all users for 8106ccf0-9bbc-440a-bdc8-a0164f6b07c4 was not successful duplicate key value violates unique constraint "ix_usersuserid_userdirectory""

At first I thought it might be because I was getting warnings about AD Groups that generating WARNS of Recursive group hierarchy discovered, but I added an LDAP filter to get rid of those groups from the load and am still getting this message with zero users getting loaded.

Does anybody have an idea as to why this is happening and how to resolve?

[Levi_Turner]

Hey Aaron,

I haven't seen things described exactly like this, but are there duplicate users in the QMC > Users section (aka users with the exact same User Directory and User ID values)? Filtering by the problematic ID (8106ccf0-9bbc-440a-bdc8-a0164f6b07c4) may lead you in the right direction.

[jneppl]

Hey,

i have the same problem have you been able to fix it ?

I really don`t know what is causing the insert problems.

Regards,

Johannes

[atiwari]

Hi  @Levi_Turner 

I'm having the similar issue when syncing users from Oracle (OUD) to Qlik Sense. OUD don't have any duplicates as uid or DN. I checked all the users and found them unique based on their uid.

Is it referring to any specific attribute for the user? Could you please recommend the way to troubleshoot this issue. Qlik logs are not helpful to find that user or user id which causing this issue.

Error: Synchronizing all users for 1da7aaea-30d0-47e0-91f1-000000000000 was not successful    duplicate key value violates unique constraint "ix_usersuserid_userdirectory"↵↓Failed to execute query in database: INSERT INTO "Users" ↵↓("ID","CreatedDate","ModifiedDate","ModifiedByUserName","UserId","UserDirectory","UserDirectoryConnectorName","Name","RolesString","Inactive","RemovedExternally","Blacklisted","DeleteProhibited","FavoritesString")

[atiwari]

@aaroncouronsolv  Did you find out the root cause of this issue? Please share your findings if possible.

Thanks in advance!

[Albert_Candelario]

This might be an issue in the AD sync filter, we have seen that sometimes adding (!description=ARCHIVE) have helped. Otherwise is also savvy to check the UserManagement.Repository logs and if any error/warning is there like "Recursive group hierarchy discovered at" that then is worth to check your your AD admins to pint point what could be the cause.

We hope this helps.

Cheers,

Albert

[atiwari]

@Albert_Candelario  Thanks for your response Albert. I tried the suggest filter on LDAP but still have the same results.  We only have 1 domain and configuration for the UDC sync. But i keep getting the similar error in the Qlik logs.

1906 20221219T101022.444+0530 ERROR Hostname UserManagement.Repository.Repository.Users.UserSyncEngine 43 36e41ee3-63fd-0000-0000-00000092b52e Domain\username synchronizing all users for ede6ba1b-85a6-0000-bb0c-d6b69815ce77 was not successful duplicate key value violates unique constraint "ix_usersuserid_userdirectory"↵↓Failed to execute query in database: INSERT INTO "Users" ↵↓("ID","CreatedDate","ModifiedDate","ModifiedByUserName","UserId","UserDirectory","Name","RolesString","Inactive","RemovedExternally","Blacklisted","DeleteProhibited")↵↓VALUES (:p__ID,:p__CreatedDate,:p__ModifiedDate,:p__ModifiedByUserName,:p__UserId,:p__UserDirectory,:p__Name,:p__RolesString,:p__Inactive,:p__RemovedExternally,:p__Blacklisted,:p__DeleteProhibited);↵↓Failed to save data into database↵↓Exception of type 'Repository.Core.Exceptions.TransactionException' was thrown.↵↓The operation failed.↵↓The "Add" operation failed.

I Have checked the QSR table and i don't see any duplicates in the users, users attribute or Directories tables.