T-SQL: Salted Passwords

Question

I am looking for an example of salting passwords withing a T-SQL Stored Procedure. And of course the matching proc to validate a user.

CREATE PROC ChangePassword(@Username nVarChar(50), @Password nVarChar(50))

CREATE PROC ValidateUser(@Username nVarChar(50), @Password nVarChar(50))

Solution 1

First, I'm going to go out on a limb here and say that hashing passwords in the database is in general a bad practice with respect to security. You would not be protected against traffic sniffers watching traffic to the database. The only way to protect against that is to ensure your connection to the database was encrypted which generally means all other traffic to the database is going to be encrypted. It's possible to work around this, but the better solution is to have the application(s) do the hashing.

As Sam Saffron stated, you can use the Hashbytes functions to get SHA1 hashing. If you want better algorithms you would need to create a CLR procedure. Salting would involve storing a cryptographically random value for each user, then appending that value to the password and running it through Hashbytes:

Create Procedure ValidateUser
    @Username nvarchar(50)
    , @Password nvarchar(50)
As

Declare @PasswordSalt varbinary(256)

Set @PasswordSalt = ( Select PasswordSalt From Users Where Username = @Username )

If @PasswordSalt Is Null
        -- generate a salt? 

Declare @Hash varbinary(max)
Set @Hash = Hashbytes('SHA1', @PasswordSalt + Cast('|' As binary(1)) + Cast(@Password As varbinary(100))

If Exists(  Select 1
            From Users
            Where Username = @Username
                And PasswordHash = @Hash )
    -- user is valid

Else
    -- user is not valid

Remember that the salt should be cryptographically random so I would not recommend using NewId(). Instead, I would generate that using something like .NET's RNGCryptoServiceProvider class.

Solution 2

You can use HASHBYTES to SHA1 a string, and NEWID() to generate a random Guid as salt.

Solution 3

have you considered salting passswords at the application level as.the server hardware for app servers esp. Cpu might have been more suitable than the dbms's to process hashing and salting?

Comments

[Sam Saffron]

That said, in general I prefer BCrypt these days, there is much about it on SO here: stackoverflow.com/questions/1561174/… and stackoverflow.com/questions/3722780/…

[Aaronaught]

Although I don't think you can do bcrypt in T-SQL (not that your security code should live in T-SQL anyway...)

[Aaronaught]

I'm also pretty sure that newid is not cryptographically strong. Neither is MD5 or SHA1, but, everything in degrees...

[Sam Saffron]

@Aaronaught, true, this does seem like the wrong place for this code, you could bcrypt using sql clr coldfusion.tcs.de/… or an extended proc, but really this is the wrong spot

[Sam Saffron]

@Aaronaught, strong enough to defeat a rainbow table :) trouble with and salted sha1 is that you can brute force it.

[Thomas]

@Sam Saffron - Wouldn't doing your hashing in the db (whether via T-SQL or the CLR) require that you encrypt communication to the db in order to prevent sniffing of the traffic?

[Sam Saffron]

@Thomas, sure but the same goes for anything sensitive you are storing in the db un-encrypted. Personally, I would not put this kind of code on my db server it feels like its in the wrong spot

[Sam Saffron]

Thomas, I thought the reason for the salt was to defeat rainbow tables. What is the rational for making it cryptographically secure?

[Thomas]

@Sam Saffron - Not so much cryptographically secure as cryptographically random. The randomness reduces clustering of the salt values.

[Sam Saffron]

@Thomas ... sorry don't mean to be a pain here what effect does clustering the salt have?

[Thomas]

@Sam Saffron - It increases the odds of deriving the salts. Granted, to make a difference, we're talking about a persistent hacker and lots of passwords and given we are talking about the potential hole of sending the pwd in plaintext to the db to hash, using NewId() would probably be good enough. In general, I do not assume that NewId() is really random but in this case, it might be considered random enough.

[Jonathan Allen]

I don't see any problem with having one connection string for secure operations and a second for bulk data transfer. Plus this let's me keep the secrets off the web servers.

[Jonathan Allen]

This was mostly curiosity. That said, this allows multiple applications without the need to build some sort of middle ware.

[Thomas]

@Jonathan Allen - If you are hashing passwords, there are no secrets on the web server. The hash algorithm isn't considered a secret. RE: Connections. I'm not even sure you can tell SQL Server to encrypt some traffic and not others. AFAIK, it is all or nothing.

[Thomas]

@Sam Saffron - When you hash the user's password on the web server, the only thing stored in the db is the hash (and the unencrypted salt). Thus, if someone steals the db, they only have hashes and salts. However, if you do the hashing in a stored proc, you have to send the password in plain text to the db to hash. That traffic can be easily sniffed (e.g. SQL Profiler). The pwd is protected in the db by virtue of being hashed. However the traffic to the db is (by default) not protected.

[Jonathan Allen]

Damn, you're right. I was really expecting to be able to control whether or not I'm using SSL on a per connection basis, but it really is all or nothing. technet.microsoft.com/en-us/library/ms177485.aspx

[Sam Saffron]

@Thomas I totally accept that hashing passwords on the db is a can of worms not worth opening