Click here to Skip to main content
Click here to Skip to main content

How To Migrate ASP.NET Users From Membership To Identity Provider

, 21 Jul 2014 CPOL
Rate this:
Please Sign up or sign in to vote.
In this article I explain step by step how to migrate users from ASP.NET Membership to ASP.NET Identity

Introduction

Information Technology is countiniuosly evolving and often you need to update your old application with the new framework. With the advent of ASP.NET Identity, user's management has been radically changed, before many applications used the Microsoft ASP.NET Membership Provider that allows an easy user managment inteface. 
Now coming up the need to migrate these users to the new ASP.NET Identity Management. 
To do this you can use scripts that allow the correct table creation and data migration from one provider to another (ASP.NET Membership -> ASP.NET Identity. 
Here are the steps to follow:

Step By Step

STEP 1: Remove the default tables created by ASP.NET Identity

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

STEP 2: Create Table Users (AspNetUsers):

 

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].[aspnet_Applications] ([ApplicationId]), );

STEP 3: Import Users from Membership:

INSERT INTO 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 aspnet_Users.UserId,
aspnet_Users.UserName ,(aspnet_Membership.Password+'|'+CAST(aspnet_Membership.PasswordFormat as varchar)+'|'+aspnet_Membership.PasswordSalt) AS PasswordHash,
'User',
NewID(),
aspnet_Users.ApplicationId,
aspnet_Users.LoweredUserName,
aspnet_Users.MobileAlias,
aspnet_Users.IsAnonymous,
aspnet_Users.LastActivityDate,
aspnet_Membership.Password,
aspnet_Membership.MobilePIN,
aspnet_Membership.Email,
aspnet_Membership.LoweredEmail,
aspnet_Membership.PasswordQuestion,
aspnet_Membership.PasswordAnswer,
aspnet_Membership.IsApproved,
aspnet_Membership.IsLockedOut,
aspnet_Membership.CreateDate,
aspnet_Membership.LastLoginDate,
aspnet_Membership.LastPasswordChangedDate,
aspnet_Membership.LastLockoutDate,
aspnet_Membership.FailedPasswordAttemptCount, aspnet_Membership.FailedPasswordAnswerAttemptWindowStart,
aspnet_Membership.FailedPasswordAnswerAttemptCount,
aspnet_Membership.FailedPasswordAttemptWindowStart,
aspnet_Membership.Comment 
FROM aspnet_Users 
LEFT OUTER JOIN aspnet_Membership ON aspnet_Membership.ApplicationId = aspnet_Users.ApplicationId 
AND aspnet_Users.UserId = aspnet_Membership.UserId;

STEP 4: Create Table Roles:

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

STEP 5: Importing Roles from Membership:

INSERT INTO AspNetRoles(Id,Name)
SELECT RoleId,RoleName
FROM aspnet_Roles;

STEP 6: Create Table Roles for Users (AspNetUserRoles):

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

STEP 7: Importing Roles for Users from Membership:

INSERT INTO AspNetUserRoles(UserId,RoleId) 
SELECT UserId,RoleId FROM aspnet_UsersInRoles;

STEP 8: Creating Tables Other ASP.NET Identity:

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

Once you have completed the last step is possbile to start your application and use the imported users to access. 

Happy Coding;)

 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

congiuluc
Web Developer
Italy Italy
I am an Italian developer specialized on ASP.NET web applications.
I consider myself a lucky person because my work coincides with my passion: Develop code!
I began to develop code from the age of about ten years with the infamous Commodore 64.
My turning point was the advent of the internet since 1995 and I started to develop websites first static and then dynamic (ASP) before moving to. NET platform since then ... I do not have more 'stopped!
Follow on   Twitter   LinkedIn

Comments and Discussions

 
QuestionCannot use resulting database in Identity Pinmemberllahman23-Jan-15 6:11 
AnswerRe: Cannot use resulting database in Identity Pinmembercongiuluc25-Jan-15 10:04 
GeneralMy vote of 2 Pinmemberllahman23-Jan-15 6:05 
GeneralRe: My vote of 2 Pinmembercongiuluc23-Jan-15 6:09 
Questionhow to migrate aspnet_Profile data from Asp.net Membership to Asp.net Identity Pinmemberjain_vinay8618-Nov-14 1:36 
AnswerRe: how to migrate aspnet_Profile data from Asp.net Membership to Asp.net Identity Pinmembercongiuluc18-Nov-14 5:40 
QuestionHow To Migrate ASP.NET Users From Membership To Identity Provider PinmemberKHHP4-Sep-14 11:57 
AnswerRe: How To Migrate ASP.NET Users From Membership To Identity Provider Pinmembercongiuluc7-Sep-14 6:06 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.150414.1 | Last Updated 21 Jul 2014
Article Copyright 2014 by congiuluc
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid