During Upgrade - Microsoft.Data.SqlClient.SqlException (0x80131904): Violation of PRIMARY KEY constraint 'PK_AspNetUsers'

News Knowledge Base
,
1

Microsoft.Data.SqlClient.SqlException (0x80131904): Violation of PRIMARY KEY constraint 'PK_AspNetUsers'. Cannot insert duplicate key in object 'identity.AspNetUsers'. The duplicate key value is

Issue Description

When doing an upgrade the following exception is encountered:

Microsoft.Data.SqlClient.SqlException (0x80131904): Violation of PRIMARY KEY constraint 'PK_AspNetUsers'

This is most likely to be encountered on a PaaS upgrade but potentially could be encountered with the MSI version and in a few other scenarios.

Root Cause

The issue is with the Identity.AspNetUsers table. When this error is encountered, it is when our software is trying to make sure that all Orchestrator users exist in the identity database. This happens when the software thinks it needs to create a user that actually already exists in the identity Database.

Resolution

  1. This only happens when there is a mismatch between the isActive or isDeleted flags in the Orchestrator instance dbo.users table and the corresponding aspnetusers table in the identity database. To fix this, we need to sync the fields.
  2. These queries are for the case where the Identity service and the Orchestrator Service share the same database or they use different databases that are located on the same server (This is the most common scenario).
    1. Before running the queries, make sure to update the DB Names.
      • ORCHESTRATOR PLACEHOLDER - This is the name of the Orchestrator DB.
      • IDENTITY PLACEHOLDER - Replace this with the Identity database. In most scenarios it's the same as the Orchestrator database.
    2. Run the below query and make sure to note the affected users. Post upgrade, we may need to re-delete them. 
      • -- Identify the affected users.

        SELECT *
        
FROM [ORCHESTRATOR PLACEHOLDER].dbo.users orchUsers
        
INNER JOIN [IDENTITY PLACEHOLDER].[identity].aspnetusers identityUsers
        
ON orchUsers.[Key] = identityUsers.id
        
Where (orchUsers.IsActive != identityUsers.IsActive OR orchUsers.IsDeleted != identityUsers.IsDeleted)
        
AND orchUsers.IsDeleted = 0

        ​​​​

    3. Run the following query to update the users in the aspnetusers table. 
      • -- Update the affected users 
UPDATE identityUsers 
SET identityUsers.IsActive = 1,
     identityUsers.IsDeleted = orchUsers.IsDeleted 
FROM [ORCHESTRATOR PLACEHOLDER].dbo.users AS orchUsers 
INNER JOIN [IDENTITY PLACEHOLDER].[identity].aspnetusers AS identityUsers 
ON orchUsers.[Key] = identityUsers.id 
WHERE (orchUsers.IsActive != identityUsers.IsActive 
       OR OrchUsers.IsDeleted != identityUsers.IsDeleted) 
AND orchUsers.IsDeleted = 0
  3. This query is for the scenario where the Orchestrator Service and Identity Service have different databases and are not on the same DB server or cross-database queries do not work (This might be the case with a PaaS instance hosted in Azure).
    1. Before running the queries, make sure to update the DB Names.
      • ORCHESTRATOR PLACEHOLDER - This is the name of the Orchestrator DB.
      • IDENTITY PLACEHOLDER - Replace this with the name of the Identity DB
    2. The following queries are for fixing the isDeleted field.
      1. Run the following query: 
        • -- Query to generate list of possibly affected users
DECLARE @Ids NVARCHAR(MAX)
SELECT @Ids = COALESCE(@Ids + ', ', '') + QUOTENAME([Key], '''')
FROM [ORCHESTRATOR PLACEHOLDER].[dbo].users
AND IsDeleted = 0

          – Query to update affected users
          
SELECT ‘SELECT * FROM [IDENTITY PLACEHOLDER ].[identity].aspnetusers
          
WHERE [id] IN (’+ @Ids + ‘) AND isDeleted = 1’
          
AS queryStatement

      2. Run the generated query on the identity DB and note the affected users.
      3. The following query will generate a new query that can be run on the identity database to update the affected users for the IsDeleted field. Run the query and copy the resultant query: 
        • -- Query to generate list of possibly affected users
DECLARE @Ids NVARCHAR(MAX)
SELECT @Ids = COALESCE(@Ids + ', ', '') + QUOTENAME([Key], '''')
FROM [ORCHESTRATOR PLACEHOLDER].[dbo].users
AND IsDeleted = 0

          – Query to update affected users
          
SELECT ‘UPDATE [IDENTITY PLACEHOLDER ].[identity].aspnetusers
          
SET [IDENTITY PLACEHOLDER].[identity].aspnetusers.isDeleted = 0
          
WHERE [id] IN (’+ @Ids + ‘) AND isDeleted = 1’
          
AS queryStatement

      4. Run the generated query on the identity DB.
    3. The below query will generate a new query that can be run on the identity database to update the affected users for the IsActive field. Run the query and copy the resultant query: 
      1. -- Query to generate list of possibly affected users
DECLARE @Ids NVARCHAR(MAX)
SELECT @Ids = COALESCE(@Ids + ', ', '') + QUOTENAME([Key], '''')
FROM [ORCHESTRATOR PLACEHOLDER].[dbo].users
WHERE IsActive = 1 AND IsDeleted = 0

        – Query to update affected users
        
SELECT ‘UPDATE [IDENTITY PLACEHOLDER].[identity].aspnetusers
        
SET [IDENTITY PLACEHOLDER].[identity].aspnetusers.isActive = 1
        
WHERE [id] IN (’+ @Ids + ‘) AND isDeleted=0 AND isActive=0’
        
AS queryStatement

      2. Copy the generated query and run it on the identity DB.
      3. We do not need to worry about which users had mismatches for the isActive field. This field could vary due to a user being removed from Orchestrator directly and then re-added.
  4. After running the above queries, we need to make sure we did not compromise the DB in any way. Run the below queries:
    1. Before running the queries, make sure to update the DB Names.
      1. ORCHESTRATOR PLACEHOLDER - This is the name of the Orchestrator DB.
      2. IDENTITY PLACEHOLDER - Replace this with the name of the Identity DB
    2. INSERT INTO [IDENTITY PLACEHOLDER].[identity].UserPartitions (UserId, PartitionId)
SELECT 
    aspnetusers.Id,
    aspnetusers.MasterPartitionId
FROM [IDENTITY PLACEHOLDER].[identity].aspnetusers
WHERE NOT EXISTS (
    SELECT 1
    FROM [IDENTITY PLACEHOLDER].[identity].UserPartitions 
    WHERE UserId = aspnetusers.Id 
    AND PartitionId = aspnetusers.MasterPartitionId
)
  5. Once the upgrade is complete, make sure to log in to the identity service endpoint. For each user that was affected, evaluate their access. If they should be deleted, make sure to delete them from both the identity portal and the Orchestrator portal.