Convert Plain password into .NET Membership HASH password in T-SQL

Question

We have a system that stores the username/password as plain text. I have been asked to covert this to Membership.

I'd like a SQL function to convert the plain text password into .NET Membership Hashed password. The only HashBytes function I found doesn't even come close to what I see in the .NET Membership table.

I am desperate. Please help.

Possible duplicate http://stackoverflow.com/questions/287517/encrypting-hashing-plain-text-passwords-in-database — Pawan, Jun 07 '13 at 12:44

score 0 · Answer 1 · answered Jun 10 '13 at 12:52

I learnt that you cannot generate the same HASH algorithm in T-SQL and the best way is to simple read your basic table to the ASP.net side, then call the Membership Script to insert users. This cuts down on time as well.

DataSet dtData = DivDatabase.ExecuteSQLString("SELECT * FROM Users");   

foreach (DataRow row in dtData.Tables[0].Rows)
{
    MembershipUser mUser = null;
    mUser = Membership.GetUser(row["Username"].ToString());
    if (mUser == null)
    {
        mUser = Membership.CreateUser(row["Username"].ToString(), row["Password"].ToString(), row["Email"].ToString() );
    }
}

I first check if the userName is not in the system already. This is because I had duplicating usernames and I wanted to eliminate that. For additional information from the old table that doesn't exist in the Membership tables, we agreed with the senior management that we are not going to use the Membership Profile as it's not easy to write queries against this. But I have added sample code for reference.

var profile = System.Web.Profile.ProfileBase.Create(row["Username"].ToString());
profile.SetPropertyValue("FirstName", row["FirstName"].ToString());
profile.SetPropertyValue("LastName", row["LastName"].ToString());
profile.Save();

I hope you find this useful.

Convert Plain password into .NET Membership HASH password in T-SQL

1 Answers1