Friday, November 16, 2012

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.


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.

public class webpages_Membership
    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)
        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)
        M.IsApproved as IsConfirmed, 
    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;


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


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

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.


Jim said...

Thanks for answering this question

This is a great solution to an otherwise messy problem

Sebastian said...

great work. do you know if its possible to create anonymous users or include cookies related to any information of simplemembership in order to allow storing values like countryid or currencyid?
best regards

PretzelSteelersFan said...

@Jim. Glad you like it.

PretzelSteelersFan said...
This comment has been removed by the author.
PretzelSteelersFan said...

@Sebastian Sorry forgot to encode the first. Have you tried adding this to your web.config? It let's the system handle it for you.

<globalization culture="auto" uiCulture="auto"/>

SomeDude said...

Had such a hard time finding a way to reset a uses Password from an admin account.

TY for the step by step on this, such a big help!

Brian said...

I cannot tell you how much I appreciate this blog post. Thank you!

jurgen van de water said...

This was very helpful!

Alan Holm said...


I've inherited a legacy .Net system that didn't use the Membership system and I'm battling to try to migrate the existing members to the Simple Membership system tables that I've created in the legacy SQL Database in the new MVC4 system.

Do you maybe know of any tutorial that could help with guuidance in how to tackle that?

I'm sure I'm not the only one tasked with modernising legacy systems to MVC4, so, I'm sure someone must have put some advice together - but so far I haven't been able to Google anything except your article - but it's for legacy systems that were using an earlier version of the Membership system.

Maybe I need to try to find detailed info. on the insides of the Membership system (but it seems convoluted and verbose).

I was hoping to avoid re-inventing the wheel with having to develop a parallel custom Membership system.

PretzelSteelersFan said...

@SomeDude, @BRian and @Jurgen. You're welcome. Glad it was helpful.

PretzelSteelersFan said...

@Alan Do you have access to the clear passwords for your users? If so, simply create a routine (not part of your app) that loops through and creates new users in the membership system.

If not, then substitute a routine that checks the password against your old system and then create new users. Basically, you're substituting your legacy routine for mine.

ASP.Net Migration said...

Good information on migration legacy application.

Unknown said...
This comment has been removed by the author.
Rich S said...

Holy cow. This post saved me literally hours of work. Thank you so much for not only sharing but sharing well! (explanation, documentation)

Robert McLaws said...

I posted an update that allows this to work with Encrypted passwords as well as Hashed passwords. You can find it here:

gogosweb said...

I think there is a gap on this implementation.
There is not taken in consideration the case that any attacker inserts an existing username and a password of his desire.

On this case the new password will be saved and the attacker will have full control on the account, even on administration of the site if he knows only the admin username.

PretzelSteelersFan said...

@gogosweb Not sure how you got there. Looking at the section Not Quite Done, we can see in the Login method on the Account controller the first step is to validate against the new method. If that fails, the next step validates against the legacy method. If that fails, we return a model error.

Can you post a path for the gap?

PretzelSteelersFan said...

@RobertMcLaws Very nice!

gogosweb said...

Yes you are right.
I ignored this lines on legacy Login method :
if (this.sha1HashedPassword != this.membership.Password)
return false;

Nice job indeed! :)

Chris Lillie said...

I've got to be doing something wrong!
First off, thank you for this article. It is the exact solution that I am looking for.
However, when executing the SQL scripts, I am running into this problem:
UserProfile has a USERID column that is an int while all the other userID's are uniqueidentifiers .

I am getting stuck on the UsersInRoles part of the sql script.

Please help!

PretzelSteelersFan said...

@Chris Are you trying to migrate to SimpleMembership or the new Identity tables? SimpleMembership used int's while the new Identity tables use unique identifiers.

Chris Lillie said...

It does look like I've skipped almost an entire generation as the identity 2.0 seems to be the thing now a days.

However.... I still need to migrate membership provider to simple membership provider.

And.... I did manage to get it working using your site and a script that I found somewhere else.
Thank you for your blog and your reply once again!

PretzelSteelersFan said...

Glad it worked out.

Anonymous said...

Hi Paul,
it works for me, but old system, we have the email in membership and now we dont have this property :(