Use a Identity 2.0 Database to Authenticate a ASP.NET Core 1.0 application

Question

I am trying to a create a new ASP.NET Core 1.0 web application and I want it to use the Authentication tables that I already have set up. These tables were originally created by a ASP.NET 4.6 web application that used Microsoft.ASPNet.Identity.EntityFramework 2.2.0

It looks like things have change in Microsoft.AspNetCore.Identity.EntityFrameworkCore because the new Core 1.0 application throws this error when trying to log in:

A database operation failed while processing the request.

SqlException: Invalid column name 'NormalizedUserName'. Invalid column name 'ConcurrencyStamp'. Invalid column name 'LockoutEnd'. Invalid column name 'NormalizedEmail'. Invalid column name 'NormalizedUserName'.

The project.json is straight out of the box and looks like this:

"dependencies": {
"Microsoft.NETCore.App": {
  "version": "1.0.0",
  "type": "platform"
},
"Microsoft.AspNetCore.Authentication.Cookies": "1.0.0",
"Microsoft.AspNetCore.Diagnostics": "1.0.0",
"Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore": "1.0.0",
"Microsoft.AspNetCore.Identity.EntityFrameworkCore": "1.0.0",
"Microsoft.AspNetCore.Mvc": "1.0.0",
"Microsoft.AspNetCore.Razor.Tools": {
  "version": "1.0.0-preview2-final",
  "type": "build"
},
"Microsoft.AspNetCore.Server.IISIntegration": "1.0.0",
"Microsoft.AspNetCore.Server.Kestrel": "1.0.0",
"Microsoft.AspNetCore.StaticFiles": "1.0.0",
"Microsoft.EntityFrameworkCore.SqlServer": "1.0.0",
"Microsoft.EntityFrameworkCore.SqlServer.Design": {
  "version": "1.0.0",
  "type": "build"
},
"Microsoft.EntityFrameworkCore.Tools": {
  "version": "1.0.0-preview2-final",
  "type": "build"
},
"Microsoft.Extensions.Configuration.EnvironmentVariables": "1.0.0",
"Microsoft.Extensions.Configuration.Json": "1.0.0",
"Microsoft.Extensions.Configuration.UserSecrets": "1.0.0",
"Microsoft.Extensions.Logging": "1.0.0",
"Microsoft.Extensions.Logging.Console": "1.0.0",
"Microsoft.Extensions.Logging.Debug": "1.0.0",
"Microsoft.Extensions.Options.ConfigurationExtensions": "1.0.0",
"Microsoft.VisualStudio.Web.BrowserLink.Loader": "14.0.0",
"Microsoft.VisualStudio.Web.CodeGeneration.Tools": {
  "version": "1.0.0-preview2-final",
  "type": "build"
},
"Microsoft.VisualStudio.Web.CodeGenerators.Mvc": {
  "version": "1.0.0-preview2-final",
  "type": "build"
}

},

Also, I have not changed my ApplicationDbContext, but I have seen some posts about making changes to resolve this issue in that class

You can start with a fresh Identity database if you'd like. Check out this answer for exact steps: https://stackoverflow.com/a/74856286/8644294 — Ash K, Dec 19 '22 at 21:47

score 25 · Answer 1 · answered Apr 12 '17 at 18:43

This SQL migration script got me over the above hurdle:

Alter Table ASPNETROLES
ADD
 ConcurrencyStamp varchar(255) null,               
 NormalizedName varchar(255) null

 Drop Table AspNetUserTokens

 CREATE TABLE [AspNetUserTokens] (
    [UserId]        NVARCHAR (450) NOT NULL,
    [LoginProvider] NVARCHAR (450) NOT NULL,
    [Name]          NVARCHAR (450) NOT NULL,
    [Value]         NVARCHAR (MAX) NULL,
    CONSTRAINT [PK_AspNetUserTokens]
 PRIMARY KEY CLUSTERED ([UserId] ASC, [LoginProvider] ASC, [Name] ASC)
)

Alter Table AspNetUsers
 Add
 ConcurrencyStamp varchar(255) null,
 LockoutEnd DateTime null,
 NormalizedEmail varchar(255) null,
 NormalizedUserName varchar(255) null

Drop Table [AspNetRoleClaims]

CREATE TABLE [AspNetRoleClaims] (
    [Id]         INT            IDENTITY (1, 1) NOT NULL,
    [ClaimType]  NVARCHAR (MAX) NULL,
    [ClaimValue] NVARCHAR (MAX) NULL,
    [RoleId]     NVARCHAR (128) NOT NULL,
    CONSTRAINT [PK_AspNetRoleClaims]
 PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_AspNetRoleClaims_AspNetRoles_RoleId]
 FOREIGN KEY ([RoleId])
  REFERENCES [dbo].[AspNetRoles] ([Id]) ON DELETE CASCADE
)


GO
CREATE NONCLUSTERED INDEX [IX_AspNetRoleClaims_RoleId]
    ON [AspNetRoleClaims]([RoleId] ASC)

Alter Table AspNetUserLogins
   Add  ProviderDisplayName varchar(255) null

There's not been a ton of guidance from Microsoft over how to migrate the database but this fixed the above issue for me.

I am not relying on migrations and using custom Mappers for ef entity to table mapping. I have inserted `NormalizedName` and `ConcurrencyStamp` in `Users` table it didn't resolved the issue. Than I did the same with `UserRoles` table as you suggested but that didn't worked either — JB's, Sep 02 '18 at 12:04
Did you continue using this database with your 'old' app? That's what I need to be able to do. I see your script didn't update normalized values either. Wouldn't you need to do that or is Core smart enough to update them — Simon_Weaver, Dec 02 '18 at 02:51

score 6 · Answer 2 · answered Dec 02 '18 at 07:43

There's a package available to do this. It literally is for this exact purpose. It is part of Microsoft's codebase, and appears to have been updated recently for Core 2.1.

A compatibility layer for sharing identity databases between Microsoft.AspNet.Identity.EntityFramework and Microsoft.AspNetCore.Identity.EntityFrameworkCore.

https://www.nuget.org/packages/Microsoft.AspNet.Identity.AspNetCoreCompat/

https://github.com/aspnet/Identity/tree/master/src/AspNetCoreCompat

It handles the 'differences' between the two schemas with methods like this:

    /// <summary>
    ///     Normalized email
    /// </summary>
    public string NormalizedEmail {
        get
        {
            return Email.ToUpperInvariant();
        }
        set { }
    }

    /// <summary>
    ///     Concurrency stamp
    /// </summary>
    public virtual string ConcurrencyStamp { get; set; } = Guid.NewGuid().ToString();

I cannot find any real documentation, but I've done the following and it seems to be working ok:

You install the compatibility package in your OLD website (.NET 4.6)

You must change ALL references to IdentityRole, IdentityUser, IdentityDbContext etc. to be the classes from the compatibility package.

using Compat = Microsoft.AspNet.Identity.CoreCompat;

// update to use the compatibility class
public class ApplicationDbContext : Compat.IdentityDbContext<ApplicationUser>

// change all instances, such as this
Compat.IdentityUser user = await _repo.FindUser(context.UserName, context.Password);

You have to upgrade your database to the new format (basically adding a few columns and changing some data types). This is the trickiest stage! You're going to want to do this on a staging environment for sure. I'm using Azure so I just cloned the DB.
On GitHub I found some migrations scripts by @samnpathdr called Migration.zip. There are several scripts to be run one by one. I'd recommend running each command one at a time to make sure it all runs.
Currently there's one table in his script that is custom to his implementation (AspNetUserRolePermissions) so delete references to that.
If you have any other tables referencing the AspNetUsers table, you must drop constraints, update the datatypes and add the constraints back. For example I have a Notes table which is linked to a AspNetUser so I needed to run ALTER TABLE UserProfileNote ALTER COLUMN AspNetUsersId nvarchar(450) NOT NULL; (after removing constraints). Script the constraints first!
Be careful if you have any 'auto migrations' enabled for Core because personally I wouldn't trust them after this kind of change. You should reset to a baseline, or not do EF migrations at all.

https://github.com/aspnet/Docs/issues/6425

I am having a problem with LockoutEnd field. Identity 2 wants it to be DateTime. Core wants it to be DateTimeOffset. I am unable to get them to co-exist. This error only happens when there the field is not null. Have you found a way around this? — JayTee, Jul 07 '22 at 20:58
Honestly to this day I still have one website that has Lockout related errors all the time in the logs and I'm too scared to try and touch it :-/ Sorry — Simon_Weaver, Jul 08 '22 at 21:58

score 4 · Answer 3 · answered Nov 25 '20 at 11:06

I manually wrote a migration from the old to the new Identity and the application works with both old and new users. Here's the migration if you want to save yourselves some manual work:

public partial class Identity : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AddColumn<string>(
            name: "NormalizedName",
            table: "AspNetRoles",
            type: "nvarchar(256)",
            maxLength: 256,
            nullable: true);

        migrationBuilder.AddColumn<string>(
            name: "ConcurrencyStamp",
            table: "AspNetRoles",
            type: "nvarchar(max)",
            nullable: true);

        migrationBuilder.RenameColumn(
            name: "LockoutEndDateUtc",
            table: "AspNetUsers",
            newName: "LockoutEnd");

        migrationBuilder.AddColumn<string>(
            name: "ConcurrencyStamp",
            table: "AspNetUsers",
            type: "nvarchar(max)",
            nullable: true);

        migrationBuilder.AddColumn<string>(
            name: "NormalizedEmail",
            table: "AspNetUsers",
            type: "nvarchar(256)",
            maxLength: 256,
            nullable: true);

        migrationBuilder.AddColumn<string>(
            name: "NormalizedUsername",
            table: "AspNetUsers",
            type: "nvarchar(256)",
            maxLength: 256,
            nullable: true);

        migrationBuilder.AddColumn<string>(
            name: "ProviderDisplayName",
            table: "AspNetUserLogins",
            type: "nvarchar(max)",
            nullable: true);

        migrationBuilder.CreateTable(
            name: "AspNetRoleClaims",
            columns: table => new
            {
                Id = table.Column<int>(type: "int", nullable: false)
                    .Annotation("SqlServer:Identity", "1, 1"),
                RoleId = table.Column<string>(type: "nvarchar(128)", nullable: false),
                ClaimType = table.Column<string>(type: "nvarchar(max)", nullable: true),
                ClaimValue = table.Column<string>(type: "nvarchar(max)", nullable: true)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_AspNetRoleClaims", x => x.Id);
                table.ForeignKey(
                    name: "FK_AspNetRoleClaims_AspNetRoles_RoleId",
                    column: x => x.RoleId,
                    principalTable: "AspNetRoles",
                    principalColumn: "Id",
                    onDelete: ReferentialAction.Cascade);
            });

        migrationBuilder.CreateTable(
            name: "AspNetUserTokens",
            columns: table => new
            {
                UserId = table.Column<string>(type: "nvarchar(128)", nullable: false),
                LoginProvider = table.Column<string>(type: "nvarchar(450)", nullable: false),
                Name = table.Column<string>(type: "nvarchar(450)", nullable: false),
                Value = table.Column<string>(type: "nvarchar(max)", nullable: true)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_AspNetUserTokens", x => new { x.UserId, x.LoginProvider, x.Name });
                table.ForeignKey(
                    name: "FK_AspNetUserTokens_AspNetUsers_UserId",
                    column: x => x.UserId,
                    principalTable: "AspNetUsers",
                    principalColumn: "Id",
                    onDelete: ReferentialAction.Cascade);
            });
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropColumn(
            name: "ConcurrencyStamp",
            table: "AspNetRoles");

        migrationBuilder.DropColumn(
            name: "NormalizedName",
            table: "AspNetRoles");

        migrationBuilder.RenameColumn(
            name: "LockoutEnd",
            table: "AspNetUsers",
            newName: "LockoutEndDateUtc");

        migrationBuilder.DropColumn(
            name: "ConcurrencyStamp",
            table: "AspNetUsers");

        migrationBuilder.DropColumn(
            name: "NormalizedEmail",
            table: "AspNetUsers");

        migrationBuilder.DropColumn(
            name: "NormalizedUsername",
            table: "AspNetUsers");

        migrationBuilder.DropColumn(
            name: "ProviderDisplayName",
            table: "AspNetUserLogins");

        migrationBuilder.DropTable("AspNetRoleClaims");

        migrationBuilder.DropTable("AspNetUserTokens");
    }
}

score 2 · Answer 4 · answered Mar 27 '20 at 21:46

Also keep in mind that is one way migration. To be able still use old solution you could force .net core to use old password hashing algo

services.Configure<PasswordHasherOptions>(options => options.CompatibilityMode = PasswordHasherCompatibilityMode.IdentityV2);

Use a Identity 2.0 Database to Authenticate a ASP.NET Core 1.0 application

4 Answers4

Linked