T-SQL: salty passwords

Question

I am looking for an example of salting passwords with a T-SQL stored procedure. And, of course, the appropriate proc to validate the user.

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

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

Answer 1

First, I'm going to go limb here and say that hashing passwords in a database is usually bad security practice. You will not be protected from traffic by sniffers by monitoring traffic to the database. The only way to protect this is to encrypt your connection to the database, which usually means that all other traffic to the database will be encrypted. You can get around this, but the best solution is a hash application.

As Sam Shaffron said, you can use the Hashbytes functions to get SHA1 hashes. If you need better algorithms, you need to create a CLR procedure. Salting will include storing a cryptographically random value for each user, and then adding this value to the password and launching 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 salt must be cryptographically random, so I would not recommend using NewId (). Instead, I would generate this using something like .NET RNGCryptoServiceProvider.

Answer 2

You can use HASHBYTES for the SHA1 string and NEWID () to generate a random guide like salt.

Answer 3

You thought that salt transmitters at the application level are the server for application servers, especially. Perhaps the processor was more suitable than dbms for hashing and salting?