2

How to reproduce ASP.NET MVC 4 password hash, produced by the built-in membership system?

Example:
Input: loz123
Output: APeJ4h0M4h7OFz91WwmJUjFfI2Daiq5xaUaZzcevoyWfkPZ3SYFJ48F+YzNrBNvaJA==
The "Salt" field in database is empty.

I am transferring data from one database to another. In the source database passwords are stored as plain text. In the destination database, the passwords should be stored as hashes generated by ASP.NET Membership system. I know the hashing is something about using SHA1 algorithm and base64 encoding, but I can't get the correct outputs. It would be convenient if there was a built-in function in MS SQL Server, so that a query such as the following could be executed:

SELECT Username, Hash(Password) FROM Users
Igor Marković
  • 21
  • 1
  • 2

2 Answers2

1

This may help you to get to the right algorithm:

http://msdn.microsoft.com/en-us/library/system.web.security.sqlmembershipprovider.passwordformat.aspx

You will have to check your specific settings.

The summary from MSDN:

The SQL Server membership provider supports Clear, Encrypted, and Hashed password formats. Clear passwords are stored in plain text, which improves the performance of password storage and retrieval but is less secure, as passwords are easily read if your SQL Server database is compromised. Encrypted passwords are encrypted when stored and can be decrypted for password comparison or password retrieval. This requires additional processing for password storage and retrieval, but is more secure, as passwords cannot easily be determined if the SQL Server database is compromised. Hashed passwords are hashed using a one-way hash algorithm and a randomly generated salt value when stored in the database. When a password is validated, it is hashed with the salt value in the database for verification. Hashed passwords cannot be retrieved.

The PasswordFormat value is specified in the providers section of the Web.config file for the ASP.NET application.

Encrypted and Hashed passwords are encrypted or hashed by default based on information supplied in the machineKey element in your configuration. Note that if you specify a value of 3DES for the validation attribute, or if no value is specified, hashed passwords will be hashed using the SHA1 algorithm.

A custom hash algorithm can be defined using the hashAlgorithmType attribute of the membership Element (ASP.NET Settings Schema) configuration element. If you choose encryption, default password encryption uses AES. You can change the encryption algorithm by setting the decryption attribute of the machineKey configuration element. If you are encrypting passwords, you must provide an explicit value for the decryptionKey attribute in the machineKey element. The default value of AutoGenerate for the decryptionKey attribute is not supported when using encrypted passwords with ASP.NET Membership.

Community
  • 1
  • 1
Szymon
  • 42,577
  • 16
  • 96
  • 114
0

I had to update an older database with "clear" passwords to "hashed" and I wanted to do it with sql - this is what I came up with to make the switch fast and easy.

NOTE: Make a backup first!!

Select * into dbo.aspnet_Membership_BACKUP from [dbo].[aspnet_Membership]

Function to calculate the hashes:

/*
    Create compatible hashes for the older style ASP.Net Membership

    Credit for Base64 encode/decode: http://stackoverflow.com/questions/5082345/base64-encoding-in-sql-server-2005-t-sql
*/
Create Function dbo.AspNetHashCreate (@clearPass nvarchar(64), @encodedSalt nvarchar(64))
Returns nvarchar(128)
as
begin
    declare @binSalt varbinary(128)
    declare @binPass varbinary(128)

    declare @result nvarchar(64)

    Select @binPass = CONVERT(VARBINARY(128), @clearPass)

    --  Passed salt is Base64 so decode to bin, then we'll combine/append it with password
    Select @binSalt = CAST(N'' as XML).value('xs:base64Binary(sql:column("bin"))','VARBINARY(128)') 
        from (Select @encodedSalt as bin) as temp;

    --  Hash the salt + pass, then convert to Base64 for the output
    Select @result = CAST(N'' as XML).value('xs:base64Binary(xs:hexBinary(sql:column("bin")))', 'NVARCHAR(64)')
        from (Select HASHBYTES('SHA1', @binSalt + @binPass) as bin) as temp2;

    --  Debug, check sizes
    --Select DATALENGTH(@binSalt), DATALENGTH(@binPass), DATALENGTH(@binSalt + @binPass)

    return @result
end

Call it like this:

Update [dbo].[aspnet_Membership] set PasswordFormat = 1, Password = dbo.AspNetHashCreate(password, PasswordSalt) where PasswordFormat = 0

Even with my database originally set to "clear" passwords, the salt values were created with each record, however, if for some reason you don't have salt values you can create them with this:

/*
    Create compatible salts for the older style ASP.Net Membership (just a 16 byte random number in Base64)

    Note: Can't use newId() inside function so just call it like so: dbo.AspNetSaltCreate(newId())

    Credit for Base64 encode: http://stackoverflow.com/questions/5082345/base64-encoding-in-sql-server-2005-t-sql
*/
Create Function dbo.AspNetSaltCreate (@RndId uniqueidentifier)
    Returns nvarchar(24)
as
begin
    return
        (Select CAST(N'' as XML).value('xs:base64Binary(xs:hexBinary(sql:column("bin")))', 'NVARCHAR(64)')
            from (select cast(@RndId as varbinary(16)) as bin) as temp)
end

Then use it like this:

Update [dbo].[aspnet_Membership] set PasswordSalt = dbo.AspNetSaltCreate(newId()) where PasswordSalt = ''

Note that you need to generate the Salts FIRST, then the hashes. Enjoy!

Jester
  • 2,728
  • 22
  • 20