02. IdentityServer4 EntityFramework

You can find the project here.

IdentityServer4 EntityFramework is the second post in my IdentityServer4 tutorial series. I highly recommend starting with IdentityServer4 Quickstart as it will make things much easier to follow. We will continue where we left of with the project created in the quickstart. You can find the quickstart project source code here.

Currently, our project is using in-memory storage for configuration data, operational data and user store. Let’s migrate everything but the user store (we will migrate that too but just not now) to permanent storage. The user store is not a feature of IdentityServer4. For IdentityServer4 we will migrate configuration store (client store, api and identity resource store, CORS policy store), operational store (persisted grants store for tokens, codes and consents) but for user store, we need to look elsewhere.

The most used user store in .Net world is ASP.NET Identity and we will use it in one of the future tutorials. We can utilize the EntityFramework code-first approach with migrations to create table structure automatically. All we need to do is provide it with an empty database and run migration commands. This example will use the local MSSQL database but I will show you how to use PostgreSQL in my next tutorial so you can keep everything open-source and free.


From in-memory to SQL

First things first. Open the “Quickstart” solution in Visual Studio. We need to install the required NuGet package. The easiest way is to right-click the “IdentityServer” project and click “Manage NuGet Packages” to open NuGet Package Manager. Click on the “Browse” tab and type in “IdentityServer4.EntityFramework”.


Click the “Install” button.

Tip: If you have issues installing the package try to update other packages first by clicking the “Updates” tab, delete the search query (“IdentityServer4.EntityFramework”) to see all packages, select all packages for update and click “Update”. Now go back to the “Browse” tab and repeat steps above to install “IdentityServer4.EntityFramework” package.


Once the package is installed you can close the NuGet Package Manager and build the solution to make sure it builds before doing any other changes.
Now we can open the “Startup.cs” file (in the root folder of the project) and take a look at the “ConfigureServices” method. We will remove the service descriptors for IdentityServer4

var builder = services.AddIdentityServer()


and replace it with IdentityServer4 service configuration that uses SQL server like so

string connectionString = Configuration.GetConnectionString("DefaultConnection");

var migrationsAssembly = typeof(Startup).GetTypeInfo().Assembly.GetName().Name;

var builder = services.AddIdentityServer(options =>
	options.Events.RaiseErrorEvents = true;
	options.Events.RaiseInformationEvents = true;
	options.Events.RaiseFailureEvents = true;
	options.Events.RaiseSuccessEvents = true;
	options.UserInteraction.LoginUrl = "/Account/Login";
	options.UserInteraction.LogoutUrl = "/Account/Logout";
	options.Authentication = new AuthenticationOptions()
		CookieLifetime = TimeSpan.FromHours(10), // ID server cookie timeout set to 10 hours
		CookieSlidingExpiration = true
.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;


There will be some errors so let’s add missing using directives at the start of the “Startup.cs” file like so

using System.Reflection;
using IdentityServer4.Configuration;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;


We will also modify the constructor to accept the injected IConfiguration reference (and create a property to hold it) so we can access the database connection string

public IHostingEnvironment Environment { get; }
public IConfiguration Configuration { get; }

public Startup(IHostingEnvironment environment, IConfiguration configuration)
	Environment = environment;
	Configuration = configuration;


You might notice that we are expecting “DefaultConnection” connection string but we don’t even have an “appsettings.json” file created yet. So let’s add it by right-clicking the “IdentityServer” project → Add → New Item… and select “App Settings File”.



This will conveniently create an “appsettings.json” file in the root folder of our project with the “DefaultConnection” already specified. We need to change the database name to something that makes a bit more sense like so

	"ConnectionStrings": {
		"DefaultConnection": "Server=(localdb)\MSSQLLocalDB;Database=IdentityServerQuickstart;Trusted_Connection=True;MultipleActiveResultSets=true"


Tip: Try to connect to the local database manually to verify the connectivity. I prefer using MSSMS (Microsoft SQL Server Management Studio) but you can use any SQL client you want, even Visual Studio. We will use Windows Authentication to connect to the database, the same as our quickstart connection string.



After a successful login creates a new empty database called “IdentityServerQuickstart” as specified in the connection string.



Alright, we are now all set to add code-first migrations to the IdentityServer4 Quickstart project and let it create a database structure (tables) needed for the Operation store and Configuration store.



Open the Package Manager Console in Visual Studio. You can easily do that by typing “package manager console” in Visual Studio search box at the top. Execute these two commands to create migrations for Operation store and Configuration store

Add-Migration InitialPersistedGrantDbMigration -c PersistedGrantDbContext -o Data/Migrations/IdentityServer/PersistedGrantDb
Add-Migration InitialConfigurationDbMigration -c ConfigurationDbContext -o Data/Migrations/IdentityServer/ConfigurationDb


Now we have new folders with structure “Data\Migrations\IdentityServer” holding the migrations for the Identity Server stores. Remember, we still didn’t migrate to the user store. We will do that in the future tutorial and the migration for user store will also go into the “Data\Migrations” folder but not under the “IdentityServer” folder.



The last step to create the tables is to update the database with the newly created migrations. Execute the commands in the Package Manager Console

Update-Database -Context PersistedGrantDbContext
Update-Database -Context ConfigurationDbContext


Congratulations! You just successfully migrated the IdentityServer4 stores to a database.



Let’s take a look at the tables and talk about each one a bit. We didn’t specify a custom schema so all tables are created in the default “dbo” schema. This can be easily updated later but is out of the scope of this tutorial.



  • “dbo.__EFMigrationsHistory” table is keeping track of the history of the code-first migrations and is not related to IdentityServer4.


  • “dbo.ApiClaims” table is holding claim types for user claims that will be included in the access token for a specific API resource.
  • “dbo.ApiProperties” table is holding additional custom key-value pairs related to the specific API resource.
  • “dbo.ApiResources” table is holding resources that represent APIs that need to be protected.
  • “dbo.ApiScopeClaims” table is defining which user claims will be included in the access token for a specified scope.
  • “dbo.ApiScopes” table is holding possible scopes for a specific API resource.
  • “dbo.ApiSecrets” table is holding API resource secrets used by the introspection endpoint (used when using access token as reference token as opposed to JWT)


  • “dbo.ClientClaims” table is holding additional claims that will be included in the access token for a specific client.
  • “dbo.ClientCorsOrigins” table is holding allowed origins for a specific client
  • “dbo.ClientGrantTypes” table is holding allowed grant types for a specific client. Grant types specify which flows and endpoints can be used for authentication and/or authorization.
  • “dbo.ClientIdPRestrictions” table specifies which external providers can be used for a specific client. If not specified the user will see all possible external providers on the login page but if the external provider is specified only those specified (whitelisted) will be available/visible to the user.
  • “dbo.ClientPostLogoutRedirectUris” table specifies which URIs are allowed for redirect after logout for a specific client.
  • “dbo.ClientProperties” table is holding additional custom key-value pairs related to the specific client.
  • “dbo.ClientRedirectUris” table specifies which URIs are allowed to redirect to after successful login for a specific client.
  • “dbo.Clients” table is holding clients. Clients in this context are actually applications (web, desktop, native, SPA, etc.)
  • “dbo.ClientScopes” table holds allowed scopes (identity and resource scopes) for a specific client.
  • “dbo.ClientSecrets” table is holding secrets for a specific client. Client Id and Client Secret is usually used for machine-to-machine authorization to obtain access token but it can be used with other flows too. The client secret is not used with apps that can’t keep a secret like native apps for example where PKCE is preferred.
  • “dbo.DeviceCodes” table is holding the (usually numeric) user codes issued for the device authorization
  • “dbo.IdentityClaims” table is holding claim types for the user claims that will be included in the access token for a specific identity resource.
  • “dbo.IdentityProperties” table is holding additional custom key-value pairs related to the specific identity resource.
  • “dbo.IdentityResources” table specifies user resources like user id, email, name, etc. Some of them are standard like “openid” which specifies the “sub” subject claim is required for OpenID Connect.
  • “dbo.PersistedGrants” table is holding permissions (grants) given to clients (apps) by the users


Required identity resource for OpenID Connect

In order for OpenID Connect to work at least one claim, the unique identifier claim aka subject claim “sub” must be provided in the access token. Usually, there are more identity resources specified like “name”, “profile”, “email” etc. In one of the next tutorials we will add data seeding to allow us to start playing with IdentityServer4. 


Wrap up

We can now remove the “Config.cs” file from the project root as that file was used for in-memory configuration of identity resources, API resources and clients, all now configurable from database.

You can find the project here.



For direct assistance schedule a technical meeting with Ivan to talk about your requirements. For a general overview of our services and a live demo schedule a meeting with Maja.

  • Jarda says:

    it is perfect serial about IdentityServer, thank you very much!

    PS: after instal package is required package “Microsoft.EntityFrameworkCore.SqlServer” for use “b.UseSqlServer” in CofigureServices

    • deblokt says:

      Thanks Jarda! I am glad you liked it. I didn’t quite understand, is there a missing NuGet package reference issue in the tutorial? Thanks

      • Anon says:

        I agree with jarda, Great tutorial so far.

        For me the NuGet package Microsoft.EntityFrameworkCore.SqlServer was needed as well to get it to compile. I think this is what Jarda meant.

        • deblokt says:

          Thank you for your response. This tutorial is for PostgreSQL (take a look at a previous tutorial for MSSQL). I don’t think “Microsoft.EntityFrameworkCore.SqlServer” package is needed as we are using a different NuGet package for PostgreSQL but I might be wrong. Thanks for the feedback!

  • Gigante says:

    To use Add-Migration command in .NET Core 3 you need to add the Microsoft.EntityFrameworkCore.Tools package.
    And the -o parameter doesn’t work, it should be -OutputDir. This is a great tutorial but it would really help people like me if this info was included. You know, I just followed it from scratch and these are problems I encountered. I didn’t choose to use .NET Core 3, that choice was made for me by Visual Studio and it seemed great as it’s the new version so I went with it.

    • deblokt says:

      Up until few weeks ago, the ID4 quickstart was using .NET Core 2.1. It’s using .NET Core 3.1 now and we need to update our tutorials and code samples. Sit tight as we are in the process of doing so. Thanks.

  • Amini says:

    Thanks, this is best tutorial in Identity Server.

  • ibrahim COBANİ says:

    I want to ask a question with your permission. I’m a little confused. In which table are users?

Leave a comment

Your email address will not be published. Required fields are marked *