Update: I'm humbled and excited that this post was chosen as Article of the Day on the offical Microsoft ASP.NET website for March 31, 2015. You used to be able to see it posted here but there's no permalink option. Sorry!

The Story

One of my websites (a Web Forms application founded in 2007) was created using the ASP.NET Membership provider. For many years, the Membership framework served me well and the site hasn’t changed substantially so major infrastructure-level changes were never necessary. Fast forward to present day. This legacy application is undergoing some major changes and growth, and the logical step to support it in the future is to move away from the ASP.NET Membership provider and toward a newer, more extensible framework.

Enter ASP.NET Identity, a subset of Windows Identity Foundation.

Before I dig in too much, I want to make sure I give credit where credit is due. The following articles helped me through this process:

My Scenario

In this incredibly short post I’m going to outline the steps that I took to migrate my code and users to the new ASP.NET Identity framework. I’ll provide code samples and some gotchas that I ran into. But first, here’s a summary of my situation, since yours may differ:

  1. The application uses Web Forms and ASP.NET Membership. At the time of this migration, there was no use of MVC. If you do use MVC though, the code is still applicable (I think!)
  2. Entity Framework (v.6) was introduced fairly recently in a database-first approach. This is not largely relevant, but I do use Entity Framework in some optional steps below.
  3. Roles and Profiles were not used in this site, so I won’t be covering them.
  4. I discarded a lot of the Membership data from the [aspnet_Membership] table; I just really didn’t need most of it.
  5. As much as possible, I wanted to preserve the vanilla implementation of IdentityUser, the model that ASP.NET Identity uses, so any of the additional data from the old Membership tables that I did keep is stored in a separate table.
  6. ASP.NET Identity does not, by default, utilize a security question and answer for password reset. I wanted to preserve this functionality since my site uses it, so I will outline how I did that.
  7. My application database used a single Application ID from the Membership database, so I did not cover the case of a multi-tenant Membership database.
  8. My application is split into a few projects, so if you’re not sure where to put the code I provide, just put it somewhere. Refactor and move it once you understand it (which is exactly what I did.)
  9. Many examples online use Database Migrations to create an extended version of the basic Identity models, but again for the purpose of preserving as much about the vanilla integration of ASP.NET Identity I did not use this approach; if you want to, the process is documented in the first article I linked above.

The Fundamentals

ASP.NET Identity uses a class called IdentityUser which it maps to a database table called [AspNetUsers] to persist user data. I’m not sure if the naming of this table is merely convention, but it’s the default and it seems to work. In combination with this, we also have a UserManager which manages the persistence of the user model. This class provides functionality like UpdateUser(...), ChangePassword(...), GetRoles(...), etc. It replaces much of the functionality from the legacy MembershipUser and rolls them into a manager-type class.

We will be extending IdentityUser into our own subclass and calling it ApplicationUser (pretty common, actually), and likewise extending the UserManager into our own subclass called ApplicationUserManager. This does a few things: first, it allows us to extend the default UserIdentity class if we wanted to (we won’t be), and secondly it allows us to override some properties of the default UserManager; the most important property we’ll need to override is the PasswordHasher with our own implementation. Since ASP.NET Identity’s default UserManager has a different hashing algorithm (PBKDF2) than the legacy one used in ASP.NET Membership (SHA-1 by default), we’ll provide our own implementation of it called SqlPasswordHasher, which is capable of validating both types. This allows us to migrate our users as-is and still allow them to log in.

We’ll also need to create a new implementation of IdentityDbContext, which we’ll call ApplicationDbContext. This just gives Entity Framework a context through which to persist the user data. Don’t worry too much about the details of this, because it’s one line of code.

So in the end we will end up with 4 new classes:

  • ApplicationUser - Model which represents a user
  • ApplicationUserManager - Manager class which provides all functionality for adding/updating/querying users
  • ApplicationDbContext - Context class that Entity Framework uses to persist a user model to the data store
  • SqlPasswordHasher - A magic class that can validate new PBKDF2 passwords, and likewise old SHA-1 passwords for migrated accounts

We’ll also end up with 6 new tables in our database, which I’ll provide the SQL queries to create. I won’t be covering most of these, but you should still create and know about them for the future:

  • [AspNetUsers] - Stores our users, and maps to the ApplicationUser model above
  • [AspNetUsersExt] - This is a custom table, and I use it to store additional details from the Membership tables which allows me to keep the [AspNetUsers] table clean and simple. In this example, I will be storing the security question/answer credentials (and only these). You can change the name if you’d like.
  • [AspNetRoles] - Roles live here (Won’t be covered in this post)
  • [AspNetUserRoles] - Maps users to roles (Won’t be covered in this post)
  • [AspNetUserClaims] - Maps users to claims (if you’re not familiar, a claim is a simple but verifiable statement about a user that you define, e.g. “CanDeletePosts” or “PassportNumber”). Claims work alongside roles, but provide more granular assertions about what a user is authorized to do. (Won’t be covered in this post)
  • [AspNetUserLogins] - This table stores information about other logins that a user has verified, like Facebook or Twitter (Won’t be covered in this post)

Let’s Get Started

  1. Create The ASP.NET Identity Tables
  2. Update Existing Connection String To Add providerName
  3. Add References To ASP.NET Identity Assemblies
  4. Add Code For ASP.NET Identity Functionality
  5. Get Login/Signup Working
  6. Migrate Membership Database Users

Step 1. Create The ASP.NET Identity Tables

The first step I recommend is to create the database tables. Since this just means adding a few new tables, the chance of breaking anything is relatively low. The following script will create the tables described above (sorry, it’s long, but it works on SQL Azure.) I’m not sure why datetime is used instead of datetime2 but it’s working in my project. Feel free to separate these out into individual scripts:

/* Create AspNetUsers Table */
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[AspNetUsers](
  [Id] [nvarchar](128) NOT NULL,
  [Hometown] [nvarchar](max) NULL,
  [Email] [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,
  [LockoutEnabled] [bit] NOT NULL,
  [AccessFailedCount] [int] NOT NULL,
  [UserName] [nvarchar](256) NOT NULL,
 CONSTRAINT [PK_dbo.AspNetUsers] PRIMARY KEY CLUSTERED 
(
  [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

GO

/* Create AspNetUsersExt Table */
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[AspNetUsersExt](
  [UserId] [nvarchar](128) NOT NULL,
  [SecurityQuestion] [nvarchar](256) NULL,
  [SecurityAnswer] [nvarchar](128) NULL,
  [SecurityAnswerSalt] [nvarchar](128) NULL,
 CONSTRAINT [PK_AspNetUsersExt] PRIMARY KEY CLUSTERED 
(
  [UserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
) 

GO

ALTER TABLE [dbo].[AspNetUsersExt]  WITH CHECK ADD  CONSTRAINT [FK_AspNetUserExt_AspNetUsers] FOREIGN KEY([UserId])
REFERENCES [dbo].[AspNetUsers] ([Id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[AspNetUsersExt] CHECK CONSTRAINT [FK_AspNetUserExt_AspNetUsers]
GO

/* Create AspNetRoles Table */
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[AspNetRoles](
  [Id] [nvarchar](128) NOT NULL,
  [Name] [nvarchar](256) NOT NULL,
 CONSTRAINT [PK_dbo.AspNetRoles] PRIMARY KEY CLUSTERED 
(
  [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

GO

/* Create AspNetUserLogins Table */
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[AspNetUserLogins](
  [LoginProvider] [nvarchar](128) NOT NULL,
  [ProviderKey] [nvarchar](128) NOT NULL,
  [UserId] [nvarchar](128) NOT NULL,
 CONSTRAINT [PK_dbo.AspNetUserLogins] PRIMARY KEY CLUSTERED 
(
  [LoginProvider] ASC,
  [ProviderKey] ASC,
  [UserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

GO

ALTER TABLE [dbo].[AspNetUserLogins]  WITH CHECK ADD  CONSTRAINT [FK_dbo.AspNetUserLogins_dbo.AspNetUsers_UserId] FOREIGN KEY([UserId])
REFERENCES [dbo].[AspNetUsers] ([Id])
ON DELETE CASCADE
GO

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

/* Create AspNetUserClaims Table */
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[AspNetUserClaims](
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [UserId] [nvarchar](128) NOT NULL,
  [ClaimType] [nvarchar](max) NULL,
  [ClaimValue] [nvarchar](max) NULL,
 CONSTRAINT [PK_dbo.AspNetUserClaims] PRIMARY KEY CLUSTERED 
(
  [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

GO

ALTER TABLE [dbo].[AspNetUserClaims]  WITH CHECK ADD  CONSTRAINT [FK_dbo.AspNetUserClaims_dbo.AspNetUsers_UserId] FOREIGN KEY([UserId])
REFERENCES [dbo].[AspNetUsers] ([Id])
ON DELETE CASCADE
GO

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

/* Create AspNetUserRoles Table */
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

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
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

GO

ALTER TABLE [dbo].[AspNetUserRoles]  WITH CHECK ADD  CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId] FOREIGN KEY([RoleId])
REFERENCES [dbo].[AspNetRoles] ([Id])
ON DELETE CASCADE
GO

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

ALTER TABLE [dbo].[AspNetUserRoles]  WITH CHECK ADD  CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetUsers_UserId] FOREIGN KEY([UserId])
REFERENCES [dbo].[AspNetUsers] ([Id])
ON DELETE CASCADE
GO

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

Now that we have our tables, we can add the requisite assemblies and classes to our solution which give us the ASP.NET Identity functionality:

Step 2. Update Existing Connection String To Add providerName

If your connection string is missing a providerName attribute, make sure you add it. This is required for the persistence mechanism in ASP.NET Identity to work.

Gotcha Warning: This one really threw me. Long story short, I forgot to update this in my release Web.config transform and took down my production site (only for a few seconds, fortunately!) So, if you use config transforms, make sure you check this!

<add connectionString="Server=SERVERNAME;Database=DB;User ID=USERNAME;Password=PASSWORD;Trusted_Connection=False" name="MyConnectionString" providerName="System.Data.SqlClient" />

Step 3. Add References To ASP.NET Identity Assemblies

You will need to add references to the ASP.NET Identity assemblies, preferably using NuGet as below:

Install-Package Microsoft.AspNet.Identity.Owin
Install-Package Microsoft.Owin.Host.SystemWeb

Optionally if you later want to include Facbeook, Twitter, or other authentication providers, you can do so now (optional):

Install-Package Microsoft.Owin.Security.Facebook
Install-Package Microsoft.Owin.Security.Google
Install-Package Microsoft.Owin.Security.Twitter
Install-Package Microsoft.Owin.Security.MicrosoftAccount

Step 4. Add Code For ASP.NET Identity Functionality

Now that we have our database tables and all of our required assemblies, we need to add the code which ties it all together. If you’re using a single project, you can put these wherever you want. If your application is split into several projects like mine, put them in a deeper assembly like your data tier or authentication layer.

a. Add The ApplicationUser Class

// ApplicationUser.cs
public class ApplicationUser : IdentityUser
{
  public ApplicationUser() {}
}

b. Add The ApplicationDbContext Class

// ApplicationDbContext.cs
public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
  // throwIfV1Schema throws an exception if an existing v.1 Schema
  // exists for ASP.NET Identity
  public ApplicationDbContext()
    : base("MyConnectionString", throwIfV1Schema: false) {}
}

c. Add The SqlPasswordHasher Class

This class will be responsible for authenticating old Membership and new Identity passwords. It uses a specific convention which I will explain later, but all you need to know now is that the old Membership password will be stored in a pipe-delimited format in our database after we migrate our users.

// SqlPasswordHasher.cs
public class SqlPasswordHasher : PasswordHasher
{
  public override PasswordVerificationResult VerifyHashedPassword(string hashedPassword, string providedPassword)
  {
    string[] passwordProperties = hashedPassword.Split('|');
    if (passwordProperties.Length != 3)
    {
      return base.VerifyHashedPassword(hashedPassword, providedPassword);
    }
    else
    {
      string passwordHash = passwordProperties[0];
      int passwordformat = 1;
      string salt = passwordProperties[2];
      if (String.Equals(EncryptPassword(providedPassword, passwordformat, salt), passwordHash,
        StringComparison.CurrentCultureIgnoreCase))
      {
        return PasswordVerificationResult.SuccessRehashNeeded;
      }
      else
      {
        return PasswordVerificationResult.Failed;
      }
    }
  }

  // This is copied from the existing SQL providers and is provided only for back-compat.
  private string EncryptPassword(string pass, int passwordFormat, string salt)
  {
    if (passwordFormat == 0) // MembershipPasswordFormat.Clear
      return pass;

    byte[] bIn = Encoding.Unicode.GetBytes(pass);
    byte[] bSalt = Convert.FromBase64String(salt);
    byte[] bRet = null;

    if (passwordFormat == 1)
    {
      // MembershipPasswordFormat.Hashed 
      HashAlgorithm hm = HashAlgorithm.Create("SHA1");
      if (hm is KeyedHashAlgorithm)
      {
        KeyedHashAlgorithm kha = (KeyedHashAlgorithm) hm;
        if (kha.Key.Length == bSalt.Length)
        {
          kha.Key = bSalt;
        }
        else if (kha.Key.Length < bSalt.Length)
        {
          byte[] bKey = new byte[kha.Key.Length];
          Buffer.BlockCopy(bSalt, 0, bKey, 0, bKey.Length);
          kha.Key = bKey;
        }
        else
        {
          byte[] bKey = new byte[kha.Key.Length];
          for (int iter = 0; iter < bKey.Length;)
          {
            int len = Math.Min(bSalt.Length, bKey.Length - iter);
            Buffer.BlockCopy(bSalt, 0, bKey, iter, len);
            iter += len;
          }
          kha.Key = bKey;
        }
        bRet = kha.ComputeHash(bIn);
      }
      else
      {
        byte[] bAll = new byte[bSalt.Length + bIn.Length];
        Buffer.BlockCopy(bSalt, 0, bAll, 0, bSalt.Length);
        Buffer.BlockCopy(bIn, 0, bAll, bSalt.Length, bIn.Length);
        bRet = hm.ComputeHash(bAll);
      }
    }

    return Convert.ToBase64String(bRet);
  }
}

d. Add The ApplicationUserManager Class

// ApplicationUserManager.cs
public class ApplicationUserManager : UserManager<ApplicationUser>
{
  public ApplicationUserManager() 
    : base(new UserStore<ApplicationUser>(new ApplicationDbContext()))
  {
    this.PasswordHasher = new SqlPasswordHasher();
  }
}

Step 5. Get Login/Signup Working

Your implementation of login and signup may differ, but here are a few key points:

  • User creation is done by populating an instance of ApplicationUser and then passing it to an instance of ApplicationUserManager to persist.
  • The ApplicationUserManager provides a public property for PasswordHasher. We can directly reference this to utilize our SqlPasswordHasher and log users in whether they provide their legacy ASP.NET Membership password, or a new ASP.NET Identity password. This is why I do not use an instance of a SignInManager, which otherwise doesn’t provide the lower-level interfaces to do this (that I’m aware of–also this works so I didn’t want to change it.)
  • If a user logs in with their old Membership password, we will take advantage of having their password in memory and reset the user’s password to the new format. This isn’t necessary, but it’s a nice option to have.
  • In the user migration scripts below, notice how we concatenate our old Membership password with the password format and salt? The SqlPasswordHasher has a method called VerifyHashedPassword(...) that returns one of three results:
    • PasswordVerificationResult.Success - All good, the user’s password is valid
    • PasswordVerificationResult.Failed - The user’s password could not be validated
    • PasswordVerificationResult.SuccessRehashNeeded - The user’s password was validated by our SqlPasswordHasher but we have determined that it was a legacy Membership password. At this point we may choose to re-apply the user’s password.

a. Create A User

Here, we create a user and then add their security question and answer to the data store. Note that after you create the account, you would have to log the user in as well. The details of how to do that are covered next.

var user = new ApplicationUser();
user.UserName = username;
user.Email = email;
user.EmailConfirmed = true;
user.LockoutEnabled = true;
if (_userManager.Create(user, password) == IdentityResult.Success)
{
  // Add the user's security question and answer
  // This uses another Entity Context, and is separate from ASP.NET Identity
  var userExts = new AspNetUsersExt();
  userExts.UserId = user.Id;

  // These 3 properties are up to you; my implementation may be different
  // than what you need
  userExts.SecurityQuestion = securityQuestion;
  userExts.SecurityAnswerSalt = salt;
  userExts.SecurityAnswer = securityAnswer;

  using (var entities = new MyEntities())
  {
    entities.AspNetUsersExts.Add(userExts);
    entities.SaveChanges();
  }

  // All done
  // You would now log the user in
}

b. Log In With The User

Below is quick example of how you could log in a user, with an example of how to re-hash the password. This example uses OWIN context, which admittedly I don’t really understand at this point, so just copy/paste that part. This is a refactored version of my code, but should give you a pretty good idea:

protected bool Login(string username, string password)
{
  // Instantiate a new ApplicationUserManager and find a user based on provided Username
  var userManager = new ApplicationUserManager();
  var user = userManager.FindByName(username);

  // Invalid user, fail login
  if (user == null || userManager.IsLockedOut(user.Id))
  {
    InvalidLogin(); // Do something here to tell the user
    return false;
  }

  // Valid user, verify password
  var result = userManager.PasswordHasher.VerifyHashedPassword(user.PasswordHash, password);
  if (result == PasswordVerificationResult.Success)
  {
    UserAuthenticated(userManager, user);
  }
  else if (result == PasswordVerificationResult.SuccessRehashNeeded)
  {
    // Logged in using old Membership credentials - update hashed password in database
    // Since we update the user on login anyway, we'll just set the new hash
    // Optionally could set password via the ApplicationUserManager by using
    // RemovePassword() and AddPassword()
    user.PasswordHash = userManager.PasswordHasher.HashPassword(password);
    UserAuthenticated(userManager, user);
  }
  else
  {
    // Failed login, increment failed login counter
    // Lockout for 15 minutes if more than 10 failed attempts
    user.AccessFailedCount++;
    if (user.AccessFailedCount >= 10) user.LockoutEndDateUtc = DateTime.UtcNow.AddMinutes(15);
    userManager.Update(user);
    InvalidLogin(); // Do something here to tell the user
    return false;
  }
}

private void UserAuthenticated(ApplicationUserManager userManager, ApplicationUser user)
{
  // Create an instance of an AuthenticationManager and Identity to authenticate and sign in the user
  // If all goes well, redirect the user to either the querystring's return URL, or their account
  var authenticationManager = HttpContext.Current.GetOwinContext().Authentication;
  var userIdentity = userManager.CreateIdentity(user, DefaultAuthenticationTypes.ApplicationCookie);
  authenticationManager.SignIn(new AuthenticationProperties() { IsPersistent = false }, userIdentity);
  user.AccessFailedCount = 0;
  user.LockoutEndDateUtc = null;
  userManager.Update(user);
  Response.Redirect(Request.QueryString["ReturnUrl"] ?? "~/Account/");
}

c. Log The User Out

Fairly straightforward. This kills the auth cookie and logs the user out:

var authenticationManager = HttpContext.Current.GetOwinContext().Authentication;
authenticationManager.SignOut();

Step 6. Migrate Membership Database Users

And now the fun part. Do you have good database backups? Yes? Okay good. Back them up again.

In all truth, this script is pretty simple and should be idempotent, but as always, if you’re running queries on a production database, make sure you have a reliable backup. Is your backup script done yet? Okay good let’s move on.

This last step consists of running a script to find users in the [aspnet_Membership] table, joined with [aspnet_Users], and copy them over into the new [AspNetUsers] and, optionally, [AspNetUsersExt] tables. Remember again, the only thing we’re copying into the extended table are the security question and answer fields. If you’re not using the security question and answer method of password reset in your Membership implementation, you can ignore this table (or use it as an example for migrating any other data you may want to keep.)

In the script below, you can alter the TOP 1 to any number that you are comfortable with. I started with 1, and bumped it up to 1,000, then 5,000, then 10,000. In my SQL Azure database, it took roughly 20-25 seconds to migrate 10,000 users. I ran that batch a few times until all of my users were migrated, about 75,000 in total.

The script will copy the basic user details, but with a few things to note:

  • The password will be copied as a 3-part delimited string. Again, our SqlPasswordHasher will make use of these pipe delimiters.
  • Since ASP.NET Identity uses a combination of two fields to determine if a user is locked out ([LockoutEnabled] determines that the user can be locked out, and [LockoutEndDateUtc] actually does the work) we need to set both fields if a user is locked out in the Membership database. In my website, I unfortunately deal with a lot of fake accounts and spammers, so the technique I use is to set [LockoutEnabled] to 1 for all users, and to set [LockoutEndDateUtc] to an arbitrary 1,000 years in the future for locked out Membership accounts. If my site is still up in 1,000 years, they can have their accounts back.
  • The whole thing happens in a transaction. Smart, right?
  • The first part of the query migrates a batch of users idempotently. The second part migrates any security question/answers that were not already moved.
  • Note that we copied the Security Answer Salt from the Membership table. That’s because the PBKDF2 passwords in ASP.NET Identity have a built-in salt, so a separate field is no longer needed in the database to store it. However, we still need it for our security question/answer. I haven’t yet refactored this, but by this point I’m sure you can imagine how.

Gotcha Warning: You’ll notice that the new users table has a field called [SecurityStamp]. This is not a password salt so please do not treat it as such. This is a token used to verify the state of an account and is subject to change at any time. Do not use it as a salt for your application, or for your security question/answer.

BEGIN TRANSACTION MigrateUsers

  /* Migrate users */
  INSERT INTO AspNetUsers (Id,UserName,PasswordHash,SecurityStamp,EmailConfirmed,
  PhoneNumber,PhoneNumberConfirmed,TwoFactorEnabled,LockoutEndDateUtc,LockoutEnabled,AccessFailedCount,
  Email)
  SELECT TOP 1 aspnet_Users.UserId, aspnet_Users.UserName,
  (aspnet_Membership.Password+'|'+CAST(aspnet_Membership.PasswordFormat as varchar)+'|'+aspnet_Membership.PasswordSalt),
  NewID(), 1, NULL, 0, 1, CASE WHEN aspnet_Membership.IsLockedOut = 1 THEN DATEADD(YEAR, 1000, SYSUTCDATETIME()) ELSE NULL END, 1, 0,
  aspnet_Membership.Email
  FROM aspnet_Users
  LEFT OUTER JOIN aspnet_Membership ON aspnet_Membership.ApplicationId = aspnet_Users.ApplicationId 
  AND aspnet_Users.UserId = aspnet_Membership.UserId
  LEFT OUTER JOIN AspNetUsers ON aspnet_Membership.UserId = AspNetUsers.Id
  WHERE AspNetUsers.Id IS NULL

  /* Migrate user question/answer */
  INSERT INTO AspNetUsersExt (UserId, SecurityQuestion, SecurityAnswer, SecurityAnswerSalt)
  SELECT aspnet_Users.UserId, aspnet_Membership.PasswordQuestion, PasswordAnswer, PasswordSalt
  FROM aspnet_Users
  LEFT OUTER JOIN aspnet_Membership ON aspnet_Membership.ApplicationId = aspnet_Users.ApplicationId 
  AND aspnet_Users.UserId = aspnet_Membership.UserId
  LEFT OUTER JOIN AspNetUsersExt ON aspnet_Membership.UserId = AspNetUsersExt.UserId
  LEFT OUTER JOIN AspNetUsers ON aspnet_Membership.UserId = AspNetUsers.Id
  WHERE AspNetUsers.Id IS NOT NULL AND AspNetUsersExt.UserId IS NULL

IF @@ERROR <> 0 
  BEGIN 
    ROLLBACK TRANSACTION MigrateUsers
    RETURN
  END

COMMIT TRANSACTION MigrateUsers

All Done… Almost!

At this point we’ve completed all the requisite steps to move users and make them available to log in. You should be able to log in using any newly-created ASP.NET Identity user, or any legacy migrated Membership user.

Gotcha Warning: One thing I wanted to point out is that the [AspNetUsers] table uses a Guid ID. This table likewise does not have a signup/create date for users. The end result is a table of users with no discernable order nor signup date. I personally prefer to have a signup date and order for users, so I later added a column to the table called [CreatedOnUtc]. You can also use an auto-incrementing integer, but it takes some code and database changes which you’ll need to research.

To add the column to [AspNetUsers] with a default UTC date and time:

ALTER TABLE AspNetUsers 
ADD CreatedOnUtc DateTime2 NULL 
CONSTRAINT DF_AspNetUsers_CreatedOnUtc DEFAULT SYSUTCDATETIME()

To update the values from the Membership database:

UPDATE TOP(1) AspNetUsers 
   SET AspNetUsers.CreatedOnUtc = aspnet_Membership.CreateDate 
   FROM AspNetUsers INNER JOIN aspnet_Membership 
   ON AspNetUsers.Id = aspnet_Membership.UserId
   WHERE AspNetUsers.CreatedOnUtc IS NULL

The [CreatedOnUtc] field does not necessarily need to be added to your ApplicationUser model, though you could add it. I simply wanted to have the data in case I need it in the future.

Please let me know if you have any questions, comments, or concerns below.


Share This Article

I am available for remote contract work

If you have a project that needs help, a process that needs improvement, or an idea that you want a sounding board for, I would love to have a discussion with you.

Learn More →

I am not available for full-time work

I'm currently employed as a Senior Consultant at Magenic and not looking for other opportunities at this time.

Let's Connect

Want free advice, thoughts, or feedback? Tell me what you're working on or describe a challenge you're facing and I'll do my best to help.

Free eBook: 10 Killer Tips for .NET Web API

Be awesome. Download my free 56-page eBook for building performant, scalable, maintainable software using .NET Web API. (There's also a bonus chapter on effectively using HTTP Status Codes.)

Enter your email address below and get it immediately.