Password hash algorithm in SQL

Jul 28, 2014 at 10:37 AM
I'm trying to create user accounts in a SQL job, but I cannot seem to figure out how the password hash column is encoded.

I read on stack overflow that the same encoding that was used in Identity 1.0 is being used in 2.0, but it doesn't seem to work.

This is what I have so far for encoding the password, I think I'm missing a salt column?
SET @hashed_password = dbo.fbase64_encode(HASHBYTES('SHA2_512', CAST(@ClearTextPassword AS varbinary(MAX))))
I have tried all the available HASHBYTES algorithms as well as adding the SecurityStamp as salt, nothing worked.

I would be grateful if someone could shed some light on the matter.
Developer
Jul 31, 2014 at 9:40 PM
You need to hash the password using Identity APIs and it might not be done directly in the SQL statement. You can create the SQL script through a console application and hash the password through PasswordHasher.HashPassword method
Aug 1, 2014 at 6:50 AM
Edited Aug 1, 2014 at 6:51 AM
Yeah, I figured that I'll have to encapsulate the hashing logic in a CLR function that can then be called from the SQL job. Do you perhaps have an example of how Identity 2.0 hashes the password? I'm asking this because I want to avoid using the Identity API to keep the CLR as light weight as possible. I know at it's core, the API uses the Crypto library to hash the password, but I'm unsure about the exact logic?

Thanks.