Encrypting and Decrypting passwords in SQL server

We have come across scenarios wherein highly sensitive information like passwords being stored in our database. It is a highly likely chance that during SQL Injection or unprecedented attacks, these table values gets exposed. I will explain here a simple means of encrypting, followed by decrypting the same password for user authentication. But let me remind you, that cracking this logic is easy. Also, if you are smart, you can write your own procedures to find the password from an encrypted on.

To Encypt a password, all you need to do is use the pwdencrypt(‘password’) method.

Check the example below:
Declare @x varbinary(255)
SET @x = pwdencrypt(‘AAAA’)
print @x

The output of the above query would be(will vary with you unique machine id):
0x010036D726AE86834E97F20B198ACD219D60B446AC5E48C54F30

However the decrypt is not as simple as encrypt. In fact there is no direct method to decrypt the password. Instead SQL Server provide a method to compare the password you passed with the encrypted password, using the pwdcompare(‘password passed’,encrypted password).

Check the example below:

select pwdcompare(‘AAAA’,0x01008C75C2236E7101F87898988A5AEEE995D4CDFD16A8C95ECD)

This query will return 1 or 0 depending on the equality of the password.