Migrating Legacy Apps to the New SimpleMembership Provider

Asp.Net MVC4 uses the new SimpleMembership provider, changes the table structure and adds a new hashing algorithm. The reasons for the changes can be found in this article by Jon Galloway. This article shows how to migrate your existing apps to the new provider.

I’m assuming that you stored your passwords in the unrecoverable SHA-1 format. If you didn’t, then you’ll have to change a couple of things. All of my apps are done this way so… I’m also assuming that you have created the basic skeleton of the new app and ran it once so the correct tables will be created.

First, we’ll look at the new tables. Previously, we had all of those aspnet_xxxxxx tables. Here’s the new ones.

UserProfile	Contains all of the elements relevant to the user. This is a combination of the aspnet_Users table and the aspnet_Profiles table.
webpages_Membership	Stores the password info when not using OAuth, Live, Facebook, etc. This table is somewhat of a match to the aspnet_Membership table.
webpages_OAuthMembership	Stores the provider info when using OAuth, Live, Facebook, etc.
webpages_Roles	The roles available in the system. Matches the aspnet_Roles table.
webpages_UsersInRoles	The junction table used to place users in roles. Matches the aspnet_UsersInRoles table.

Here’s a diagram of the tables.

Notice there is no ApplicationId. From reading Jon’s post I’m assuming they decided it was much simpler and probably pretty common to imbed the membership tables in the application database or one specifically for the app.

Preparation

In the old membership all of the custom properties in a blob field. SimpleMembership let’s you add columns to the UserProfile table. So do that now. In your app, you’ll also need to modify the UserProfile class in the AccountModels.cs file to have corresponding properties. I have FirstName, LastName and Phone in my example so you’ll see where those get copied.

We also need to add a class. Paste the following into the AccountModels.cs file.

[Table("webpages_Membership")]
public class webpages_Membership
{
    [Key]
    public int UserId { get; set; }

    public Nullable<DateTime> CreateDate { get; set; }

    public string ConfirmationToken { get; set; }

    public bool IsConfirmed { get; set; }

    public Nullable<DateTime> LastPasswordFailureDate { get; set; }

    public int PasswordFailuresSinceLastSuccess { get; set; }

    public string Password { get; set; }

    public Nullable<DateTime> PasswordChangedDate { get; set; }

    public string PasswordSalt { get; set; }

    public string PasswordVerificationToken { get; set; }

    public Nullable<DateTime> PasswordVerificationTokenExpirationDate { get; set; }
}

Now modify the UsersContext to look like this.

public class UsersContext : DbContext
{
    public UsersContext()
        : base("DefaultConnection")
    {
    }

    public DbSet<UserProfile> UserProfiles { get; set; }

    public DbSet<webpages_Membership> Memberships { get; set; }
}

I’m using “DefaultConnection” as my connection string name. You’ll need to modify that accordingly.

This blog post has a UDF that will extract your custom properties from the legacy system. We need to add that. I personally have a database titled Utilities that contains all my functions. You’ll need to create the UDF and place the correct database name in the migration script. Hint: Jay Hilden modified my original and placed a copy in the comments section - use that one!

While your looking at the old tables, get the ApplicationId of the application users you want to migrate. You’ll need in the copy data step.

Copy Your Data

Now that we have everything ready, we need to copy the values from the legacy database to the new database. Open a query window in the new database. In the following script, please LegacyDB with the name of your legacy database. You also need to set the @ApplicationId to the correct value.

Remember I said I had FirstName, LastName and Phone as custom properties. You’ll need to change those lines in this script to get your custom properties. If you don’t have any custom properties, just cut them out.

If you want to test this script in you can wrap it in BEGIN TRAN and ROLLBACK. You’ll just get higher identity values on the UserProfile table but you can reseed if necessary.

-- TODO: Set @ApplicationId to match the correct value from aspnet_Applications
DECLARE @ApplicationId uniqueidentifier = 'C9F849A0-68AA-47B0-B51B-4D927A9E52F0'

-- These are the values we're
-- just going to default.
DECLARE @ConfirmationToken nvarchar(128) = null,
    @IsConfirmed bit = 1,
    @LastPasswordFailureDate datetime = null,
    @PasswordFailuresSinceLastSuccess int = 0,
    @PasswordChangedDate datetime = getdate(),
    @PasswordVerificationToken nvarchar(128) = null,
    @PasswordVerificationTokenExpirationDate datetime = null

/* **************** NOTE ***************
This insert statement needs to be modified to handle
your custom profile properties.
It creates our UserProfile record.
The UserId is the primary key that is used 
throughout all of the other tables to
identify a user.
*/
INSERT INTO UserProfile (UserName, FirstName, LastName, Phone)
    SELECT 
        u.UserName,
        Utilities.dbo.GetProfilePropertyValue('FirstName', P.PropertyNames, P.PropertyValuesString), 
        Utilities.dbo.GetProfilePropertyValue('LastName', P.PropertyNames, P.PropertyValuesString), 
        Utilities.dbo.GetProfilePropertyValue('Phone', P.PropertyNames, P.PropertyValuesString)
    FROM LegacyDb.dbo.aspnet_Users AS U 
    INNER JOIN LegacyDb.dbo.aspnet_Membership AS M ON U.UserId = M.UserId 
        AND U.ApplicationId = M.ApplicationId 
    INNER JOIN LegacyDb.dbo.aspnet_Profile AS P ON U.UserId = P.UserId
/* **************** END NOTE *************** */

/* 
This insert creates the membership record and stores the old password and salt.
*/
INSERT INTO webpages_Membership (UserId, CreateDate, ConfirmationToken, IsConfirmed, LastPasswordFailureDate, PasswordFailuresSinceLastSuccess, Password, PasswordChangedDate, PasswordSalt, PasswordVerificationToken, PasswordVerificationTokenExpirationDate)
    SELECT
        up.UserId, 
        m.CreateDate, 
        @ConfirmationToken, 
        M.IsApproved as IsConfirmed, 
        @LastPasswordFailureDate, 
        @PasswordFailuresSinceLastSuccess, 
        m.Password, 
        @PasswordChangedDate, 
        m.PasswordSalt, 
        @PasswordVerificationToken, 
        @PasswordVerificationTokenExpirationDate 
    FROM LegacyDb.dbo.aspnet_Users AS U 
    INNER JOIN LegacyDb.dbo.aspnet_Membership AS M ON U.UserId = M.UserId 
        AND U.ApplicationId = M.ApplicationId 
    INNER JOIN LegacyDb.dbo.aspnet_Profile AS P ON U.UserId = P.UserId
    INNER JOIN UserProfile up on up.UserName = u.UserName

-- Now we move the roles
INSERT INTO webpages_Roles (RoleName) 
    SELECT r.RoleName FROM LegacyDb.dbo.aspnet_Roles r WHERE r.ApplicationId = @ApplicationId

-- Get everybody in the correct roles.
INSERT INTO webpages_UsersInRoles
    SELECT up.UserId, wp_R.RoleId
    FROM LegacyDb.dbo.aspnet_UsersInRoles a_UIR
    INNER JOIN LegacyDb.dbo.aspnet_Roles a_R ON a_UIR.RoleId = a_R.RoleId
    INNER JOIN webpages_Roles wp_R ON a_R.RoleName = wp_R.RoleName
    INNER JOIN LegacyDb.dbo.aspnet_Users a_U ON a_UIR.UserId = a_U.UserId
    INNER JOIN UserProfile up ON a_U.UserName = up.UserName

Making The App Work

You might think you’re ready to go but not yet. The old membership used a different hash to store passwords. This means when your user goes to login, they’ll get rejected because the hashed password values don’t match.

What we’re going to do is manually validate the password using the old hash and then have SimpleMembership “change” the password so that the correct hash is stored in the tables.

Here’s the code.

public class LegacySecurity
{
    /// <summary>
    /// The user's profile record.
    /// </summary>
    private UserProfile userProfile;

    /// <summary>
    /// The users membership record.
    /// </summary>
    private webpages_Membership membership;

    /// <summary>
    /// The clear text password.
    /// </summary>
    private string clearPassword;

    /// <summary>
    /// The password after it has been hashed using SHA1.
    /// </summary>
    private string sha1HashedPassword;

    /// <summary>
    /// The user's user name.
    /// </summary>
    private string userName;

    /// <summary>
    /// Inidcates if the authentication token in the cookie should be persisted beyond the current session.
    /// </summary>
    private bool persistCookie;

    /// <summary>
    /// Validates the user against legacy values.
    /// </summary>
    /// <param name="userName">The user's UserName.</param>
    /// <param name="password">The user's password.</param>
    /// <param name="persistCookie">Inidcates if the authentication token in the cookie should be persisted beyond the current session.</param>
    /// <returns>true if the user is validated and logged in, otherwise false.</returns>
    public bool Login(string userName, string password, bool persistCookie = false)
    {
        this.userName = userName;
        this.clearPassword = password;
        this.persistCookie = persistCookie;

        if (!GetOriginalValues())
        {
            return false;
        }

        SetHashedPassword();

        if (this.sha1HashedPassword != this.membership.Password)
        {
            return false;
        }

        this.SetPasswordAndLoginUser();

        return true;
    }

    /// <summary>
    /// Gets the original password values
    /// </summary>
    protected bool GetOriginalValues()
    {
        using (var context = new Models.UsersContext())
        {
            this.userProfile = context.UserProfiles.Where(x => x.UserName.ToLower() == userName.ToLower()).SingleOrDefault();

            if (this.userProfile == null)
            {
                return false;
            }

            this.membership = context.Memberships.Where(x => x.UserId == this.userProfile.UserId).SingleOrDefault();

            if (this.membership == null)
            {
                return false;
            }
            
            if (!this.membership.IsConfirmed)
            {
                return false;
            }
        }

        return true;
    }

    /// <summary>
    /// Encrypts the password using the SHA1 algorithm.
    /// </summary>
    /// <remarks>
    /// Many thanks to Malcolm Swaine for the hashing code.
    /// http://www.codeproject.com/Articles/32600/Manually-validating-an-ASP-NET-user-account-with-a
    /// </remarks>
    protected void SetHashedPassword()
    {
        byte[] bIn = Encoding.Unicode.GetBytes(clearPassword);
        byte[] bSalt = Convert.FromBase64String(membership.PasswordSalt);
        byte[] bAll = new byte[bSalt.Length + bIn.Length];
        byte[] bRet = null;
        Buffer.BlockCopy(bSalt, 0, bAll, 0, bSalt.Length);
        Buffer.BlockCopy(bIn, 0, bAll, bSalt.Length, bIn.Length);

        HashAlgorithm s = HashAlgorithm.Create("SHA1");
            bRet = s.ComputeHash(bAll);
        string newHash = Convert.ToBase64String(bRet);
            this.sha1HashedPassword = newHash;
    }

    /// <summary>
    /// Sets the password using the new algorithm and perofrms a login.
    /// </summary>
    protected void SetPasswordAndLoginUser()
    {
        var token = WebMatrix.WebData.WebSecurity.GeneratePasswordResetToken(this.userName, 2);
            WebMatrix.WebData.WebSecurity.ResetPassword(token, clearPassword);
            WebMatrix.WebData.WebSecurity.Login(userName, clearPassword, persistCookie);
    }
}

Here’s how this works. GetOriginalValues retrieves the original hashed password and salt. SetHashedPassword hashes the password entered by the user. The two hashed strings are compared. If they don’t match, false is returned.

If they do, then SetPasswordAndLoginUser gets a reset token and then immediately resets the password using SimpleMembership. Now everything is stored correctly. We have to use GeneratePasswordResetToken instead of ChangePassword because even though we know the password, we can’t use it because the hashes won’t match.

We login the user so that the user doesn’t know anything different happened.

Not Quite Done

We have one thing left to do. We have to get the system to call our Login method. In the AccountController, go to the Login method and replace it with this.

[HttpPost]
[AllowAnonymous]
[ValidateAntiForgeryToken]
public ActionResult Login(LoginModel model, string returnUrl)
{
    if (ModelState.IsValid && WebSecurity.Login(model.UserName, model.Password, persistCookie: model.RememberMe))
    {
        return RedirectToLocal(returnUrl);
    }

    // Here we check to see if the user has a legacy password.
    var legacySecurity = new LegacySecurity();
    if (ModelState.IsValid && legacySecurity.Login(model.UserName, model.Password, persistCookie: model.RememberMe))
    {
        return RedirectToLocal(returnUrl);
    }

    // If we got this far, something failed, redisplay form
    ModelState.AddModelError("", "The user name or password provided is incorrect.");
    return View(model);
}

The nice thing about this code is that when we’re done allowing the old hash, we simply remove the code in the middle and it is exactly as it was.

What about performance? Once a user is converted to the new hash, they won’t hit the legacy check unless they use the wrong password so there should be minimal impact.

The thing I like about this is that it keeps the user from knowing anything changed.

Hope this makes life easier for you.