PWDENCRYPT & PWDCOMPARE are two undocumented methods provided by MS SQL Server. PWDENCRYPT generates a hash given a string, and PWDCOMPARE is used to Compare a string with generated hash.

Example:

DECLARE @varPassword NVARCHAR(128)
SELECT @varPassword = ‘Test Encryption’

DECLARE @passwordHash VARBINARY(128)
SELECT @passwordHash = PWDENCRYPT(@varPassword)

PRINT ‘Password: ’ + @varPassword
PRINT ‘Password Hash: ’ + CAST(@passwordHash AS NVARCHAR(128))

DECLARE @chkPassword NVARCHAR(128)
SELECT @chkPassword = ‘Test Encryption’

IF (PWDCOMPARE(@chkPassword, @passwordHash) = 1)
BEGIN
PRINT ‘Match found’
END
ELSE
PRINT ‘Match not found’

These methods work great as far as you are not expecting too much out of them. Let me put down some pros and cons which will help you make your choice regarding whether you should use these methods or not.

Pros:

  • Easy to use
  • Encryption/Decryption handled directly at SQL level
  • Easy to make bulk changes using queries
  • In built methods

Cons:

  • Case insensitive (now that’s a great problem if you are planning to use it for generating passwords)
  • Altering the collation settings does not help make it work consistently
  • Undocumented and hence not supported by Microsoft

Summary:

If you are in any way using PWDENCRYPT & PWDCOMPARE, you might want to go back and check for inconsistencies. I would personally recommend handling the encryption at Code level along with case sensitive collation on the SQL side for storing the encrypted values.