fbpx

02. IdentityServer4 EntityFramework .NET Core 3.1

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.

After installing “IdentityServer4.EntityFramework” we need to install two more packages. First is “Microsoft.EntityFrameworkCore.SqlServer”

 

and second package is “Microsoft.EntityFrameworkCore.Tools”

 

Once the packages are 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()
                .AddInMemoryIdentityResources(Config.GetIdentityResources())
                .AddInMemoryApiResources(Config.GetApis())
                .AddInMemoryClients(Config.GetClients());

 

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;
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 IWebHostEnvironment Environment { get; }
public IConfiguration Configuration { get; }

public Startup(IWebHostEnvironment 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.NetCore3.1;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.NetCore3.1” 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.

 

Migrations

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.

 

Tables

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.

 

Support

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.

Comments
  • Katie says:

    Hi, I had a couple of things I had to work around to get this to work for me, thought I’d mention in case anyone else hit them too.
    – I wasn’t able to add the nuget packages until I manually changed the project’s target to .Net Core 3.1, it was still on 3.0. I also had to upgrade the base IdentityServer4 nuget before I could add them, but that is mentioned in a tip in the article.
    – In addition to those mentioned, I had to add the namespace System, in order to get the TimeSpan object to be recognized.
    – I had a compiler error that the Type IHostingEnvironment was ambiguous, and obsolete. I used the recommendation in the Visual Studio help to instead use the type IWebHostEnvironment.

    Just a heads up in case anyone else runs into them. I’m probably just working with different versions of various software that the tutorial didn’t exactly line up with.

    • deblokt says:

      Dear Katie,

      I am not sure what you are looking at, maybe one of our older tutorials for .NET Core 2.1, but all points you mention are not valid for this .NET Core 3.1 tutorial.

      • Katie says:

        I’m following your entire .Net Core 3.1 tutorial series…started with : 01. IdentityServer4 Quickstart .NET Core 3.1 and then this one.

        It’s almost like the template wasn’t set to 3.1 though.

  • Oliver Kane says:

    There’s a missing set of backslashes in the default connection string provided. It reads
    {
    “ConnectionStrings”: {
    “DefaultConnection”: “Server=(localdb)MSSQLLocalDB;Database=IdentityServerQuickstart;Trusted_Connection=True;MultipleActiveResultSets=true”
    }
    }

    But should be
    {
    “ConnectionStrings”: {
    “DefaultConnection”: “Server=(localdb)\\MSSQLLocalDB;Database=IdentityServerQuickstart;Trusted_Connection=True;MultipleActiveResultSets=true”
    }
    }

    • deblokt says:

      Hi Oliver,

      Seems like there is an issue with our website as this affects all tutorials as they are all missing “\” character. Working on getting it fixed. Thank you for reporting!

  • Karen says:

    I saw the exact same issues as Katie 1/31 on this code set.
    I followed her answers.
    Added Using System
    Used pubic IWebHostEnvironment for the Environment variable
    and on public Startup)IWebHostEnvironment environment, IConfiguration configuration)

    These allowed this to build for me also.

  • Dangelo says:

    The name of the database and the connection string have to be THE SAME:
    IdentityServerQuickstart.NetCore3.1 or IdentityServerQuickstart, but can´t be both.

    Very nice job doing this update. Thank you so much!

  • Lenny says:

    connectionString and migrationsAssembly: “does not exist in the current context”

  • Lenny says:

    found it, tutorial steps missing lines
    string connectionString = Configuration.GetConnectionString(“DefaultConnection”);
    var migrationsAssembly = typeof(Startup).GetTypeInfo().Assembly.GetName().Name;

  • Oleg says:

    If I’m migrateing the IdentityServer4 stores to a database, can I specify a different schema? If yes, how? Thank You!

  • Gopal Chettri says:

    Hello Deblokt,
    Thank you very much for writing such a beautiful tutorial for novice like us.
    While following the tutorial i found that there are difference in the table structure in ApiResource and Clients, which again creates issue in the seeding data. Kindly refer the image links.
    https://ibb.co/YLj9fLG
    https://ibb.co/LYpw4Lw
    https://ibb.co/WcmMHL4
    https://ibb.co/Zz1y944

Comments are closed.