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)
Please find the below steps for the Solution.
If Upgrade is failed with the above error, request to restore the Database since new identity schema is updated in the target database.
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
Below query will give the ID corresponding to 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
)
From above query, you need to keep just 1 record per userID and delete rest of the records. For example. if the result of above query is
ID userID
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.