Using ASP.Net Core 3.0 Identity with MySQL


As per It's official documentation IdentityServer is a free, open source OpenID Connect and OAuth 2.0 framework for ASP.NET Core. Founded and maintained by Dominick Baier and Brock Allen, IdentityServer4 incorporates all the protocol implementations and extensibility points needed to integrate token-based authentication, single-sign-on and API access control in your applications. 

However all the examples of implementing identity server in ASP.NET Core 3.0 use MS SQL server as their back-end database, which is primaraly used by corporates not indie developers or small organisations becuase of huge license fee involved. In this article we will learn an alternate to this, we will learn how to use MySQL as back-end database for identity server implementation in ASP.Net core 3.0 in a step by step fashion

Setting Up a New Project with Identity: 

Create a new ASP.NET Core MVC project in Visual studio by following the steps mentioned in official documentation.  at Step 4 of  the blog instead of choosing Web Application choose Web Application (Model-View-Controller) , instead of No Authentication choose Individual authentication by clicking on change highlighted in below image. 

  Or If you are comfortable in using Command Prompt then use following command in Visual Studio Command Prompt:

dotnet new mvc --auth Individual

Adding Identity to an Existing ASP.Net Core Application: 

If you are like me who have already an ASP.NET Core MVC application without Identity Implemented then you just need to add Microsoft.AspNetCore.Identity nuget package from nuget package manager window (shown below) which will appear by right clicking on the Project file  and selecting the option Manage Nuget Packages [As shown in Image of Step-1 of Steps to use MySQL as back-end database].

Steps to use MySQL as Back-end database: 

Step-1: Open Nuget package manager in visual studio by right clicking on the project file and select Manage Nuget Packages as shown in below screenshot

Step-2: In the Browse tab of Nuget package manager, search for package Pomelo.EntityFrameworkCore.MySql this is the Entity Framework Core version with MySQL provider implemented as mentioned in the provider list maintained by Microsoft

Step-3: Change the connection string in appsettings.json file with your MySQL Db details something like following (Use your DB details for the Bold Parts)

  "ConnectionStrings": {
    "DefaultConnection": "server=[DB-Server Name];port=3306;database=[DB-Name];uid=[User-ID];password=[Password]"

Step-4:  Update ConfigureServices  method of  StartUp.cs file with following code

    public void ConfigureServices(IServiceCollection services)
            services.AddDbContext<ApplicationDbContext>(options =>
            services.AddDefaultIdentity<IdentityUser>(options => options.SignIn.RequireConfirmedAccount = true)

Step-5:  Delete the  Migrations folder (highlighted in below screenshot) from the project by right clicking on it and selecting Delete from the Context menu as the default Migrations code is written based on SQL Server.

Step-6:  Open the Package Manager Console from View menu of Visual Studio as shown in following screenshot:

Step-7:  Execute  following command to create new migrations code based on MY SQL database. In simple words migration code is the code responsible to create the database ( (if not present)), tables and stored procedures used by Identity server.

Add-Migration InitialCreate

Once above code is executed you will see the Migration folder created on the root of the project as shown in following screenshot:

Step-8:  Now execute  following command to apply new migration code to the database.

PM> Update-Database

That's it our ASP.Net Core application is ready with identity server implemented using  MySQL as back-end database, you can execute the application and see the login and Register pages working just like SQL Server based Identity code.

In this article we learned, how to use MySQL as back-end database for identity server implementation in ASP.Net core, Code of this sample application is available here at Github in  CoreIdentityEx Project  folder.

S Ravi Kumar

Ravi is an experienced IT professional with over 15 years of IT services experience using Microsoft technologies including like ASP.Net, C#, .Net Core, Xamarin.Forms, Azure etc.

Add a new comment