.NET Membership Database – Hashing Plain Passwords in SQL

0 Flares Twitter 0 Facebook 0 StumbleUpon 0 Google+ 0 LinkedIn 0 0 Flares ×

I had a requirement this week to take a load of plain passwords in the aspnet membership database, and hash them all so that they would continue to work with the membership provider.

My first iteration was a C# console app that ran over the membership provider and made it hash the passwords – this was slow and ran into to issues around password rules.

I then tried using a simple sql compute hash comand in sql server but this also failed – it turns out that the membership provider isn’t storing the salt as a string, but as a base 64 encoded string. It also seems that the hashed password is stored in base 64 encoded format. I finally got this sorted with a few functions and a single stored proc.

First a couple of utility functions to encode and decode base 64 strings:

Next the function to create the hashed and salted password:

And finally the stored procedure to generate new salts, and hash+salt every plain password in the membership database:

, ,

  • jay

    Hi there, this is an older post but I thought I would ask anyways…

    RETURN ( SELECT [text language="()"][/text] = @Data FOR XML PATH(”) )

    When I run that statement in the base64Encode function I get an error Incorrect syntax near ‘=’. and I’m wodnering without a lot of searching and experimenting if you have a quick answer.

  • Andy

    The base64Encode function doesn’t work, syntax errors so SQL won’t run to even create the function.
    Errors are here: [text language="()"][/text] as mentioned by previous comment.

    Found different base64Encode function instead:

    CREATE FUNCTION [dbo].[fnBase64Encode] ( @Input NVARCHAR(MAX) )
    RETURNS VARCHAR(MAX)
    BEGIN
    DECLARE @Input1 VARBINARY(MAX)
    DECLARE @EncodedOutput VARCHAR(MAX)

    SET @Input1 = CONVERT(varbinary(MAX), @Input)
    SET @EncodedOutput = CAST(N” as xml).value(‘xs:base64Binary(sql:variable(“@Input1”))’, ‘VARCHAR(max)’)
    RETURN @EncodedOutput
    END
    GO

    Hope this helps anyone else that comes across this if stuck – even if it is an older article!

The Essential App Marketing Kit
Subscribe To My Newsletter To Get an Entire Chapter From The Book for FREE
Never display this again
0 Flares Twitter 0 Facebook 0 StumbleUpon 0 Google+ 0 LinkedIn 0 0 Flares ×