approaches to encrypt password in sql server 2005

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('SecureSymmetri开发者_运维百科cKey1'),@Password)

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

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

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.