0

Today I got up and running on an old version/ development version of my companys database, one of the things I discovered while looking through the 'Members' table of the DB is that the passwords are stored in plaintext as opposed to having been hashed and salted. So my question is how do I approach iterating across a large database to convert these plaintext passwords to hashed passwords.

The data is stored in a format of dbo.Tradesmen with the passwords column simply being called Password

Thanks for any help

GMWQ
  • 33
  • 1
  • 6
  • You have to fix the data and the applications that use all at the same time. To fix the data you just have to do it row by row. There is no alternative there. – Sean Lange Apr 11 '18 at 15:57

1 Answers1

1

Firstly, I would recommend Salting the passwords with a random GUID before they are hashed. This will ensure that no hashes are the same, even if two users happen to use the same password:

ALTER TABLE dbo.Tradesmen
ADD Salt uniqueidentifier;

Set the column so a new Salt is automatically created when a new record is inserted:

ALTER TABLE dbo.Tradesmen
ADD CONSTRAINT salt_def  
DEFAULT NewID() FOR Salt;

Populate all existing records with a Salt:

UPDATE dbo.Tradesmen
SET Salt = NewID()
WHERE Salt is null;

Create a field to store the hashed passwords:

ALTER TABLE dbo.Tradesmen
ADD PasswordHash BINARY(64);

Then run an update to create the hashed and salted passwords:

UPDATE dbo.Tradesmen
SET PasswordHash = HASHBYTES('SHA2_512', Password+Cast(Salt as NVARCHAR(36)))
WHERE PasswordHash is null and Password != '';

Once verified, wipe the Password field. To verify a user, I would recommend using a function like this (assuming that the user accounts are in a field called User). If a record is returned, the login combination is correct:

CREATE Function dbo.VerifyUser (
    @p_User varchar(50),
    @p_Password varchar(50)
)
RETURNS TABLE
AS
RETURN
(
    SELECT Top 1 User
    from dbo.Tradesmen
    where User = @p_User
        and
        PasswordHash = HASHBYTES('SHA2_512', @p_Password+CAST(Salt as NVARCHAR(36)))
)
alondo
  • 141
  • 1
  • 4
  • This is good for a purely t-SQL solution, but it's worth being aware that using a SHA hash for passwords leaves you vulnerable to brute forcing. It's better to use PBKDF2. See https://stackoverflow.com/questions/7837547/is-there-a-sql-implementation-of-pbkdf2 – Paul Apr 11 '18 at 16:49