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.

Feel free to leave a reply here...

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s