4

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...

ACP
  • 34,682
  • 100
  • 231
  • 371

2 Answers2

3

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

Community
  • 1
  • 1
kevchadders
  • 8,335
  • 4
  • 42
  • 61
2

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.

Grzegorz Gierlik
  • 11,112
  • 4
  • 47
  • 55