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?

@jjj Interesting, let me explore that path, I never used CLR integration yet but this might do the job — Patrick Desjardins
– Patrick Desjardins, Commented Jun 4, 2015 at 4:04
@jjj I did a test and it worked. It is pretty slow to use though but I guess it worth it. To give you an idea, it passes from 7 seconds to 18 minutes :) — Patrick Desjardins
– Patrick Desjardins, Commented Jun 4, 2015 at 5:01
First I'd migrate plain-text passwords into the new DB and put them into TempPassword column in users table. Then a small console app that will go into every record and generate password hash from the plain text and update the record with the hash. Console app will probably be about 30 lines. Then validate you can login and then drop TempPassword column. Quick and dirty, good enough for one-time migration. — trailmax
– trailmax, Commented Jun 4, 2015 at 11:08
There is a great answer on how the default implementation works in c# here stackoverflow.com/questions/20621950/… I recently used the above and this stackoverflow.com/questions/7837547/… to create a solution to generate the hased passwords in pure SQL which can be found here stackoverflow.com/questions/5033886/… — Dave Cornall
– Dave Cornall, Commented Oct 29, 2015 at 17:15

Community · Accepted Answer · 2017-05-23 12:18:33Z

3

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

edited May 23, 2017 at 12:18

CommunityBot

11 silver badge

answered Jun 4, 2015 at 4:50

Mikael Eliasson

5,29727 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Patrick Desjardins Over a year ago

Thank you for your answer. I will definitely take a closer look to your approach. I want to run in SQL because I am creating a huge DB conversion with LinkedServer. I have this old table that need to go into a new one that need the password to be hashed. It is the only table that really require weird manipulation so far. I try to avoid having to have too much .Net code because this is a 1 time deal project. Once the conversion is done we will not need anymore the project.

StuartLC Over a year ago

Good point about the TLOG. OP is already using Rfc2898DeriveBytes, although at only 1000 iterations which isn't sufficient according to OWASP - this should be over 100k by now. However note that by design, this many iterations makes the hashing slow (100ms #hash), meaning that it will take quite a long time for you to migrate a large number of passwords.

Mikael Eliasson Over a year ago

@PatrickDesjardins It would take < 50 LOC of c# even if you want to parallellize it (See Stuarts comment). Are you doing a "hot" migration or will you keep it offline during the migration? If you are doing a hot migration. You might need to prepare the hashes before hand anyway.

Mikael Eliasson Over a year ago

@StuartLC True, that feels like even more reason to keep it out of the database. I guess you need to sync the iterations with what you will use in identity later on though.

StuartLC Over a year ago

A common technique here (e.g. Membership Reboot) is to store the #of hashes, the salt used, and the password hash in one column. This way there's an atomic all or nothing approach.

|

Collectives™ on Stack Overflow

Converting Plain Password to EF Asp.Net Identity PasswordHash

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related