An error occurred while migrating orchestrator data. Violation of PRIMARY KEY constraint 'PK_AspNetUserLogins'. Cannot insert duplicate key in object 'identity.AspNetUserLogins'. The duplicate key value is (Windows.Scheme, S-1-5-21-705512089-1558976605-4064737748-16945, 52da69bb-f3cb-4d90-806d-64bbbe6321bf)
Resolution:
If an upgrade fails with this error, request to restore the Database, as a new identity schema is updated in the target database.
The below query will return the duplicate UserIDs and their count :
select UserId, count(UserId) from dbo.UserLogins
group by UserId, LoginProvider,ProviderKey,Tenantid
having count(UserId) > 1
group by UserId, LoginProvider,ProviderKey,Tenantid
having count(UserId) > 1
The below query will give the ID corresponding to the duplicate userIDs.
Select ID, userID from dbo.UserLogins_test where userID in
(
select UserId, count(UserId) from dbo.UserLogins
group by UserId, LoginProvider,ProviderKey,Tenantid
having count(UserId) > 1
)
(
select UserId, count(UserId) from dbo.UserLogins
group by UserId, LoginProvider,ProviderKey,Tenantid
having count(UserId) > 1
)
From the above query, keep only 1 record per userID and delete the rest of the records. For example. if the result of the above query is:
ID userID
21 30
23 30
25 30
21 30
23 30
25 30
then run query - delete from dbo.UserLogins_test where id in (21,23) to delete ID 21 and 23 and keep 25.