Kevin Kulla Kevin Kulla - 1 month ago 20
C# Question

Azure AD using Visual Studio Violation of PRIMARY KEY constraint

I created an MVC application using visual studio 2013 and connected to our Azure AD Tenant with no issues. All of a sudden this morning i'm getting error:

Violation of PRIMARY KEY constraint 'PK_dbo.Tenants'. Cannot insert duplicate key in object 'dbo.Tenants'. The duplicate key value is (XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXX).
The statement has been terminated.

The Tenant key is our organization's Tenant Id.

This application has been in development for over a month with no issues until this morning.

The error occurs in this function that was generated when the application was created:

public static void RefreshKeys(string metadataLocation)
{
IssuingAuthority issuingAuthority = ValidatingIssuerNameRegistry.GetIssuingAuthority(metadataLocation);

bool newKeys = false;
foreach (string thumbprint in issuingAuthority.Thumbprints)
{
if (!ContainsKey(thumbprint))
{
newKeys = true;
break;
}
}

if (newKeys)
{
using (TenantDbContext context = new TenantDbContext())
{
context.IssuingAuthorityKeys.RemoveRange(context.IssuingAuthorityKeys);
foreach (string thumbprint in issuingAuthority.Thumbprints)
{
context.IssuingAuthorityKeys.Add(new IssuingAuthorityKey { Id = thumbprint });
}
foreach (string issuer in issuingAuthority.Issuers)
{
context.Tenants.Add(new Tenant { Id = issuer.TrimEnd('/').Split('/').Last() });
}
context.SaveChanges();
}
}
}

Answer

Your code is creating new Tenants without first checking if they already exist. At a guess, the system has never before dealt with a Tenant ID that is associated with more than one IssuingAuthority.

To resolve the immediate issue, check if the Tenant already exists, like this (note I haven't tested this code):

public static void RefreshKeys(string metadataLocation)
{
    IssuingAuthority issuingAuthority = ValidatingIssuerNameRegistry.GetIssuingAuthority(metadataLocation);

    bool newKeys = false;
    foreach (string thumbprint in issuingAuthority.Thumbprints)
    {
        if (!ContainsKey(thumbprint))
        {
            newKeys = true;
            break;
        }
    }

    if (newKeys)
    {
        using (TenantDbContext context = new TenantDbContext())
        {
            context.IssuingAuthorityKeys.RemoveRange(context.IssuingAuthorityKeys);
            foreach (string thumbprint in issuingAuthority.Thumbprints)
            {
                context.IssuingAuthorityKeys.Add(new IssuingAuthorityKey { Id = thumbprint });
            }

            // Get the Tenant IDs we have been supplied with
            IEnumerable<string> tenantIds = issuingAuthority.Issuers.Select(i => i.TrimEnd('/').Split('/').Last());

            // Exclude any that already exist in the database
            List<string> newTenantIds = tenantIds.Except(context.Tenants.Select(t => t.Id)).ToList();

            // Add only the new Tenant instances to the database
            foreach (string tenantId in newTenantIds)
            {
                context.Tenants.Add(new Tenant { Id = tenantId });
            }
            context.SaveChanges();
        }
    }
}