Home > Security, SQL Server > SQL SERVER – Password Encryption using PwdEncrypt() and PwdCompare() functions @SQLSERVER

SQL SERVER – Password Encryption using PwdEncrypt() and PwdCompare() functions @SQLSERVER

Application might need to store user login information in SQL Server database. of course, we can easily store it in a table with varchar column. But, any authorized user can view this information and may get exposed to others too by info leak.

Keep the information secure (inaccessible) is one way to protect organization data. Another way is to encrypt your data. There are many encryption algorithm available in the market and SQL Server has heavy security feature. For instance, you can encrypt entire database or you can protect specific object and etc.

In this article I will be showing you the use of two function that helps you save a sensitive information (password) in to a table.

Functions are:

PWDENCRYPT(Plain text) – It encrypt the plain text and returns varbinary(128) hash value using hash algorithm

PWDCOMPARE(Plain text, hash value) – It compares the plain text with the hash equivalent value and returns 1 if plain text matches hash value or Zero (0) for not match string.


Select PWDENCRYPT(‘Ayyappan’)

select PWDCOMPARE(‘Ayyappan’, PWDENCRYPT(‘Ayyappan’))

Additional information

These two functions may be deprecate in future release. But, I have tested my code in SQL Server 2012 rc0. It works fine.

SQL Server team has introduce and function to generate hash value for a given plain text with different algorithm.

Algorithms available for this function are MD2, MD4, MD5, SHA, or SHA1.

HashBytes ( '<algorithm>', { @input | 'input' } )

<algorithm>::= MD2 | MD4 | MD5 | SHA | SHA1


Convert Hash value

Select HashBytes ( ‘MD5’, ‘SQLServerRider’)

Compare Hash value

IF hashBytes ( ‘MD5’, ‘SQLServerRider’) = hashBytes ( ‘MD5’, ‘SQLServerRider’)  Print ‘Equal’ else  print ‘Not Equal’

Take care of your data.

Thanks for reading.

Categories: Security, SQL Server
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

%d bloggers like this: