Eventually, you will want to restrict access to some/all pages at your .NET Core MVC application, so everyone would have to enter their login and password first (authenticate themselves), and then server will decide whether to let them open the page or not (authorize the access).

Official manual guides you through the process pretty nicely, however it only tells about setting it up with MS SQL Server. But we, of course, would like to use MySQL for that.

.NET Core Identity MySQL

I tried to use MySQL with .NET Core Identity before, but something was really wrong with its support back then, and now it actually works (more or less).

Why use Identity at all? Of course, you can create your own username/password authentication yourself, but your own custom bicycle is unlikely to be as good as Identity, which takes care of lots of stuff, including proper passwords storing (you didn’t plan to store them as plain text, did you), ready-made views and models, already implemented roles mechanism, social logins and so on.

.NET Core 2.0

Before we start, here’s my dotnet version just in case:

$ dotnet --info
.NET Command Line Tools (2.1.4)

Product Information:
 Version:            2.1.4
 Commit SHA-1 hash:  5e8add2190

Runtime Environment:
 OS Name:     Mac OS X
 OS Version:  10.13
 OS Platform: Darwin
 RID:         osx.10.12-x64
 Base Path:   /usr/local/share/dotnet/sdk/2.1.4/

Microsoft .NET Core Shared Framework Host

  Version  : 2.0.5
  Build    : 17373eb129b3b05aa18ece963f8795d65ef8ea54

Assuming you have used the template with a built-in Identity authentication/authorization (dotnet new mvc --auth Individual), I’ll skip the controllers, models and views. If you already have an existing project and want to add it there, then no problems, just create a new one using this template anyway and simply copy its models and views to your project.

Install the following NuGet packages:

  • Microsoft.EntityFrameworkCore.Tools (2.0.2);
  • Microsoft.EntityFrameworkCore.Design (2.0.2);
  • MySql.Data (6.10.6);
  • MySql.Data.EntityFrameworkCore (6.10.6).

Those are the versions I have, but most probably other version will be fine too.

Add connection string to your database in appsettings.json

"ConnectionStrings": {
    "DefaultConnection": "server=localhost;port=3306;database=some;user=some;password=some;CharSet=utf8;"
}

Add IdentityContext class to your project, /Data/IdentityContext.cs:

using Microsoft.AspNetCore.Identity;
using Microsoft.AspNetCore.Identity.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore;

namespace YOUR-PROJECT-NAMESPACE.Data
{
    public class IdentityContext : IdentityDbContext<IdentityUser>
    {
        public IdentityContext(DbContextOptions<IdentityContext> options)
            : base(options)
        {
        }

        protected override void OnModelCreating(ModelBuilder builder)
        {
            base.OnModelCreating(builder);
        }
    }
}

I did not create my own ApplicationUser class, because I decided to take the default IdentityUser, so I replaced it in views Login.cshtml and _LoginPartial.cshtml and also changed the AccountController (and ManageController) constructor as follows:

namespace YOUR-PROJECT-NAMESPACE.Controllers
{
    public class AccountController : Controller
    {
        private readonly UserManager<IdentityUser> _userManager;
        private readonly RoleManager<IdentityRole> _roleManager;
        private readonly SignInManager<IdentityUser> _signInManager;
        private readonly ILogger _logger;
        private readonly IdentityContext _context;
        private readonly IConfiguration _configuration;

        public AccountController(
            UserManager<IdentityUser> userManager,
            RoleManager<IdentityRole> roleManager,
            SignInManager<IdentityUser> signInManager,
            ILoggerFactory loggerFactory,
            IdentityContext context,
            IConfiguration configuration
            )
        {
            _userManager = userManager;
            _roleManager = roleManager;
            _signInManager = signInManager;
            _logger = loggerFactory.CreateLogger<AccountController>();
            _context = context;
            _configuration = configuration;
        }

        // ...
    }
}

Enable Identity authentication/authorization in Startup.cs:

public void ConfigureServices(IServiceCollection services)
{
    // that's where you tell that you want to use MySQL
    services.AddDbContext<IdentityContext>(
        options => options.UseMySQL(Configuration.GetConnectionString("DefaultConnection"))
    );
    services.AddIdentity<IdentityUser, IdentityRole>().AddEntityFrameworkStores<IdentityContext>();

    services.Configure<IdentityOptions>(options =>
    {
        // Password settings
        options.Password.RequireDigit = true;
        options.Password.RequiredLength = 8;
        options.Password.RequireNonAlphanumeric = true;
        options.Password.RequireUppercase = true;
        options.Password.RequireLowercase = true;

        // Lockout settings
        options.Lockout.DefaultLockoutTimeSpan = TimeSpan.FromMinutes(30);
        options.Lockout.MaxFailedAccessAttempts = 10;

        // User settings
        options.User.RequireUniqueEmail = true;
    });

    // If you want to tweak Identity cookies, they're no longer part of IdentityOptions.
    services.ConfigureApplicationCookie(options => options.LoginPath = "/Account/Login");

    // If you don't want the cookie to be automatically authenticated and assigned to HttpContext.User,
    // remove the CookieAuthenticationDefaults.AuthenticationScheme parameter passed to AddAuthentication.
    services.AddAuthentication(CookieAuthenticationDefaults.AuthenticationScheme)
            .AddCookie(options => {
                options.LoginPath = "/Account/Login";
                options.LogoutPath = "/Account/Logout";
                options.ExpireTimeSpan = TimeSpan.FromDays(150);
            });

    services.AddMvc(
        options =>
        {
            var policy = new AuthorizationPolicyBuilder()
                .RequireAuthenticatedUser()
                .Build();
            options.Filters.Add(new AuthorizeFilter(policy));
            // ...
        }
    );

    // ...
}

public void Configure(
    IApplicationBuilder app,
    IHostingEnvironment env,
    ILoggerFactory loggerFactory
    )
{
    // ...

    app.UseAuthentication();

    // ...
}

Couple of (lame) words about Entity Framework. Identity can use various storages for its users/passwords/roles/whatever information. Here we use a MySQL database. And by using so-called code-first approach Entity Framework generates SQL statements from Identity internal C# models (tables for users, roles, etc and relations between them). The process of generating these statements and applying them to database is called migration. At least that’s how I understand the process, so better read some books on the subject.

We have our Identity models ready for migration, so let’s perform one. Make sure that you have dotnet version 2.x.x and not 1.x.x, and also that you are in the directory with YOUR-PROJECT.csproj. Run this command:

dotnet ef migrations add InitialCreate

Most likely you will get the following error:

No executable found matching command "dotnet-ef"

The solution for that was found in this thread (and other places): make sure that you have the following lines in YOUR-PROJECT.csproj:

<ItemGroup>
    <DotNetCliToolReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Tools" Version="2.0.0" />
    <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="2.0.0" />
</ItemGroup>

Run dotnet restore. Mine failed with this error:

error MSB4024: The imported project file "project/obj/project.csproj.EntityFrameworkCore.targets" could not be loaded. Unexpected end tag.

I openned the file, deleted this “unexpected” tag, and cleaned and rebuilt the project (just in case).

Having done that, try to check EF tools:

$ dotnet ef

                     _/\__
               ---==/    \\
         ___  ___   |.    \|\
        | __|| __|  |  )   \\\
        | _| | _|   \_/ |  //|\\
        |___||_|       /   \\\/\\

Entity Framework Core .NET Command Line Tools 2.0.0-rtm-26452

Usage: dotnet ef [options] [command]

Options:
  --version        Show version information
  -h|--help        Show help information
  -v|--verbose     Show verbose output.
  --no-color       Don't colorize output.
  --prefix-output  Prefix output with level.

Commands:
  database    Commands to manage the database.
  dbcontext   Commands to manage DbContext types.
  migrations  Commands to manage migrations.

Use "dotnet ef [command] --help" for more information about a command.

Okay! Now perform your initial migration (stop debugging if you have it running in Visual Studio, otherwise you’ll get an error The process cannot access the file):

$ dotnet ef migrations add InitialCreate

info: Microsoft.AspNetCore.DataProtection.KeyManagement.XmlKeyManager[0]
      User profile is available. Using '/Users/vasya/.aspnet/DataProtection-Keys' as key repository; keys will not be encrypted at rest.
info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 2.0.2-rtm-10011 initialized 'IdentityContext' using provider 'MySql.Data.EntityFrameworkCore' with options: None
info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 2.0.2-rtm-10011 initialized 'IdentityContext' using provider 'MySql.Data.EntityFrameworkCore' with options: None
Done. To undo this action, use 'ef migrations remove'

Good. You should get the following files generated:

  • /Migrations/20180318174058_InitialCreate.cs;
  • /Migrations/20180318174058_InitialCreate.Designer.cs;
  • /Migrations/IdentityContextModelSnapshot.cs.

Apply this migration to the database:

dotnet ef database update

…At least, try to. Because for me that produced the whole bunch of errors:

MySql.Data.MySqlClient.MySqlException (0x80004005): Table 'YOUR-PROJECT-NAMESPACE.__efmigrationshistory' doesn't exist

From this answer I got that MySQL (or whoever) doesn’t properly support migrations yet, so you need to create this table manually (via mysql or DBMS of your choice):

CREATE TABLE `__EFMigrationsHistory` (
    `MigrationId` NVARCHAR (150) NOT NULL,
    `ProductVersion` NVARCHAR (32) NOT NULL,
    PRIMARY KEY (`MigrationId`)
);

Having run dotnet ef database update after that, I got a new error:

MySql.Data.MySqlClient.MySqlException (0x80004005): Specified key was too long; max key length is 3072 bytes

This one happened because somewhere in MySQL EF (or wherever) there is some mess with the composite keys, so they have total length that exceeds the limit. I hope that will be fixed in future, but meanwhile here’s a workaround: edit your /Data/IdentityContext.cs:

protected override void OnModelCreating(ModelBuilder builder)
{
      base.OnModelCreating(builder);

      builder.Entity<IdentityUser>(entity => entity.Property(m => m.Id).HasMaxLength(85));
      builder.Entity<IdentityUser>(entity => entity.Property(m => m.NormalizedEmail).HasMaxLength(85));
      builder.Entity<IdentityUser>(entity => entity.Property(m => m.NormalizedUserName).HasMaxLength(85));

      builder.Entity<IdentityRole>(entity => entity.Property(m => m.Id).HasMaxLength(85));
      builder.Entity<IdentityRole>(entity => entity.Property(m => m.NormalizedName).HasMaxLength(85));

      builder.Entity<IdentityUserLogin<string>>(entity => entity.Property(m => m.LoginProvider).HasMaxLength(85));
      builder.Entity<IdentityUserLogin<string>>(entity => entity.Property(m => m.ProviderKey).HasMaxLength(85));
      builder.Entity<IdentityUserLogin<string>>(entity => entity.Property(m => m.UserId).HasMaxLength(85));
      builder.Entity<IdentityUserRole<string>>(entity => entity.Property(m => m.UserId).HasMaxLength(85));

      builder.Entity<IdentityUserRole<string>>(entity => entity.Property(m => m.RoleId).HasMaxLength(85));

      builder.Entity<IdentityUserToken<string>>(entity => entity.Property(m => m.UserId).HasMaxLength(85));
      builder.Entity<IdentityUserToken<string>>(entity => entity.Property(m => m.LoginProvider).HasMaxLength(85));
      builder.Entity<IdentityUserToken<string>>(entity => entity.Property(m => m.Name).HasMaxLength(85));

      builder.Entity<IdentityUserClaim<string>>(entity => entity.Property(m => m.Id).HasMaxLength(85));
      builder.Entity<IdentityUserClaim<string>>(entity => entity.Property(m => m.UserId).HasMaxLength(85));
      builder.Entity<IdentityRoleClaim<string>>(entity => entity.Property(m => m.Id).HasMaxLength(85));
      builder.Entity<IdentityRoleClaim<string>>(entity => entity.Property(m => m.RoleId).HasMaxLength(85));
}

Now you can finally run dotnet ef database update and it will create all the necessary tables with no problems (at least, it did for me). Here’re the tables you should get:

mysql> show tables;
+------------------------+
| Tables_in_project      |
+------------------------+
| AspNetRoleClaims       |
| AspNetRoles            |
| AspNetUserClaims       |
| AspNetUserLogins       |
| AspNetUserRoles        |
| AspNetUserTokens       |
| AspNetUsers            |
| __EFMigrationsHistory  |
+------------------------+
8 rows in set (0.00 sec)

If you want to rollback migrations to perform a new one, try to run dotnet ef migrations remove or simply remove the files from /Migrations/. But that won’t touch the half-created tables in the database, so you’ll need to delete those manually.

Migration part is done. However, you might get the following errors after trying to run your application:

One or more compilation references are missing. Ensure that your project is referencing 'Microsoft.NET.Sdk.Web' and the 'PreserveCompilationContext' property is not set to false.
The type or namespace name 'LoginViewModel' could not be found (are you missing a using directive or an assembly reference?)
The type or namespace name 'SignInManager<>' could not be found (are you missing a using directive or an assembly reference?)
The type or namespace name 'IdentityUser' could not be found (are you missing a using directive or an assembly reference?)

Most probably, you were adding Identity views and models to an existing project and forgot to copy the contents of /Views/_ViewImports.cshtml:

@using YOUR-PROJECT-NAMESPACE
@using YOUR-PROJECT-NAMESPACE.Models
@using YOUR-PROJECT-NAMESPACE.Models.AccountViewModels
@using YOUR-PROJECT-NAMESPACE.Models.ManageViewModels
@using Microsoft.AspNetCore.Identity
@addTagHelper *, Microsoft.AspNetCore.Mvc.TagHelpers

Now everything should be fine.

So, how to use it?

You already are using it. All the actions of your controllers now require user to be logged-in.

Probably, you would like to keep the actions of HomeController publicly available, because most likely that’s the one that handles the main page and also error pages in your project. If that’s the case, add [AllowAnonymous] attribute to the whole controller:

namespace YOUR-PROJECT-NAMESPACE.Controllers
{
    [AllowAnonymous]
    public class HomeController : Controller
    {
        // ...
    }
}

Same goes for the Login actions of AccountController, otherwise no-one will be able to login to your website. In order to do so, add [AllowAnonymous] attribute to the actions, so they would be available for anonymous users:

namespace YOUR-PROJECT-NAMESPACE.Controllers
{
    public class AccountController : Controller
    {
        // ...

        [HttpGet]
        [AllowAnonymous]
        public async Task<IActionResult> Login(string returnUrl = null)
        {
            // ...
        }

        [HttpPost]
        [AllowAnonymous]
        public async Task<IActionResult> Login(LoginViewModel model, string returnUrl = null)
        {
            // ...
        }

        // ...
    }
}

And when user tries to open a page that requires him to authenticate himself (actions without [AllowAnonymous] attribute), your application will show him login page:

.NET Core MVC login page

As I already mentioned, Identity comes with roles support out-of-the-box, so you can control access even for already logged-in users based on their roles. For example, the following action is only available for users with the role admin:

[Authorize(Roles = "admin")]
public ActionResult DeleteUser(string email)
{
    // ...
}

And thanks to Identity, you are provided with everything you need to manage roles. For instance, if you open /Account/Index (or just /Account) page in your browser, you’ll see something like this:

.NET Core MVC account page

You can register new user, delete existing ones and control the roles they have. And there are views for creating/deleting roles as well.

So, using .NET Core Identity with MySQL database is definitely possible, even though the process overall still has some problems.

[19.10.2018] Update: Migration to .NET Core 2.1

.NET Core 2.1

So, .NET Core 2.1 was released:

$ dotnet --info
.NET Core SDK (reflecting any global.json):
 Version:   2.1.403
 Commit:    04e15494b6

Runtime Environment:
 OS Name:     Mac OS X
 OS Version:  10.14
 OS Platform: Darwin
 RID:         osx-x64
 Base Path:   /usr/local/share/dotnet/sdk/2.1.403/

Host (useful for support):
  Version: 2.1.5
  Commit:  290303f510

.NET Core SDKs installed:
  2.1.403 [/usr/local/share/dotnet/sdk]

.NET Core runtimes installed:
  Microsoft.AspNetCore.All 2.1.5 [/usr/local/share/dotnet/shared/Microsoft.AspNetCore.All]
  Microsoft.AspNetCore.App 2.1.5 [/usr/local/share/dotnet/shared/Microsoft.AspNetCore.App]
  Microsoft.NETCore.App 2.1.5 [/usr/local/share/dotnet/shared/Microsoft.NETCore.App]

…and of course it broke some things. While some can be fixed by following the migration manual, MySQL EntityFramework from Oracle got broken for good.

For instance, I got the following error (also noted by someone in comments) after the update to .NET Core 2.1:

The 'MySQLNumberTypeMapping' does not support value conversions. Support for value conversions typically requires changes in the database provider.

Googling the error I found this thread, from where I got to this bugreport at Oracle tracker, which basically says that Oracle doesn’t give a flying rat about fixing it.

But good news Pomelo Foundation does, and they actually fixed the problem in their package, so the solution is just to give up on Oracle and switch to Pomelo.

Let’s see that on an example of a new project created from scratch:

mkdir some-project && cd "$_"
dotnet new mvc --auth Individual
dotnet add package Pomelo.EntityFrameworkCore.MySql
dotnet restore

Modify the connection string in appsettings.json:

{
  "ConnectionStrings": {
    "DefaultConnection": "server=localhost;port=3306;database=DATABASE;user=USERNAME;password=PASSWORD;CharSet=utf8;SslMode=none;"
  },

  // ...
}

And perform migration:

dotnet ef migrations add InitialCreate
dotnet ef database update

That will create something like this in your database:

mysql> show tables;
+-----------------------+
| Tables_in_DATABASE    |
+-----------------------+
| AspNetRoleClaims      |
| AspNetRoles           |
| AspNetUserClaims      |
| AspNetUserLogins      |
| AspNetUserRoles       |
| AspNetUserTokens      |
| AspNetUsers           |
| __EFMigrationsHistory |
+-----------------------+
8 rows in set (0.00 sec)

Then change from UseSqlite() to UseMySql() in Startup.cs:

public void ConfigureServices(IServiceCollection services)
{
    // ...

    services.AddDbContext<ApplicationDbContext>(options =>
        options.UseMySql(Configuration.GetConnectionString("DefaultConnection"))
        );
    services.AddDefaultIdentity<IdentityUser>()
        .AddEntityFrameworkStores<ApplicationDbContext>();

    // ...
}

And that’s it, there is no even need to override OnModelCreating() with those weird data types customization - everything just works out of the box.

[17.10.2020] Update: .NET 5 and PostgreSQL

I wrote a new post about .NET Core MVC project with Identity and PostgreSQL.