SQL Database as a storage for IdentityServer4 with ASP.NET Core 6

Introduction:

This article is continuation of my previous article, where we have seen how the operational and configuration data are store in-memory for identiyServer4 with ASP.NET Core 6. In this article you will learn how to store the operational and configuration data in MS SQL Database with ASP.NET and Entityframework Core.

Prerequisites:

• Basic knowledge in building ASP.NET Core application.

•  If you are new to IdentityServer4 with ASP.NET Core 6, I strongly recommend you to read my previous article before going through this article.

Table of Content

• Create a new ASP.NET Core Project
•   Add Entity framework libraries
•   Configure Operational and Configuration Store
•   Establish the database connection
•   Add migration and update the database
•   Test the Service

Create a New ASP.NET Core Project

Create an empty ASP.NET Core project with .NET 6 framework using Visual Studio.

This project will act as an actual IdentityServer4. I have added this project into same solution which was created in my last article and named it as IdentityServerEFCore.

My solution

Add Entity Framework Libraries

Add following libraries into a project using NuGet Package manager

• IdentityServer4.EntityFramework
• Microsoft.EntityFrameworkCore.SqlServer
• Microsoft.EntityFrameworkCore.Tools
• System.Configuration.ConfigurationManager

IdentityServer4.EntityFramework -This package is used to incorporate EntityFramework to IdentityServer4, it acts as an EntityFramework persistence layer for IdentityServer4.
Microsoft.EntityFrameworkCore.SqlServer– To include the Microsoft SQL Server database provider for EntityFramework.
Microsoft.EntityFrameworkCore.Tools– Entity Framework Core Tools for the NuGet Package Manager Console in Visual Studio. By including this package, we can use the EF Core migration commands in NuGet Package manager console.

Configure Operational and Configurational Store

Open Program.cs file from the project and add following code

string connectionString = builder.Configuration.GetConnectionString("localdb");

var migrationsAssembly = typeof(Program).Assembly.GetName().Name;


builder.Services.AddIdentityServer()
.AddConfigurationStore(options =>
{
    options.ConfigureDbContext = b => b.UseSqlServer(connectionString, sql => sql.MigrationsAssembly(migrationsAssembly));
})
.AddOperationalStore(options =>
{
    options.ConfigureDbContext = b => b.UseSqlServer(connectionString, sql => sql.MigrationsAssembly(migrationsAssembly));
    options.EnableTokenCleanup = true;
});

The above code will configure and add the identity Server and it tells to Use SQL Server to store the configurational and operational data.

Add Config file in the project, and add a below code

  public static class Config
    {
        public static IEnumerable<IdentityResource> Ids =>
            new List<IdentityResource>
            {
                new IdentityResources.OpenId(),
                new IdentityResources.Profile(),
            };


        public static IEnumerable<ApiResource> Apis =>
            new List<ApiResource>
            {
                new ApiResource("api1", "My API")
            };

        public static IEnumerable<Client> Clients =>
            new List<Client>
            {
                // machine to machine client
                new Client
                {
                    ClientId = "client",
                    ClientSecrets = { new Secret("secret".Sha256()) },

                    AllowedGrantTypes = GrantTypes.ClientCredentials,
                    // scopes that client has access to
                    AllowedScopes = { "api1" }
                },
                // interactive ASP.NET Core MVC client
                new Client
                {
                    ClientId = "mvc",
                    ClientSecrets = { new Secret("secret".Sha256()) },

                    AllowedGrantTypes = GrantTypes.Code,
                    RequireConsent = false,
                    RequirePkce = true,
                
                    // where to redirect to after login
                    RedirectUris = { "http://localhost:5002/signin-oidc" },

                    // where to redirect to after logout
                    PostLogoutRedirectUris = { "http://localhost:5002/signout-callback-oidc" },

                    AllowedScopes = new List<string>
                    {
                        IdentityServerConstants.StandardScopes.OpenId,
                        IdentityServerConstants.StandardScopes.Profile,
                        "api1"
                    },

                    AllowOfflineAccess = true
                },
                // JavaScript Client
                new Client
                {
                    ClientId = "js",
                    ClientName = "JavaScript Client",
                    AllowedGrantTypes = GrantTypes.Code,
                    RequirePkce = true,
                    RequireClientSecret = false,

                    RedirectUris =           { "http://localhost:5003/callback.html" },
                    PostLogoutRedirectUris = { "http://localhost:5003/index.html" },
                    AllowedCorsOrigins =     { "http://localhost:5003" },

                    AllowedScopes =
                    {
                        IdentityServerConstants.StandardScopes.OpenId,
                        IdentityServerConstants.StandardScopes.Profile,
                        "api1"
                    }
                }
            };

    }

The above code will seed a configurational data into database. In production environment it should be dynamic.

Let’s config database seeding in Program.cs file

if(app.Environment.IsDevelopment())
{
    InitializeDatabase(app);
}
static void InitializeDatabase(IApplicationBuilder app)
{
    using (var serviceScope =
            app.ApplicationServices
                .GetService<IServiceScopeFactory>().CreateScope())
    {
        serviceScope
            .ServiceProvider
                .GetRequiredService<PersistedGrantDbContext>()
                .Database.Migrate();

        var context =
            serviceScope.ServiceProvider
                .GetRequiredService<ConfigurationDbContext>();

        context.Database.Migrate();
        if (!context.Clients.Any())
        {
            foreach (var client in Config.Clients)
            {
                context.Clients.Add(client.ToEntity());
            }
            context.SaveChanges();
        }

        if (!context.IdentityResources.Any())
        {
            foreach (var resource in Config.Ids)
            {
                context.IdentityResources.Add(resource.ToEntity());
            }
            context.SaveChanges();
        }

        if (!context.ApiResources.Any())
        {
            foreach (var resource in Config.Apis)
            {
                context.ApiResources.Add(resource.ToEntity());
            }
            context.SaveChanges();
        }
    }

The above code will seed the static data from config file into database, when you start the application.

Establish the database connection

Establish the database connection by providing the connection string in appsettings.json

"ConnectionStrings": {
    "localdb": "Server=[your server name];Database=[your database name];Integrated Security= SSPI"
  },

Add migration and update the database

Use Add-Migration command in NuGet Package Manager console.

Use below command for ConfigurationDbContext Migration

“Add-Migration InitialConfigurationDbMigration -context ConfigurationDbContext”

Use below command for PersistedGrantDbContext Migration

“Add-Migration InitialPersistedGrantDbMigration -context PersistedGrantDbContext”

It will add the migration information as shown in below figure.

Use update-database command from Package Manager Console in Visual Studio to create/update the database tables based on the migration information. Make sure you have given a database connection string.

List of tables created

Run the application, the client information in the config file will be inserted into respective tables.

Client information in table

There client information’s are inserted into a table based on the static data(Test Users) added/defined in config.cs file.

Test the Service

Let’s test the service using POSTMAN

we got a token by passing the valid client credentials.

Summary:

In this article, we covered how to store the configuration and operational data of IdentityServer4 in SQL Server instead of In-Memory with ASP.NET Core and Entity Framework core.

Source code – Get here.

I hope this article will help you to get start with IdentityServer4. Please share your queries, suggestions in the comments section below.

Happy Coding!!!