Using Encryption to Store Sensitive Data

SQL Server, even the earlier version, has a significant number of ways to protect data from unauthorized users (ie. Views, functions, user rights, etc). However, administrators and database owners can still view table contents that might be too sensitive for anyone to see, like password stored in tables that are frequently used by UIs and other front-end apps.

SQL Server 2005 has an undocumented system function that can be use to encrypt a string to store encrypted information. It should be noted, however, that the function is a one way hash encryption. It means you can not decrypt the stored data. The only way to use it is to compare it with an encrypted string.

declare @string varchar(50), @EncryptedString varbinary(max)

set @string = 'correctpassword'
set @EncryptedString = pwdencrypt(@string)

select
pwdcompare('correctpassword',@EncryptedString ) correctpass, 
  pwdcompare('wrongpassword',@EncryptedString ) wongpass

Here's the result set:

correctpass wongpass
----------- -----------
1           0  

This function is useful if you don't need to display the encrypted data later. If you need the decrypted data, later (like encrypting date of birth data then you'll need to display it later),it would be better to use a customized encryption function.



~~CK