CODEWITHSUNDEEP
sqlsql-server-2005encryptionpasswords
ACP
ACP

Reputation: 35268

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('SecureSymmetricKey1'),@Password)

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

Upvotes: 4

Views: 3295

Answers (2)

Grzegorz Gierlik
Grzegorz Gierlik

Reputation: 11232

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.

Upvotes: 2

kevchadders
kevchadders

Reputation: 8335

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

Upvotes: 3

Related Questions