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

2194 views 1 comments last modified about 4 years ago Raymond

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

Set AttachDbFilename as Relative Path in .NET Core

212 views   0 comments last modified about 4 months ago

.NET Framework, you can use |DataDirectory| to configure connection string when connecting to SQL Server database file via attach mode: AttachDbFilename=|DataDirectory|\dbname.mdf In .NET Core, you cannot directly set SQL Server Express connec...

View detail

Instantiate a Service in ConfigureServices Method in .NET Core

36 views   0 comments last modified about 4 months ago

.NET Core is built in with dependency injection. Usually method ConfigureServices in Startup class is used to register services in the container. The signature of the method looks like the following: public void ConfigureServices(IServiceC...

View detail

SQLite in .NET Core with Entity Framework Core

123 views   0 comments last modified about 4 months ago

SQLite is a self-contained and embedded SQL database engine. In .NET Core, Entity Framework Core provides APIs to work with SQLite. This page provides sample code to create a SQLite database using package Microsoft.EntityFrameworkCore.Sqlite . Create sample project ...

View detail

Graphics Programming and Image Processing in .NET Core 2.x

89 views   0 comments last modified about 4 months ago

In .NET Core 2.x, Windows Forms or WPF are not implemented since they are based on GDI+ and DirectX respectively in Windows. In .NET Core 3.0, there is plan to add Desktop Packs which includes UWP. WPF and Windows Forms. However, they will still be Windows-only. In .NET Core applications, you may...

View detail

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

7181 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)

178 views   0 comments last modified about 8 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

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...