Add a Salt with the SQL Server HASHBYTES Function

By:   |   Updated: 2014-07-25   |   Comments (6)   |   Related: > Security


Problem

I am trying to store password hashes in SQL Server. I know I can generate those hashes using the HASHBYTES() function, but I don't see where it takes a salt. I've been told it's good to have a salt. Is there an easy way to do this?

Solution

Indeed there is. However, first, a caveat. If you can, you want to generate the hash in the application. If you don't, there is the potential for a DBA to be able to see the password using SQL Profiler, a server side trace, or through Extended Events. HASHBYTES() doesn't cause these mechanisms to hide the T-SQL that was passed, as can be seen here:

Including a Salt with HASHBYTES()

If you can't do this at the application layer, here's how to do it entirely within SQL Server.

What to Use as a Salt the SQL Server HASHBYTES() function

If you're not familiar with what the salt is when it comes to cryptographic functions, it's basically something added to whatever we're trying to encrypt to make it harder to decrypt the data (two way functions, like symmetric and asymmetric key functions) or find a collision (one way functions, AKA hash functions). The salt should be potentially different for every single piece of encrypted data. The salt should be randomly generated.

Since the salt should be randomly generated, this eliminates basic functions derived from date/time or anything of that sort. SQL Server does have a RAND() function, which does serve as random number generator. In addition, it can be seeded. However, it's a pseudo-random number generator. If you give it the same seed, it'll produce the same results. Therefore, we'll want our potential seed value range to be large.

We can use the time, specifically the hour, minute, second, and millisecond values to generate a reasonable large seed value pool. It is not perfectly random, but nothing ever is when it comes to these functions. Most of the random number generator functions work off of the computer clock and we're basically using that in order to generate the values for our salt. That leads to something like:

  
DECLARE @Salt VARCHAR(25);

-- Generate the salt
DECLARE @Seed int;
DECLARE @LCV tinyint;
DECLARE @CTime DATETIME;

SET @CTime = GETDATE();
SET @Seed = (DATEPART(hh, @Ctime) * 10000000) + (DATEPART(n, @CTime) * 100000) + (DATEPART(s, @CTime) * 1000) + DATEPART(ms, @CTime);
SET @LCV = 1;
SET @Salt = CHAR(ROUND((RAND(@Seed) * 94.0) + 32, 3));

WHILE (@LCV < 25)
BEGIN
   SET @Salt = @Salt + CHAR(ROUND((RAND() * 94.0) + 32, 3));
   SET @LCV = @LCV + 1;
END;

Note that I'm generating the seed value by shifting hour, minute, and second values over by powers of ten. Then I'm using the RAND() function to generate a text string of 25 characters. This will be our salt.

Putting It All Together

With the salt generated, it's a simple matter of concatenating the salt and the password, then submitting the combined string into HASHBYTES(). This results in a solution which will store both the salt and the salt+password hash:

CREATE DATABASE TestDB;
GO 

USE TestDB;
GO 

CREATE TABLE dbo.SecurityAccounts (
  AccountID INT IDENTITY(1,1),
  AccountName varchar(50),
  Salt CHAR(25),
  AccountPwd varbinary(20),
  CONSTRAINT PK_SecurityAccounts PRIMARY KEY (AccountID)
);
GO 

CREATE UNIQUE INDEX NDX_SecurityAccounts_AccountName 
    ON dbo.SecurityAccounts (AccountName) INCLUDE (Salt, AccountPwd);
GO 

CREATE PROC dbo.CreateAccount
  @NewAccountName VARCHAR(50),
  @NewAccountPwd VARCHAR(100)
AS
BEGIN

  SET NOCOUNT ON;

  DECLARE @Salt VARCHAR(25);
  DECLARE @PwdWithSalt VARCHAR(125);

  -- Generate the salt
  DECLARE @Seed int;
  DECLARE @LCV tinyint;
  DECLARE @CTime DATETIME;

  SET @CTime = GETDATE();
  SET @Seed = (DATEPART(hh, @Ctime) * 10000000) + (DATEPART(n, @CTime) * 100000)
      + (DATEPART(s, @CTime) * 1000) + DATEPART(ms, @CTime);
  SET @LCV = 1;
  SET @Salt = CHAR(ROUND((RAND(@Seed) * 94.0) + 32, 3));

  WHILE (@LCV < 25)
  BEGIN
    SET @Salt = @Salt + CHAR(ROUND((RAND() * 94.0) + 32, 3));
    SET @LCV = @LCV + 1;
  END;

  SET @PwdWithSalt = @Salt + @NewAccountPwd;

  INSERT INTO dbo.SecurityAccounts 
  (AccountName, Salt, AccountPwd)
  VALUES (@NewAccountName, @Salt, HASHBYTES('SHA1', @PwdWithSalt));
END;
GO

As for verification, we'll need to basically repeat the same steps, except we'll retrieve the stored salt from the database.

CREATE PROC dbo.VerifyAccount
  @AccountName VARCHAR(50),
  @AccountPwd VARCHAR(100)
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @Salt CHAR(25);
  DECLARE @PwdWithSalt VARCHAR(125);
  DECLARE @PwdHash VARBINARY(20);  

  SELECT @Salt = Salt, @PwdHash = AccountPwd 
  FROM dbo.SecurityAccounts WHERE AccountName = @AccountName;
  
  SET @PwdWithSalt = @Salt + @AccountPwd;

  IF (HASHBYTES('SHA1', @PwdWithSalt) = @PwdHash)
    RETURN 0;
  ELSE
    RETURN 1;

END;
GO

Testing the Solution

We can test it both with a relatively normal sized password and with the longest password allowed.

USE TestDB; 
GO 

EXEC dbo.CreateAccount @NewAccountName = 'Brian', @NewAccountPwd = 'Str0ngP4ssw0rd!';
GO 

SELECT * FROM dbo.SecurityAccounts;

DECLARE @Result TINYINT;

EXEC @Result = dbo.VerifyAccount @AccountName = 'Brian', @AccountPwd = 'Str0ngP4ssw0rd!';
SELECT @Result;

EXEC @Result = dbo.VerifyAccount @AccountName = 'Brian', @AccountPwd = 'WeakP4ssw0rd!';
SELECT @Result;
GO 

DECLARE @Password CHAR(100);
SET @Password = REPLICATE('A', 100);

EXEC dbo.CreateAccount @NewAccountName = 'John', @NewAccountPwd = @Password;
GO 

SELECT * FROM dbo.SecurityAccounts;
GO 

DECLARE @Result TINYINT;
DECLARE @Password CHAR(100);
SET @Password = REPLICATE('A', 100);

EXEC @Result = dbo.VerifyAccount @AccountName = 'Brian', @AccountPwd = @Password;
SELECT @Result;

EXEC @Result = dbo.VerifyAccount @AccountName = 'John', @AccountPwd = @Password;
SELECT @Result;
GO

If we get a zero on the return from the stored procedure, we have a match. With a value of 1, we don't. Therefore, if we just run the verification test all at once, we get:

We can test it both with a relatively normal sized password and with the longest password allowed.
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2014-07-25

Comments For This Article




Wednesday, January 30, 2019 - 2:43:47 AM - Thomas Franz Back To Top (78908)

I just use an UNIQUEIDENTIFIER as salt for our user table (ALTER TABLE dbo.users ADD salt UNIQUEIDENTIFIER NOT NULL DEFAULT NewID()), this way I don't have to bother with strange routines to create pseudo-random string values.

Friday, June 12, 2015 - 1:26:56 PM - Emma Back To Top (37908)

Great article and I can see a lot of use for this. How do I use this in in OPENROWSET if I want to pass the user name and password?

 

Thanks,

Emma

Friday, October 31, 2014 - 5:19:33 PM - Ray Back To Top (35142)

An interesting an informative article.  And a couple of informative comments also.

Just as a matter of simplification it is easy to treat the DateTime data type as a Float, Real, or Numeric which helps make the code less "busy" looking.

 

Declare @Now Datetime, @Salt Float;

Set @Now = GetDate();

Set @Salt = Cast (@Now as Float);

 You can get fancy with the expression to tailor the results.

E.g., Set @Salt = SQRT(Cast(@Now as Float) - Floor(Cast(@Now as Float));

I am not suggesting SQRT is the best or even a good choice.  Just showing how you could massage your Salt.

 

Friday, July 25, 2014 - 4:19:58 PM - TimothyAWiseman Back To Top (32891)

This is a great article, thank you for posting it.

I would like to add that doing security properly is rather difficult.  This is a great step for something which does not require high security, but it is worth having every step reviewed by a skilled security professional if security is important.  Arstechnica has an article about password cracking that also seems relevant here.

Friday, July 25, 2014 - 10:54:02 AM - SecurePasswords Back To Top (32884)

Jason Carter, please note that a salt is always stored with the password - it is not supposed to be a secret, it is merely there to prevent:

A)  one hash attempt from cracking multiple passwords

B) Pre-prepared hash tables from being useful without having already stolen the salts (which is why WPA/2 with default SSIDs fails, since the salt is the SSID, and default SSIDs are common and can therefore have precomputed tables - see Pyrit)

 

K. Brian Kelley, you've made a good start, but as is a single iteration of a salted hash is completely insufficient.  If you're curious about the speeds at which attackers operate, Google "oclhashcat". 

Note that as of mid-2014, a single PC with 8x AMD R9 290X GPU's can try more than 76 quadrillion (i.e. 76 million billion) single SHA-1 hashes, salted or not, every 30 days.  That's 7.65E16, or just over 2^56 tries every 30 days.

You are absolutely correct, you should do your hashing in your application with PBKDF2, BCrypt, or SCrypt. There are some sample implementations at https://github.com/Anti-weakpasswords

If you use PBKDF2 at all:

1) For password storage, do NOT use more output bits than the native hash size; the attacker only has to attack the first "set" and a success is a success, while the defender has to calculate at least one more than one "set", thus giving the attacker a larger advantage.  Native hash sizes are:

  • MD5 16 bytes (don't use this)
  • SHA-1 20 bytes (use this only if you're on SQL 2008R2 or lower using HASHBYTES)
  • SHA-224 28 bytes
  • SHA-256 32 bytes
  • SHA-384 48 bytes
  • SHA-512 64 bytes (preferred)

2) If you are on a 64-bit system, try to use HMAC-SHA-512, since computer CPU's currently do the 64-bit operations SHA-512 closer to the speed of the GPU's many attackers use in 2014, thus reducing the attacker's advantage at the moment.

3) Choose as high a number of iterations as your system can handle under peak load; note that T-SQL is horrifically slow even with the optimized code linked below

If you must do password hashing in SQL, there is at least one pure T-SQL PBKDF2 implementation:

https://stackoverflow.com/questions/7837547/is-there-a-sql-implementation-of-pbkdf2/19898192#19898192

And please read Thomas Porrin's answer to "How to securely store passwords", it's a very good primer.

https://security.stackexchange.com/questions/211/how-to-securely-hash-passwords/31846#31846

 

 

Friday, July 25, 2014 - 9:31:28 AM - Jason Carter Back To Top (32882)

While I understand that this is just a demo, storing the salt is obviously a terrible idea, unless that salt is another piece of data that would be there anyway, such as the email address.  Using the email address would create a unique salt value for each key, but then that is no good, as someone who has the database could easily figure out the proc and get all the password.

A third idea would be to combine both a user salt (email address)  with a second, system wide salt, which could be stored somewhere local to the system, such as the registry or a file on the disk even, but then again, those present their own issues as well.

Great article.














get free sql tips
agree to terms