CODEWITHSUNDEEP
sql-serverencryptionencodingpasswordssha1
Atzi
Atzi

Reputation: 515

SQL Server - Password Field Encoding

First Question :

Which One is better to encode password ?

  1. SHA1

HashBytes('SHA1','MySecret Phrase')

  1. Encrypt

EncryptByPassPhrase('secretKey', '123456789')

Is there any better way that is more safe ?

And other question is :

Can we encode password for more than 1 time ? for example two times encoding ?

Upvotes: 0

Views: 469

Answers (1)

Roger Wolf
Roger Wolf

Reputation: 7702

  1. Neither. Hash without a salt is vulnerable to attack by rainbow tables. Passphrase, on the other hand, has to be submitted in clear text, which makes it vulnerable to SQL trace.

  2. I have recently studied this particular question for my current project and eventually settled down on using salted X.509 signatures. You create a certificate in your database and use its private key for creating password signatures (+ personalised GUID salt). The resulting hash cannot be reverted back to the original password, even when the salt is stored as plain text.

Basically, the code looks like this:

create function [dbo].[security_GetPasswordHash]
(
    @Password sysname,
    @Salt uniqueidentifier
)

returns binary(128) with schemabinding, returns null on null input as begin

declare @CertId int, @CertPwd sysname;

set @CertId = ...; -- Get your cert however you like it
set @CertPwd = ...; -- If your cert is encrypted with password, get it too

return SignByCert(
    @CertId,
    SignByCert(@CertId, @Password, @CertPwd) + cast(@Salt as binary(16)),
    @CertPwd
);
end;
go

This way, even the knowledge of a private key' password will not help an attacker to reverse the hash. And in your authentication stored procedure, you may use code like this to determine whether the password is correct:

-- Try to validate the user
select @UserId = u.Id
from dbo.Users u
where u.LoginName = @Login
    and u.PasswordHash = dbo.security_GetPasswordHash(@Password, u.PasswordSalt);

-- Special case of user existence - there may be a wrong password here, too.
if @UserId is null begin
    -- The specified user either does not exist, or wrong password has been supplied.
    set @Error = 51008;
    set @Message = dbo.sys_FormatErrorMessage(@Error, @CultureId, default, default, default, default);
    throw 50000, @Message, 1;
end;

And despite all warnings about expensive computations when using certificates, this algorithm works sufficiently fast even on a 2-year old laptop.

Upvotes: 1

Related Questions