Converting Plain Password to EF Asp.Net Identity PasswordHash

Question

I have a project where we need to migrate a lot of users that has their password in plain text into a new database where we will hash the password.

The new system use Entity Framework and it needs to be authenticated with the Asp.Net Identity framework.

I found that I can generate in C# a correct hashed password that Entity Framework can read without problem.

    public static string HashPassword(string password)
    {
        byte[] salt;
        byte[] buffer2;
        using (var bytes = new Rfc2898DeriveBytes(password, 0x10, 0x3e8))
        {
            salt = bytes.Salt;
            buffer2 = bytes.GetBytes(0x20);
        }
        byte[] dst = new byte[0x31];
        Buffer.BlockCopy(salt, 0, dst, 1, 0x10);
        Buffer.BlockCopy(buffer2, 0, dst, 0x11, 0x20);
        return Convert.ToBase64String(dst);
    }

Is there something similar in SQL that I could use within INSERT statement form a SELECT to the other table?

Answer 1

Not built in, hashing is cpu intensive and normally an operation you would want to avoid on the DB server, I realize migration are not a normal operation though. The solution depends a bit on why you want to run in SQL.

If it's because of simplicity I would look at something like in this question Is there a SQL implementation of PBKDF2?

If it's because of performance I would consider just building a small .net migrator and use bulk inserting/updating. For example with https://github.com/MikaelEliasson/EntityFramework.Utilities#batch-update-entities you could read only the UserId and the plain text Password with a select. Hash it in .net and then update the database in one bulk at probably over 100k updates / second.

Now two slight warnings Make sure you don't end up with plain text passwords in the transaction log. Preferebly by doing the hashing in the source database before it ends up in the new one. Otherwise it's possible to clear the transaction log after the initial import How do you clear the SQL Server transaction log?

Instead of writing the hashing method yourself you can use PasswordHasher which is what Asp.net identity is using by default. It in turn is using Rfc2898DeriveBytes. See this answer https://stackoverflow.com/a/21496255/507279