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?

don't want to use [SQL Server CLR integration](https://msdn.microsoft.com/en-us/library/w2kae45k(v=vs.90).aspx)? — jjj, Jun 04 '15 at 03:43
@jjj Interesting, let me explore that path, I never used CLR integration yet but this might do the job — Patrick Desjardins, Jun 04 '15 at 04: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, Jun 04 '15 at 05: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, Jun 04 '15 at 11:08
There is a great answer on how the default implementation works in c# here http://stackoverflow.com/questions/20621950/asp-net-identity-default-password-hasher-how-does-it-work-and-is-it-secure I recently used the above and this http://stackoverflow.com/questions/7837547/is-there-a-sql-implementation-of-pbkdf2 to create a solution to generate the hased passwords in pure SQL which can be found here http://stackoverflow.com/questions/5033886/generate-asp-net-membership-password-hash-in-pure-t-sql/33420086 — Dave Cornall, Oct 29 '15 at 17:15

score 3 · Answer 1 · edited May 23 '17 at 12:18

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 '17 at 12:18

Community

1
1

answered Jun 04 '15 at 04:50

Mikael Eliasson

5,157
23
27

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. – Patrick Desjardins Jun 04 '15 at 05:05
Good point about the TLOG. OP is already using [Rfc2898DeriveBytes](https://msdn.microsoft.com/en-us/library/system.security.cryptography.rfc2898derivebytes%28v=vs.110%29.aspx?f=255&MSPPError=-2147217396), although at only 1000 iterations which [isn't sufficient according to OWASP](http://stackoverflow.com/a/14263775/314291) - 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. – StuartLC Jun 04 '15 at 05:11
@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 Jun 04 '15 at 06:01
@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. – Mikael Eliasson Jun 04 '15 at 06:01
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. – StuartLC Jun 04 '15 at 08:25
THank you MikaelEliasson and @StuartLC, this is pretty interesting and I think I'll go with something on the side like suggested. I have the luxury to be able to do it offline so this won't be a problem. – Patrick Desjardins Jun 05 '15 at 01:02

Converting Plain Password to EF Asp.Net Identity PasswordHash

1 Answers1