How to migrate Identity users from a MVC5 app to a ASP.NET Core 2.2 app

Question

I have a web app built on MVC5 using Identity. I want to convert this project to a ASP.NET Core 2.2 web app. I created a new ASP.NET Core 2.2 web application with authentication set to Individual User Accounts and added my database to the project by following these instructions. I then added a new Identity Scaffolded item to my project and added a migration and updated the database.

I registered a test user and when I checked SQL management server I see it created a new database for this project even though my connection string is for my old database.

I would like to keep my old database but convert it to use the new Identity Razor pages that are built in with ASP.NET Core. What is the best way to go about doing this?

Answer 1

I went with just starting fresh.

Step 1:

Create migration to create new Identity tables. (My new Identity nuget package version is 6.0.10)

1.1 Create DbContext for migration:

Note: I deleted the old migration file and model snapshot that created the old Identity tables at this point.

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
        : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder builder)
    {
        base.OnModelCreating(builder);
        // Customize the ASP.NET Identity model and override the defaults if needed.
        // For example, you can rename the ASP.NET Identity table names and more.
        // Add your customizations after calling base.OnModelCreating(builder);

        // Create these Identity tables in a new schema
        builder.Entity<ApplicationUser>().ToTable("AspNetUsers", "mynewschema");
        builder.Entity<IdentityRole>().ToTable("AspNetRoles", "mynewschema");
        builder.Entity<IdentityUserClaim<string>>().ToTable("AspNetUserClaims", "mynewschema");
        builder.Entity<IdentityUserToken<string>>().ToTable("AspNetUserTokens", "mynewschema");
        builder.Entity<IdentityUserLogin<string>>().ToTable("AspNetUserLogins", "mynewschema");
        builder.Entity<IdentityRoleClaim<string>>().ToTable("AspNetRoleClaims", "mynewschema");
        builder.Entity<IdentityUserRole<string>>().ToTable("AspNetUserRoles", "mynewschema");
    }
}

ApplicationUser looks like this:

public class ApplicationUser : IdentityUser
{
}

1.2 Create migration:

PM> Add-Migration 'Create_new_identity_tables_on_new_schema'

Step 2:

Use migration from Step 1 to create new identity tables:

PM> Update-Database

Step 3:

Copy data from old tables to new tables using the following scripts:

3.1 Copy Users:

INSERT INTO [mynewschema].[AspNetUsers] (
    [Id]
    ,[UserName]
    ,[NormalizedUserName]
    ,[Email]
    ,[NormalizedEmail]
    ,[EmailConfirmed]
    ,[PasswordHash]
    ,[SecurityStamp]
    ,[ConcurrencyStamp]
    ,[PhoneNumber]
    ,[PhoneNumberConfirmed]
    ,[TwoFactorEnabled]
    ,[LockoutEnd]
    ,[LockoutEnabled]
    ,[AccessFailedCount]
    )
SELECT [Id]
    ,[UserName]
    ,UPPER([UserName])
    ,[Email]
    ,UPPER([Email])
    ,[EmailConfirmed]
    ,[PasswordHash]
    ,COALESCE([SecurityStamp], '')
    ,''
    ,[PhoneNumber]
    ,[PhoneNumberConfirmed]
    ,[TwoFactorEnabled]
    ,NULL
    ,[LockoutEnabled]
    ,[AccessFailedCount]
FROM [myoldschema].[AspNetUsers];

3.2 Copy Roles:

INSERT INTO [mynewschema].[AspNetRoles] (
    [Id]
    ,[Name]
    ,[NormalizedName]
    ,[ConcurrencyStamp]
    )
SELECT [Id]
    ,[Name]
    ,UPPER([Name])
    ,NULL
FROM [myoldschema].[AspNetRoles];

3.3 Copy Users to Roles mapping:

INSERT INTO [mynewschema].[AspNetUserRoles] (
    [UserId]
    ,[RoleId]
    )
SELECT [UserId]
    ,[RoleId]
FROM [myoldschema].[AspNetUserRoles];

3.4 You might want to copy other tables as well. I didn't do that because I wasn't using other tables.

It all worked pretty well for me at this point. 🙌

Step 4:

You might want to add PasswordHasherCompatibilityMode in Program.cs. (I didn't do this because in my system we authenticate users with Active Directory password. Something like this).

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

Answer 2

After upgrading the Identity tables, you may want to update existing users password hashes. Some new columns in the AspNetUsers table will have NULL values. First run this:

UPDATE AspNetUsers SET NormalizedEmail = UPPER(Email), NormalizedUserName = UPPER(UserName)
WHERE NormalizedEmail IS NULL

We need a way to distinguish which users are using the new hash version or not.

One way is to add a new property to IdentityUser:

public class ApplicationUser : IdentityUser
{
    public PasswordHashVersion HashVersion { get; set; }

    public ApplicationUser()
    {
        this.HashVersion = PasswordHashVersion.Core;
    }
}

public enum PasswordHashVersion
{
    OldMvc,
    Core
}

Existing users will have default PasswordHashVersion equals zero (OldMvc), new registered users will default to 1 (Core). If you have a smarter way to detect if a hash is from new or old algorithms, you don't need this.

Then we create a custom PasswordHash, which uses the old default hash algorithm implementation:

public class OldMvcPasswordHasher : PasswordHasher<ApplicationUser>
{
    public override PasswordVerificationResult VerifyHashedPassword(ApplicationUser user, string hashedPassword, string providedPassword)
    {
        // if it's the new algorithm version, delegate the call to parent class
        if (user.HashVersion == PasswordHashVersion.Core)
            return base.VerifyHashedPassword(user, hashedPassword, providedPassword);

        byte[] buffer4;
        if (hashedPassword == null)
        {
            return PasswordVerificationResult.Failed;
        }
        if (providedPassword == null)
        {
            throw new ArgumentNullException("providedPassword");
        }
        byte[] src = Convert.FromBase64String(hashedPassword);
        if ((src.Length != 0x31) || (src[0] != 0))
        {
            return PasswordVerificationResult.Failed;
        }
        byte[] dst = new byte[0x10];
        Buffer.BlockCopy(src, 1, dst, 0, 0x10);
        byte[] buffer3 = new byte[0x20];
        Buffer.BlockCopy(src, 0x11, buffer3, 0, 0x20);
        using (Rfc2898DeriveBytes bytes = new Rfc2898DeriveBytes(providedPassword, dst, 0x3e8))
        {
            buffer4 = bytes.GetBytes(0x20);
        }
        if (AreHashesEqual(buffer3, buffer4))
        {
            user.HashVersion = PasswordHashVersion.Core;
            return PasswordVerificationResult.SuccessRehashNeeded;
        }
        return PasswordVerificationResult.Failed;
    }

    private bool AreHashesEqual(byte[] firstHash, byte[] secondHash)
    {
        int _minHashLength = firstHash.Length <= secondHash.Length ? firstHash.Length : secondHash.Length;
        var xor = firstHash.Length ^ secondHash.Length;
        for (int i = 0; i < _minHashLength; i++)
            xor |= firstHash[i] ^ secondHash[i];
        return 0 == xor;
    }
}

This class inherits the new Identity Core PasswordHasher. If the user's password hash version is already using the new algorithm (e.g HashVersion = Core), then we just call the base method from PasswordHasher which uses the new algorithm. Otherwise, use the old identity algorithm to verify the password.

If the password matches, we update the user password hash version to Core, and return PasswordVerificationResult.SuccessRehashNeeded to force updating the existing hash with the new algorithm.

Lastly, you need to make sure your custom PasswordHasher is being used. Add this to Startup.cs inside ConfigureServices:

// Replace the existing scoped IPasswordHasher<> implementation
services.Replace(new ServiceDescriptor(
    serviceType: typeof(IPasswordHasher<ApplicationUser>),
    implementationType: typeof(OldMvcPasswordHasher),
    ServiceLifetime.Scoped));

This must be added after any calls to AddIdentity, AddDefaultIdentity or AddIdentityCore.

This will gradually upgrade password hashes as your users authenticate.

Answer 3

I was just able to successfully migrate a .NET 4.5.2 project to .NET Core 3.1 with the followinig steps

1. Use Scaffold-DbContext to create a model based on your existing database [1]

Scaffold-DbContext [-Connection] [-Provider] [-OutputDir] [-Context] [-Schemas>] [-Tables>] [-DataAnnotations] [-Force] [-Project] [-StartupProject] [<CommonParameters>]

2. Remove all the AspNet related tables from the generated context and also its .cs files that are no longer needed.

3. Add base.OnModelCreating(modelBuilder); to the generated OnModelCreating method on the context file. [2]

4. Run the script below to update/create the identity tables [3] [4]

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

UPDATE AspNetUsers SET NormalizedEmail = UPPER(Email), NormalizedUserName = UPPER(UserName)
WHERE NormalizedEmail IS NULL

5. On the Startup.cs file set the password hasher compatibility mode to consider IdentityV2

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

Answer 4

I've written the SQL script for migration to AspNetCore Identity.

This script is fully backward compatible with AspNet Identity, because I've added new columns to table and extend already exists columns.

Let's have a look on screenshots which were genereted via Scheme compare:

Bellow you can see the final script

BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET XACT_ABORT ON;

GO
PRINT N'Starting dropping constraints';

GO
ALTER TABLE [dbo].[AspNetUserRoles]
    DROP CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId];

GO
ALTER TABLE [dbo].[AspNetUserRoles]
    DROP CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetUsers_UserId];

GO
ALTER TABLE [dbo].[AspNetUserClaims]
    DROP CONSTRAINT [FK_dbo.AspNetUserClaims_dbo.AspNetUsers_UserId];

GO
ALTER TABLE [dbo].[AspNetUserLogins]
    DROP CONSTRAINT [FK_dbo.AspNetUserLogins_dbo.AspNetUsers_UserId];

-----------------------------------------------------------------------------
GO
PRINT N'Starting rebuilding table [dbo].[AspNetRoles]...';

GO
BEGIN TRANSACTION;
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    SET XACT_ABORT ON;

    CREATE TABLE [dbo].[tmp_ms_xx_AspNetRoles] (
        [Id]               NVARCHAR (450) NOT NULL,
        [Name]             NVARCHAR (256) NULL,
        [NormalizedName]   NVARCHAR (256) NULL,
        [ConcurrencyStamp] NVARCHAR (MAX) NULL,
        CONSTRAINT [tmp_ms_xx_constraint_PK_AspNetRoles1] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (FILLFACTOR = 80)
    );

    IF EXISTS (SELECT TOP 1 1 
               FROM   [dbo].[AspNetRoles])
        BEGIN
            INSERT INTO [dbo].[tmp_ms_xx_AspNetRoles] ([Id], [Name], [NormalizedName], [ConcurrencyStamp])
            SELECT   [Id],
                     [Name],
                     UPPER([Name]),
                     NEWID()
            FROM     [dbo].[AspNetRoles]
            ORDER BY [Id] ASC;
        END

    DROP TABLE [dbo].[AspNetRoles];

    EXECUTE sp_rename N'[dbo].[tmp_ms_xx_AspNetRoles]', N'AspNetRoles';
    EXECUTE sp_rename N'[dbo].[tmp_ms_xx_constraint_PK_AspNetRoles1]', N'PK_AspNetRoles', N'OBJECT';

COMMIT TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

GO
CREATE UNIQUE NONCLUSTERED INDEX [RoleNameIndex]
    ON [dbo].[AspNetRoles]([Name] ASC) WHERE ([Name] IS NOT NULL) WITH (FILLFACTOR = 80);

GO
CREATE UNIQUE NONCLUSTERED INDEX [RoleNormalizedNameIndex]
    ON [dbo].[AspNetRoles]([NormalizedName] ASC) WHERE ([NormalizedName] IS NOT NULL) WITH (FILLFACTOR = 80);

-------------------------------------------------------------------
GO
PRINT N'Starting rebuilding table [dbo].[AspNetUserClaims]...';

GO
BEGIN TRANSACTION;
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    SET XACT_ABORT ON;

    CREATE TABLE [dbo].[tmp_ms_xx_AspNetUserClaims] (
        [Id]         INT            IDENTITY (1, 1) NOT NULL,
        [UserId]     NVARCHAR (450) NOT NULL,
        [ClaimType]  NVARCHAR (MAX) NULL,
        [ClaimValue] NVARCHAR (MAX) NULL,
        CONSTRAINT [tmp_ms_xx_constraint_PK_AspNetUserClaims1] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (FILLFACTOR = 80)
    );

    IF EXISTS (SELECT TOP 1 1 
               FROM   [dbo].[AspNetUserClaims])
        BEGIN
            SET IDENTITY_INSERT [dbo].[tmp_ms_xx_AspNetUserClaims] ON;
            INSERT INTO [dbo].[tmp_ms_xx_AspNetUserClaims] ([Id], [UserId], [ClaimType], [ClaimValue])
            SELECT   [Id],
                     [UserId],
                     [ClaimType],
                     [ClaimValue]
            FROM     [dbo].[AspNetUserClaims]
            ORDER BY [Id] ASC;
            SET IDENTITY_INSERT [dbo].[tmp_ms_xx_AspNetUserClaims] OFF;
        END

    DROP TABLE [dbo].[AspNetUserClaims];

    EXECUTE sp_rename N'[dbo].[tmp_ms_xx_AspNetUserClaims]', N'AspNetUserClaims';
    EXECUTE sp_rename N'[dbo].[tmp_ms_xx_constraint_PK_AspNetUserClaims1]', N'PK_AspNetUserClaims', N'OBJECT';

COMMIT TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

GO
CREATE NONCLUSTERED INDEX [IX_AspNetUserClaims_UserId]
    ON [dbo].[AspNetUserClaims]([UserId] ASC) WITH (FILLFACTOR = 80);

-------------------------------------------------------------------
GO
PRINT N'Starting rebuilding table [dbo].[AspNetUserLogins]...';

GO
BEGIN TRANSACTION;
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    SET XACT_ABORT ON;

    CREATE TABLE [dbo].[tmp_ms_xx_AspNetUserLogins] (
        [LoginProvider]       NVARCHAR (450) NOT NULL,
        [ProviderKey]         NVARCHAR (450) NOT NULL,
        [ProviderDisplayName] NVARCHAR (MAX) NULL,
        [UserId]              NVARCHAR (450) NOT NULL,
        CONSTRAINT [tmp_ms_xx_constraint_PK_AspNetUserLogins1] PRIMARY KEY CLUSTERED ([LoginProvider] ASC, [ProviderKey] ASC, [UserId] ASC) WITH (FILLFACTOR = 80)
    );

    IF EXISTS (SELECT TOP 1 1 
               FROM   [dbo].[AspNetUserLogins])
        BEGIN
            INSERT INTO [dbo].[tmp_ms_xx_AspNetUserLogins] ([LoginProvider], [ProviderKey], [UserId])
            SELECT   [LoginProvider],
                     [ProviderKey],
                     [UserId]
            FROM     [dbo].[AspNetUserLogins]
            ORDER BY [LoginProvider] ASC, [ProviderKey] ASC;
        END

    DROP TABLE [dbo].[AspNetUserLogins];

    EXECUTE sp_rename N'[dbo].[tmp_ms_xx_AspNetUserLogins]', N'AspNetUserLogins';
    EXECUTE sp_rename N'[dbo].[tmp_ms_xx_constraint_PK_AspNetUserLogins1]', N'PK_AspNetUserLogins', N'OBJECT';
COMMIT TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;


GO
CREATE NONCLUSTERED INDEX [IX_AspNetUserLogins_UserId]
    ON [dbo].[AspNetUserLogins]([UserId] ASC) WITH (FILLFACTOR = 80);

-------------------------------------------------------------------
GO
PRINT N'Starting rebuilding table [dbo].[AspNetUserRoles]...';

GO
BEGIN TRANSACTION;
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    SET XACT_ABORT ON;

    CREATE TABLE [dbo].[tmp_ms_xx_AspNetUserRoles] (
        [UserId] NVARCHAR (450) NOT NULL,
        [RoleId] NVARCHAR (450) NOT NULL,
        CONSTRAINT [tmp_ms_xx_constraint_PK_AspNetUserRoles1] PRIMARY KEY CLUSTERED ([UserId] ASC, [RoleId] ASC) WITH (FILLFACTOR = 80)
    );

    IF EXISTS (SELECT TOP 1 1 
               FROM   [dbo].[AspNetUserRoles])
        BEGIN
            INSERT INTO [dbo].[tmp_ms_xx_AspNetUserRoles] ([UserId], [RoleId])
            SELECT   [UserId],
                     [RoleId]
            FROM     [dbo].[AspNetUserRoles]
            ORDER BY [UserId] ASC, [RoleId] ASC;
        END

    DROP TABLE [dbo].[AspNetUserRoles];

    EXECUTE sp_rename N'[dbo].[tmp_ms_xx_AspNetUserRoles]', N'AspNetUserRoles';
    EXECUTE sp_rename N'[dbo].[tmp_ms_xx_constraint_PK_AspNetUserRoles1]', N'PK_AspNetUserRoles', N'OBJECT';

COMMIT TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

GO
CREATE NONCLUSTERED INDEX [IX_AspNetUserRoles_RoleId]
    ON [dbo].[AspNetUserRoles]([RoleId] ASC) WITH (FILLFACTOR = 80);
-------------------------------------------------------------------
GO
PRINT N'Starting rebuilding table [dbo].[AspNetUsers]...';

GO
BEGIN TRANSACTION;
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    SET XACT_ABORT ON;

    CREATE TABLE [dbo].[tmp_ms_xx_AspNetUsers] (
        [Id]                   NVARCHAR (450)     NOT NULL,
        [Email]                NVARCHAR (256)     NULL,
        [NormalizedEmail]      NVARCHAR (256)     NULL,
        [EmailConfirmed]       BIT                NOT NULL,
        [PasswordHash]         NVARCHAR (MAX)     NULL,
        [SecurityStamp]        NVARCHAR (MAX)     NULL,
        [PhoneNumber]          NVARCHAR (MAX)     NULL,
        [PhoneNumberConfirmed] BIT                NOT NULL,
        [TwoFactorEnabled]     BIT                NOT NULL,
        [LockoutEndDateUtc]    DATETIME           NULL,
        [LockoutEnd]           DATETIMEOFFSET (7) NULL,
        [LockoutEnabled]       BIT                NOT NULL,
        [AccessFailedCount]    INT                NOT NULL,
        [UserName]             NVARCHAR (256)     NULL,
        [NormalizedUserName]   NVARCHAR (256)     NULL,
        [ConcurrencyStamp]     NVARCHAR (MAX)     NULL,
        CONSTRAINT [tmp_ms_xx_constraint_PK_AspNetUsers1] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (FILLFACTOR = 80)
    );

    IF EXISTS (SELECT TOP 1 1 
               FROM   [dbo].[AspNetUsers])
        BEGIN
            INSERT INTO [dbo].[tmp_ms_xx_AspNetUsers] (
                     [Id], 
                     [Email],
                     [NormalizedEmail],
                     [EmailConfirmed],
                     [PasswordHash],
                     [SecurityStamp],
                     [PhoneNumber],
                     [PhoneNumberConfirmed],
                     [TwoFactorEnabled],
                     [LockoutEnabled],
                     [AccessFailedCount],
                     [UserName],
                     [NormalizedUserName],
                     [ConcurrencyStamp])
            SELECT   [Id],
                     [Email],
                     UPPER([Email]),
                     [EmailConfirmed],
                     [PasswordHash],
                     [SecurityStamp],
                     [PhoneNumber],
                     [PhoneNumberConfirmed],
                     [TwoFactorEnabled],
                     [LockoutEnabled],
                     [AccessFailedCount],
                     [UserName],
                     UPPER([UserName]),
                     NEWID()
            FROM     [dbo].[AspNetUsers]
            ORDER BY [Id] ASC;
        END

    DROP TABLE [dbo].[AspNetUsers];

    EXECUTE sp_rename N'[dbo].[tmp_ms_xx_AspNetUsers]', N'AspNetUsers';
    EXECUTE sp_rename N'[dbo].[tmp_ms_xx_constraint_PK_AspNetUsers1]', N'PK_AspNetUsers', N'OBJECT';

COMMIT TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

GO
CREATE UNIQUE NONCLUSTERED INDEX [UserNameIndex]
    ON [dbo].[AspNetUsers]([UserName] ASC) WHERE ([UserName] IS NOT NULL) WITH (FILLFACTOR = 80);

GO
CREATE UNIQUE NONCLUSTERED INDEX [NormalizedUserNameIndex]
    ON [dbo].[AspNetUsers]([NormalizedUserName] ASC) WHERE ([NormalizedUserName] IS NOT NULL) WITH (FILLFACTOR = 80);

GO
CREATE NONCLUSTERED INDEX [EmailIndex]
    ON [dbo].[AspNetUsers]([NormalizedEmail] ASC) WITH (FILLFACTOR = 80);

-------------------------------------------------------------------
GO
CREATE TABLE [dbo].[AspNetRoleClaims] (
    [Id]         INT            IDENTITY (1, 1) NOT NULL,
    [RoleId]     NVARCHAR (450) NOT NULL,
    [ClaimType]  NVARCHAR (MAX) NULL,
    [ClaimValue] NVARCHAR (MAX) NULL,
    CONSTRAINT [PK_AspNetRoleClaims] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (FILLFACTOR = 80)
);

GO
CREATE NONCLUSTERED INDEX [IX_AspNetRoleClaims_RoleId]
    ON [dbo].[AspNetRoleClaims]([RoleId] ASC) WITH (FILLFACTOR = 80);

-------------------------------------------------------------------
GO
PRINT N'Creating [dbo].[AspNetUserTokens]...';

GO
CREATE TABLE [dbo].[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) WITH (FILLFACTOR = 80)
);

-------------------------------------------------------------------
GO
PRINT N'Creating [dbo].[__EFMigrationsHistory]...';


GO
CREATE TABLE [dbo].[__EFMigrationsHistory] (
    [MigrationId]    NVARCHAR (150) NOT NULL,
    [ProductVersion] NVARCHAR (32)  NOT NULL,
    CONSTRAINT [PK___EFMigrationsHistory] PRIMARY KEY CLUSTERED ([MigrationId] ASC) WITH (FILLFACTOR = 80)
);

--GO
--INSERT INTO [dbo].[__EFMigrationsHistory]
--           ([MigrationId], [ProductVersion])
--VALUES
--            ('20200406184458_InitialCreate',    '2.2.6-servicing-10079')
-------------------------------------------------------------------
GO
PRINT N'Creating constraints';

GO
ALTER TABLE [dbo].[AspNetUserLogins] WITH NOCHECK
    ADD CONSTRAINT [FK_AspNetUserLogins_AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE;

GO
ALTER TABLE [dbo].[AspNetUserRoles] WITH NOCHECK
    ADD CONSTRAINT [FK_AspNetUserRoles_AspNetRoles_RoleId] FOREIGN KEY ([RoleId]) REFERENCES [dbo].[AspNetRoles] ([Id]) ON DELETE CASCADE;

GO
ALTER TABLE [dbo].[AspNetUserRoles] WITH NOCHECK
    ADD CONSTRAINT [FK_AspNetUserRoles_AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE;

GO
ALTER TABLE [dbo].[AspNetUserClaims] WITH NOCHECK
    ADD CONSTRAINT [FK_AspNetUserClaims_AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE;

GO
ALTER TABLE [dbo].[AspNetRoleClaims] WITH NOCHECK
    ADD CONSTRAINT [FK_AspNetRoleClaims_AspNetRoles_RoleId] FOREIGN KEY ([RoleId]) REFERENCES [dbo].[AspNetRoles] ([Id]) ON DELETE CASCADE;

GO
ALTER TABLE [dbo].[AspNetUserTokens] WITH NOCHECK
    ADD CONSTRAINT [FK_AspNetUserTokens_AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE;
-------------------------------------------------------------------

GO
PRINT N'Update complete.';

COMMIT TRANSACTION;

Answer 5

An alternate solution would be to export/import the data to the new tables. This is what I did for a few different reasons, but ultimately didn't want to alter my original source table in case I needed it back quick and painlessly.

The long story short is that you can do a straight import/export of your old data to the new Identity tables and the only thing that's NEEDED additionally is the normalized username and email values.

It would be great if Identity could handle that internally as it handles (updates) the new password hash, security stamp and concurrency stamp without issue when the user logs in for the first time.

The super quick and easy way I migrated my users with intact passwords:

1) Export your users table on the old version of Identity to a CSV file.

2) Open that csv file in Excel and add three columns to the CSV file for the two missing normalization columns (username and email) and one missing concurrency stamp column (concurrency column is not really needed).

3) In the first blank normalization column (typically c2), add the Excel formula =UPPER(b2) (where b2 would be the field where the first value is you're trying to normalize... e.g. Username=b2 or Email=d2 on a straight export where you put the normalized columns next to their non-normalized counterparts). Hit enter and you should see the upper case value in the field now.

4) Drag that formula down to the end of the spreadsheet normalizing all the usernames and emails to their uppercase values (i.e. all upper case = normalized).

5) Copy the uppercase values out of the spreadsheet and paste them into a empty notepad file... then copy them back into the spreadsheet into the same place (which overwrites the formulas with the normalized values. Excel might be smart enough to get rid of the formulas and keep the normalized values on a CSV save, but I didn't test it).

6) Save the spreadsheet back as CSV and import it to the new identity AspnetUsers table.

Concurrency stamp can be left blank and the latest Identity will take care of everything else as soon as the user logs in for the first time.

Identity will also not login usernames if you haven't enabled that in the new version and did in the old version (so you would either need to enable that feature or convert your usernames to their relevant email address before you can login successfully).

It's not pretty, but for a one time job, it took me less than 15 minutes to migrate my users from Identity 4.0.30319 to Core 2.2 with this method and no password changes are needed. It took me longer to type all this out than it did to migrate my users. Hope it helps.

Answer 6

While the Microsoft documentation was somewhat helpful. The most helpful information was in the comments.

I was able to migrate existing DBs using the following steps:

• Create a new ASP.net Core project and modify it's ModelSnapshot to
  match with EF6. After that, you can generate a script for the chnages of EF6 to EF Core.

• Write a script to update the AspNetUsers table. In ASP.net core
  identity when authenticating it is used NormalizedEmail,
  NormalizedUserName columns. So we need to update those two columns
  using our existing data.

Here is the GitHub link for the scripts to update the tables. Migration.zip