Migrate from System.Web.Providers to ASP.NET Identity 1.0 and then to 2.1.0

Migration from simple ASP.NET membership provider to ASP.NET Identity is not straightforward. In order to migrate smoothly, we can firstly upgrade to Identity 1.0 and then to the latest version 2.0.

Reference

Migrating an Existing Website from SQL Membership to ASP.NET Identity

Updating ASP.NET applications from ASP.NET Identity 1.0 to 2.0.0-alpha1

All the details will be covered in the above two posts. I will only provide the scripts and solutions to some problems you may encounter.

Step 1: Script to Update Database Schema to Identity 1.0

IF OBJECT_ID('AspNetUserRoles', 'U') IS NOT NULL
BEGIN
DROP TABLE AspNetUserRoles;
END

IF OBJECT_ID('AspNetUserClaims', 'U') IS NOT NULL
BEGIN
DROP TABLE AspNetUserClaims;
END

IF OBJECT_ID('AspNetUserLogins', 'U') IS NOT NULL
BEGIN
DROP TABLE AspNetUserLogins;
END

IF OBJECT_ID('AspNetRoles', 'U') IS NOT NULL
BEGIN
DROP TABLE AspNetRoles;
END

IF OBJECT_ID('AspNetUsers', 'U') IS NOT NULL
BEGIN
DROP TABLE AspNetUsers;
END

CREATE TABLE [dbo].[AspNetUsers] (
    [Id]            NVARCHAR (128) NOT NULL,
    [UserName]      NVARCHAR (MAX) NULL,
    [PasswordHash]  NVARCHAR (MAX) NULL,
    [SecurityStamp] NVARCHAR (MAX) NULL,
    [Discriminator] NVARCHAR (128) NOT NULL,
    [ApplicationId]                          UNIQUEIDENTIFIER NOT NULL,
    [LegacyPasswordHash]  NVARCHAR (MAX) NULL,
    [LoweredUserName]  NVARCHAR (256)   NOT NULL,
    [MobileAlias]      NVARCHAR (16)    DEFAULT (NULL) NULL,
    [IsAnonymous]      BIT              DEFAULT ((0)) NOT NULL,
    [LastActivityDate] DATETIME2         NOT NULL,
    [MobilePIN]                              NVARCHAR (16)    NULL,
    [Email]                                  NVARCHAR (256)   NULL,
    [LoweredEmail]                           NVARCHAR (256)   NULL,
    [PasswordQuestion]                       NVARCHAR (256)   NULL,
    [PasswordAnswer]                         NVARCHAR (128)   NULL,
    [IsApproved]                             BIT              NOT NULL,
    [IsLockedOut]                            BIT              NOT NULL,
    [CreateDate]                             DATETIME2             NOT NULL,
    [LastLoginDate]                          DATETIME2         NOT NULL,
    [LastPasswordChangedDate]                DATETIME2         NOT NULL,
    [LastLockoutDate]                        DATETIME2         NOT NULL,
    [FailedPasswordAttemptCount]             INT              NOT NULL,
    [FailedPasswordAttemptWindowStart]       DATETIME2         NOT NULL,
    [FailedPasswordAnswerAttemptCount]       INT              NOT NULL,
    [FailedPasswordAnswerAttemptWindowStart] DATETIME2         NOT NULL,
    [Comment]                                NTEXT            NULL,
    CONSTRAINT [PK_dbo.AspNetUsers] PRIMARY KEY CLUSTERED ([Id] ASC),
    FOREIGN KEY ([ApplicationId]) REFERENCES [dbo].[Applications] ([ApplicationId]),
);

CREATE TABLE [dbo].[AspNetRoles] (
    [Id]   NVARCHAR (128) NOT NULL,
    [Name] NVARCHAR (MAX) NOT NULL,
    PRIMARY KEY NONCLUSTERED ([Id] ASC),
);

CREATE TABLE [dbo].[AspNetUserRoles] (
    [UserId] NVARCHAR (128) NOT NULL,
    [RoleId] NVARCHAR (128) NOT NULL,
    CONSTRAINT [PK_dbo.AspNetUserRoles] PRIMARY KEY CLUSTERED ([UserId] ASC, [RoleId] ASC),
    CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId] FOREIGN KEY ([RoleId]) REFERENCES [dbo].[AspNetRoles] ([Id]) ON DELETE CASCADE,
    CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE
);


CREATE TABLE [dbo].[AspNetUserClaims] (
    [Id]         INT            IDENTITY (1, 1) NOT NULL,
    [ClaimType]  NVARCHAR (MAX) NULL,
    [ClaimValue] NVARCHAR (MAX) NULL,
    [User_Id]    NVARCHAR (128) NOT NULL,
    CONSTRAINT [PK_dbo.AspNetUserClaims] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_dbo.AspNetUserClaims_dbo.AspNetUsers_User_Id] FOREIGN KEY ([User_Id]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE
);

GO
CREATE NONCLUSTERED INDEX [IX_User_Id]
    ON [dbo].[AspNetUserClaims]([User_Id] ASC);

CREATE TABLE [dbo].[AspNetUserLogins] (
    [UserId]        NVARCHAR (128) NOT NULL,
    [LoginProvider] NVARCHAR (128) NOT NULL,
    [ProviderKey]   NVARCHAR (128) NOT NULL,
    CONSTRAINT [PK_dbo.AspNetUserLogins] PRIMARY KEY CLUSTERED ([UserId] ASC, [LoginProvider] ASC, [ProviderKey] ASC),
    CONSTRAINT [FK_dbo.AspNetUserLogins_dbo.AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE
);

GO
CREATE NONCLUSTERED INDEX [IX_UserId]
    ON [dbo].[AspNetUserLogins]([UserId] ASC);

Step 2: Migrate Data

DECLARE @CurrentDate DATETIME 
DECLARE @MinDate DATETIME
SET @MinDate = CAST(0 AS DATETIME)
SET @CurrentDate = GETDATE()

-- Insert ASP.NET Users

 INSERT INTO dbo.AspNetUsers (
    Id
    ,UserName
    ,PasswordHash
    ,Discriminator
    ,SecurityStamp
    ,ApplicationId
    ,LoweredUserName
    ,MobileAlias
    ,IsAnonymous
    ,LastActivityDate
    ,LegacyPasswordHash
    ,MobilePIN
    ,Email
    ,LoweredEmail
    ,PasswordQuestion
    ,PasswordAnswer
    ,IsApproved
    ,IsLockedOut
    ,CreateDate
    ,LastLoginDate
    ,LastPasswordChangedDate
    ,LastLockoutDate
    ,FailedPasswordAttemptCount
    ,FailedPasswordAnswerAttemptWindowStart
    ,FailedPasswordAnswerAttemptCount
    ,FailedPasswordAttemptWindowStart
    ,Comment
    )
SELECT U.UserId
    ,U.UserName
    ,(M.Password + '|' + CAST(M.PasswordFormat AS VARCHAR) + '|' + M.PasswordSalt) AS PasswordHash
    ,'User' AS Discriminator
    ,NewID() AS SecurityStamp
    ,U.ApplicationId
    ,LOWER(U.UserName) AS LoweredUserName
    ,NULL AS MobileAlias
    ,U.IsAnonymous
    ,U.LastActivityDate
    ,M.Password
    ,NULL AS MobilePIN
    ,M.Email
    ,LOWER(M.Email) AS LoweredEmail
    ,M.PasswordQuestion
    ,M.PasswordAnswer
    ,M.IsApproved
    ,M.IsLockedOut
    ,M.CreateDate
    ,M.LastLoginDate
    ,M.LastPasswordChangedDate
    ,M.LastLockoutDate
    ,M.FailedPasswordAttemptCount
    ,@CurrentDate AS FailedPasswordAnswerAttemptWindowStart
    ,M.FailedPasswordAnswerAttemptCount
    ,M.FailedPasswordAttemptWindowStart
    ,M.Comment
FROM [dbo].[Users] AS U
LEFT JOIN [dbo].[Memberships] AS M ON M.ApplicationId = U.ApplicationId
    AND U.UserId = M.UserId;

-- insert roles
INSERT INTO dbo.AspNetRoles(Id,Name)
SELECT RoleId, RoleName
FROM dbo.Roles;

-- insert users in roles
INSERT INTO dbo.AspNetUserRoles(UserId,RoleId)
SELECT UserId,RoleId
FROM dbo.UsersInRoles;

Step 3: Upgrade to ASP.NET Identity 2.1.0 Schema

If you only upgraded the reference to 2.0 without schema changes, you would encounter the following exception:

An exception of type 'System.InvalidOperationException' occurred in Microsoft.AspNet.Identity.EntityFramework.dll but was not handled in user code

Additional information: The model backing the 'ApplicationDbContext' context has changed since the database was created. This could have happened because the model used by ASP.NET Identity Framework has changed or the model being used in your application has changed. To resolve this issue, you need to update your database. Consider using Code First Migrations to update the database (http://go.microsoft.com/fwlink/?LinkId=301867).  Before you update your database using Code First Migrations, please disable the schema consistency check for ASP.NET Identity by setting throwIfV1Schema = false in the constructor of your ApplicationDbContext in your application.

        public ApplicationDbContext() : base("ApplicationServices", throwIfV1Schema:false)

Use the follow script to upgrade the database schema to resolve this issue:

/*RenameColumn(table: "dbo.AspNetUserClaims", name: "User_Id", newName: "UserId");

AddColumn("dbo.AspNetUsers", "Email", c => c.String());

AddColumn("dbo.AspNetUsers", "IsConfirmed", c => c.Boolean(nullable: false));

AlterColumn("dbo.AspNetUsers", "UserName", c => c.String(nullable: false));

DropColumn("dbo.AspNetUsers", "Discriminator");
*/



EXEC sp_rename
    @objname = '[dbo].[AspNetUserClaims].User_Id',
    @newname = 'UserId',
    @objtype = 'COLUMN';


ALTER TABLE [dbo].[AspNetUsers] ADD IsConfirmed BIT NOT NULL DEFAULT(1);

ALTER TABLE [dbo].[AspNetUsers] ALTER COLUMN UserName NVARCHAR(256) NOT NULL;

ALTER TABLE [dbo].[AspNetUsers] DROP COLUMN Discriminator;

/*
Invalid column name 'EmailConfirmed'.
Invalid column name 'PhoneNumber'.
Invalid column name 'PhoneNumberConfirmed'.
Invalid column name 'TwoFactorEnabled'.
Invalid column name 'LockoutEndDateUtc'.
Invalid column name 'LockoutEnabled'.
Invalid column name 'AccessFailedCount'.
*/
ALTER TABLE [dbo].[AspNetUsers] ADD EmailConfirmed BIT NOT NULL DEFAULT(1);
ALTER TABLE [dbo].[AspNetUsers] ADD PhoneNumber NVARCHAR(MAX) NULL;
ALTER TABLE [dbo].[AspNetUsers] ADD PhoneNumberConfirmed BIT NOT NULL DEFAULT(0);
ALTER TABLE [dbo].[AspNetUsers] ADD TwoFactorEnabled BIT NOT NULL DEFAULT(0);
ALTER TABLE [dbo].[AspNetUsers] ADD LockoutEndDateUtc DATETIME NULL;
ALTER TABLE [dbo].[AspNetUsers] ADD LockoutEnabled BIT NOT NULL DEFAULT(1);
ALTER TABLE [dbo].[AspNetUsers] ADD AccessFailedCount INT NOT NULL DEFAULT(0);

Step 4: Remember to add the new columns into the application user class

If you want to use the extra columns added above, remember to add them into the ApplicationUser class since they are not properties of IdentityUser.

public class ApplicationUser : IdentityUser
    {
        public ApplicationUser()
        {
            CreateDate = DateTime.Now;
            IsApproved = false;
            LastLoginDate = DateTime.Now;
            LastActivityDate = DateTime.Now;
            LastPasswordChangedDate = DateTime.Now;
            LastLockoutDate = DateTime.Parse("1/1/1754");
            FailedPasswordAnswerAttemptWindowStart = DateTime.Parse("1/1/1754");
            FailedPasswordAttemptWindowStart = DateTime.Parse("1/1/1754");
        }

        public System.Guid ApplicationId { get; set; }
        public string MobileAlias { get; set; }
        public bool IsAnonymous { get; set; }
        public System.DateTime LastActivityDate { get; set; }
        public string MobilePIN { get; set; }
        public string LoweredEmail { get; set; }
        public string LoweredUserName { get; set; }
        public string PasswordQuestion { get; set; }
        public string PasswordAnswer { get; set; }
        public bool IsApproved { get; set; }
        public bool IsLockedOut { get; set; }
        public System.DateTime CreateDate { get; set; }
        public System.DateTime LastLoginDate { get; set; }
        public System.DateTime LastPasswordChangedDate { get; set; }
        public System.DateTime LastLockoutDate { get; set; }
        public int FailedPasswordAttemptCount { get; set; }
        public System.DateTime FailedPasswordAttemptWindowStart { get; set; }
        public int FailedPasswordAnswerAttemptCount { get; set; }
        public System.DateTime FailedPasswordAnswerAttemptWindowStart { get; set; }
        public string Comment { get; set; }

    }

If you don’t need those attributes, you can drop those columns from ASPNETUsers table.

Summary

After several hours’ hard work, my migration is successful.