Integrate IdentityServer with ASP.NET Core (Part 5 - Config in Db)

In Part 1, Part 2, Part 3, and Part 4 we touched upon various aspects of configuring IdentityServer, OAuth, and OIDC configuration in ASP.NET Core Web API and MVC applications. Although our sample application is working as expected, it stores all the IdentityServer related configuration in-memory. In a more realistic app you would like to store the configuration in some persistent data store such as SQL Server database. In this part we will do just that.

Recollect from Part 1 that the Server project's Startup class contains the following code in ConfigureServices() :

services.AddIdentityServer()
        .AddInMemoryApiResources
(ServerConfiguration.ApiResources)
        .AddInMemoryApiScopes
(ServerConfiguration.ApiScopes)
        .AddInMemoryIdentityResources
(ServerConfiguration.IdentityResources)
        .AddTestUsers
(ServerConfiguration.TestUsers)
        .AddInMemoryClients
(ServerConfiguration.Clients)
        .AddDeveloperSigningCredential();

Notice all  the "AddInMemory..." methods. They store various pieces of configuration such as API resources, API scopes, identity resources, and clients in-memory. We will now change this and store all these pieces in a SQL Server database. To accomplish this task you need to prepare a SQL Server database with a set of tables required by IdentityServer.

Begin by adding the following NuGet packages to the IdentityServerDemo.Server project:

• Microsoft.EntityFrameworkCore.SqlServer
• Microsoft.EntityFrameworkCore.Design
• IdentityServer4.EntityFramework

The Microsoft.EntityFrameworkCore.SqlServer is the EF Core data provider for SQL Server. The Microsoft.EntityFrameworkCore.Design is required because we want to use EF Core migrations. And IdentityServer4.EntityFramework provides support for EF Core.

Then open appsettings.json and store your database connection string as shown below:

"ConnectionStrings": {
  "AppDb": "data source=.;
            initial catalog=Northwind;
            Integrated Security=true"
}

Here, I am using a local installation of SQL Server and Northwind sample database. Make sure to change the connection string as per your setup of SQL Server.

Next, we will create the necessary database tables using EF Core migrations. Make sure to set the IdentityServerDemo.Server project as the startup project in the Solution Explorer.

Then open Visual Studio developer command prompt. Navigate to the IdentityServerDemo.Server project's root folder and issue the following commands :

> dotnet ef migrations 
         add OpMigration 
         -c PersistedGrantDbContext 
         -o Migrations/OpDb

> dotnet ef migrations 
         add ConfigMigration 
         -c ConfigurationDbContext 
         -o Migrations/ConfigDb

The overall working of IdentityServer involves two kinds of data - operational data and configuration data. The operational data contains authorization grants, consents, and tokens whereas the configuration data involves clients, API resources, identity resources and such things. The above commands will create the required EF Core migrations. The PersistedGrantDbContext and ConfigurationDbContext classes are provided by IdentityServer itself. They are custom DbContext classes that are required for accessing operational data and configuration data respectively.

Once you execute the above commands you will find that Migrations/OpDb and Migrations/ConfigDb folders get created in the Server project and they contain migration related files.

After creating the migrations we can update our database so that required tables get created. To do so, apply the migrations using the following commands :

> dotnet ef database update --context PersistedGrantDbContext

> dotnet ef database update --context ConfigurationDbContext

This should create a set of tables in the Northwind (or whatever database you used) as shown below:

Note that my installation of Northwind also contains ASP.NET Core Identity tables (all the tables that start with AspNet*) and a few other tables that aren't created by these commands.

Now our database is ready to persist operational and configuration data. We need to tell IdentityServer about our persistent data store.

Go to ConfigureServices() of IdentityServerDemo.Server project and modify it as shown below:

public void ConfigureServices(IServiceCollection services)
{
    services.AddControllersWithViews();

    string connStr = Configuration.
GetConnectionString("AppDb");

    Action<DbContextOptionsBuilder> dbCtx = 
(ctx => ctx.UseSqlServer(connStr));

    services.AddIdentityServer()
            .AddTestUsers(ServerConfiguration.TestUsers)
            .AddDeveloperSigningCredential()
            .AddConfigurationStore(o =>
            {
                o.ConfigureDbContext = dbCtx;
            })
            .AddOperationalStore(o =>
            {
                o.ConfigureDbContext = dbCtx;
            }); 
}

Notice the code shown in bold letters. It retrieves the database connection string from the appsettings.json file. It then creates a callback action for configuring the DbContext. Then we use AddConfigurationStore() and AddOperationalStore() methods to specify the persistent data store. The ConfigureDbContext callback sets the database connection string to Northwind database (or whatever database you specified). As you will notice, now there are no "AddInMemory..." calls in the configuration.

Although our database is now having the required tables, these tables are currently empty. We need the initial configuration data in those tables. So, the next step is to seed initial data such as clients, API resources, identity resources and so on in the database. We will do this by writing a helper method and call that helper method in the Configure() method.

So, go to Startup class of the Server project and add a private helper method called SeedIdentityServerData() as shown below:

private void SeedIdentityServerData
(IApplicationBuilder app)
{
    var scope = app.ApplicationServices.GetService
<IServiceScopeFactory>().CreateScope();

    var configDbCtx = scope.ServiceProvider.
GetRequiredService<ConfigurationDbContext>();

    if (!configDbCtx.IdentityResources.Any())
    {
        foreach (var r in ServerConfiguration.
IdentityResources)
        {
            configDbCtx.IdentityResources.Add
(r.ToEntity());
        }
        configDbCtx.SaveChanges();
    }

    if (!configDbCtx.ApiResources.Any())
    {
        foreach (var r in ServerConfiguration.
ApiResources)
        {
            configDbCtx.ApiResources.Add
(r.ToEntity());
        }
        configDbCtx.SaveChanges();
    }

    if (!configDbCtx.ApiScopes.Any())
    {
        foreach (var s in 
ServerConfiguration.ApiScopes)
        {
            configDbCtx.ApiScopes.Add
(s.ToEntity());
        }
        configDbCtx.SaveChanges();
    }

    if (!configDbCtx.Clients.Any())
    {
        foreach (var c in 
ServerConfiguration.Clients)
        {
            configDbCtx.Clients.Add(c.ToEntity());
        }
        configDbCtx.SaveChanges();
    }
}

The above code basically grabs the ConfigurationDbContext and adds various pieces of configuration such as Identity Resources, API Resources, API Scopes, and Clients. I won't go into too much details of this code since it's quite straightforward. You can also take a look at the official documentation to know more about seeding configuration data in the persistent data storage.

The SeedIdentityServerData() helper method is called in the Configure() like this :

public void Configure(IApplicationBuilder app, 
IWebHostEnvironment env)
{
    if (env.IsDevelopment())
    {
        app.UseDeveloperExceptionPage();
    }

    SeedIdentityServerData(app);
    ...
    ...
    ...
}

Note that the SeedIdentityServerData() method needs to be called only once during the initial run of the application. Once the sample data gets added into the tables this method is not needed. You might considering removing this call once the seeding is done or consider creating a separate mechanism to seed this data.

The following figure shows how API resources and Clients get added to the ApiResources and Clients tables respectively.

Now run all the projects in this sequence - Server, Web API, and Client. Try logging in with user1 as well as user2 credentials. Remember that we have removed "AddInMemory..." calls from ConfigureServices() and all the configuration is now coming from a SQL Server database. The MVC client application should run exactly as before as shown below:

If you always want the consent page to be displayed when the client application runs you can add this in the ConfigureServices() of IdentityServerDemo.Client :

.AddOpenIdConnect("oidc", o =>
{
    o.ClientId = "client2";
    o.ClientSecret = "client2_secret_code";
    ...
    o.GetClaimsFromUserInfoEndpoint = true;
    o.Prompt = "consent";
    o.Scope.Add("employeesWebApi");
    ...
}

Setting the Prompt to consent will always show the consent page to the user. 

That's it for now! Keep coding!!