approaches to encrypt password in sql server 2005

Question

Hai guys,

Thus far i am using the following statements for encrypting a password variable in sql server 2005

OPEN SYMMETRIC KEY SecureSymmetricKey1
DECRYPTION BY PASSWORD = N'StrongPassword';

 DECLARE @encrypted_str VARBINARY(MAX)
select @encrypted_str=EncryptByKey(Key_GUID('SecureSymmetricKey1'),@Password)

Is this a good practice or any other approach for doing this...

Answer 1

If you mean your application user password it would be much easier (and probably good enough) to just hash and salt the user password.

There are a few reasons:

• Hashing password is common practice/standard.
• Password should not be recoverable from database (even with access to database it's hard to recover the password).
• Database is not a calculator -- it's storing engine (advanced engine, but for storing data, not calculating them).

In SQL Server 2005 there is a function HashBytes is available. Don't forget to salt password before hash.

Exemplary code using HashBytes could look like this:

DECLARE 
    @password nvarchar(100),
    @salt AS nvarchar(100)

SET @salt = 'various random characters i.e. #_$a1b'
SET @password = 'my password'

SELECT HashBytes('SHA1', @salt + @password)

However, probably, it's much easier to make hash directly in application and only save hashed password to database.

Answer 2

You may find this post on preferred-method-of-storing-passwords-in-database in Stackoverflow useful as well