This site uses cookies to deliver our services. By using this site, you acknowledge that you have read and understand our Cookie and Privacy policy. Your use of Kontext website is subject to this policy. Allow Cookies and Dismiss

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

2012 views 1 comments last modified about 4 years ago Raymond

In this page

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.

image

Related pages

Issue - Unable to get property 'apply' of undefined or null reference occurred in Angular 4.*, VS2017 15.3, ASP.NET Core 2.0

6128 views   10 comments last modified about 2 years ago

Issue Context After installed Visual Studio 2017 15.3 preview and .net core 2.0 preview SDK, I upgraded one of my existing asp.net core project to 2.0. The project was created using ‘dotnet new angular’ SPA template.  I also upgraded all the client app packages to the latest. For exa...

View detail

Tuples in C# (4.x, 7.0, 7.1)

85 views   0 comments last modified about 4 months ago

What is a tuple? Tuple is an ordered sequence, fixed-size, immutable and of heterogeneous objects. Tuple has been existing in other languages such as F#, Python, Perl and etc. for a long time. It was first introduced into C# from C# 4.0 and has been evolving over time. Since C# 7.1, tuple...

View detail

Invoke Hadoop WebHDFS APIs in .NET Core

138 views   0 comments last modified about 4 months ago

Background Apache doesn't provide native official .NET APIs for Hadoop HDFS. The HTTP REST API supports the complete FileSystem / ...

View detail

Logging configuration in .NET core

257 views   0 comments last modified about 9 months ago

.NET core introduces a logging API that works with a number of logging frameworks. The built-in providers are configurable and extensible to support different level loggings.

View detail

Sending Emails in .NET Core Applications

1195 views   0 comments last modified about 9 months ago

Sending emails are common in applications. For example, when user registers, we need to send account activation emails. This post summarize the approaches we can use to send emails in .NET Core 1.x and 2.x.

View detail

Retrieve Http client request metadata like IP address and languages in asp.net core

799 views   0 comments last modified about 9 months ago

IP Address In ASP.NET Core, Request.UserHostAddress has been removed though that attribute exists in the traditional ASP.NET applications. We can use HttpContext.Connection to retrieve the remove client IP address: var ipAddress = HttpContext.Connecti...

View detail

Add comment

Please login first to add comments.  Log in New user?  Register

Comments (1)

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

Ra*** about 4 years ago

I also need to replace or the current Membership class with the new UserManager...