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

Answer 1

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.

Answer 2

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