IdentityServer4 + Asp.Net Core Identity in a single database

If you are new to IdentityServer4, the official getting-started tutorials offer an excellent background for your to start mixing and matching OIDC authentication in your projects.

Haven’t completed the tutorials yet? Well, then I strongly recommend you to do so, because there’s a lot of configuration options you’ll miss down the road if you don’t.

The goal of this tutorial is to centralize the IdentityServer + Asp.Net Core Identity tables in a single database. Your database structure will look like this:

Anyway, for this article I’ll be referring mainly to these two getting-started tutorials:

1. Using ASP.NET Core Identity
2. Using EntityFramework Core for configuration and operational data

Creating the project

So, starting with the first getting-started, follow the instructions to create a project from the IS4 + Asp.Net Identity template. Use the command:

dotnet new is4aspid -n Identity.Api

As per the tutorial, modify the Config.cs file to add your Clients.

Configuring EF Core as your storage

Head over to the second getting-started, Using EntityFramework Core for configuration and operational data.

Run these commands to install the necessary NuGet packages into your project:

dotnet add package IdentityServer4.EntityFramework
dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Design

Modify the ApplicationDbContext class to also implement IPersistedGrantDbContext and IConfigurationDbContext, which will add the following members:

#region IConfigurationDbContext

public DbSet<Client> Clients { get; set; }
public DbSet<ClientCorsOrigin> ClientCorsOrigins { get; set; }
public DbSet<IdentityResource> IdentityResources { get; set; }
public DbSet<ApiResource> ApiResources { get; set; }
public DbSet<ApiScope> ApiScopes { get; set; }

#endregion

#region IPersistedGrantDbContext

public DbSet<PersistedGrant> PersistedGrants { get; set; }
public DbSet<DeviceFlowCodes> DeviceFlowCodes { get; set; }
public Task<int> SaveChangesAsync() => base.SaveChangesAsync();

#endregion

I like to keep the IdentityServer tables in a different schema when using the same database. Add these methods to you ApplicationDbContext class so that the tables are properly configured:

private void ConfigurePersistentGrantDbContext(ModelBuilder builder)
{
    var options = new OperationalStoreOptions();
    SetSchemaForAllTables(options, "isgrants");

    builder.ConfigurePersistedGrantContext(options);
}

private void ConfigureConfigurationDbContext(ModelBuilder builder)
{
    var options = new ConfigurationStoreOptions();
    SetSchemaForAllTables(options, "isconfig");

    builder.ConfigureClientContext(options);
    builder.ConfigureResourcesContext(options);
}

private void SetSchemaForAllTables<T>(T options, string schema)
{
    var tableConfigurationType = typeof(TableConfiguration);
    var schemaProperty = tableConfigurationType.GetProperty(nameof(TableConfiguration.Schema));

    var tableConfigurations = options.GetType()
        .GetProperties(BindingFlags.Public | BindingFlags.Instance)
        .Where(property => tableConfigurationType.IsAssignableFrom(property.PropertyType))
        .Select(property => property.GetValue(options, null));

    foreach (var table in tableConfigurations)
        schemaProperty.SetValue(table, schema, null);
}

Then, call the Configure methods in the OnModelCreating method. You should end up with something like this:

protected override void OnModelCreating(ModelBuilder builder)
{
    base.OnModelCreating(builder);
    ConfigurePersistentGrantDbContext(builder);
    ConfigureConfigurationDbContext(builder);
}

Now, let’s finish the IdentityServer configuration in the Startup.cs file.

First, change the original ApplicationDbContext configuration to use SqlServer instead of SqlLite:

services.AddDbContext<ApplicationDbContext>(options =>
    options.UseSqlServer(connectionString));

Next, as stated in the IdentityServer tutorial, you’ll need to replace any existing calls to AddInMemoryClients, AddInMemoryIdentityResources, AddInMemoryApiScopes, AddInMemoryApiResources, and AddInMemoryPersistedGrants in your ConfigureServices method in Startup.cs with AddConfigurationStore and AddOperationalStore.

Your configuration should look like this:

var builder = services.AddIdentityServer(options =>
{
    options.Events.RaiseErrorEvents = true;
    options.Events.RaiseInformationEvents = true;
    options.Events.RaiseFailureEvents = true;
    options.Events.RaiseSuccessEvents = true;
    options.EmitStaticAudienceClaim = true;
})
    .AddTestUsers(TestUsers.Users)
    .AddConfigurationStore<ApplicationDbContext>(options =>
    {
        options.ConfigureDbContext = b => b.UseSqlServer(connectionString);
    })
    .AddOperationalStore<ApplicationDbContext>(options =>
    {
        options.ConfigureDbContext = b => b.UseSqlServer(connectionString);
    })
    .AddAspNetIdentity<ApplicationUser>();

After that, we just need to recreate the migrations.

Navigate to the Data\Migrations folder and delete the model snapshot and the CreateIdentitySchema migration, as these are specific to SqlLite.

Then, build your project and run this command to recreate the migrations, now including the IdentityServer schema:

dotnet ef migrations add CreateIdentitySchema -o Data\Migrations

And that’s it. You should be good to go ahead and run your project.

If you got everything right, you should see something like this:

You can also check the reference project at my GitHub:

https://github.com/phillippelevidad/identityserver4-aspnetidentity-singledatabase