For this week, here is a short post about reinventing the wheel.
An interesting conversation happened on Twitter where Dave Dustin asked:
“Does anybody have an example of using HASHBYTES() to replace PWDENCRYPT() per the documentation that the latter is deprecated?” – Dave Dustin
Dave is referring to the Microsoft Docs page for PWDENCRYPT(), which has been deprecated for some time. Unfortunately, although the recommended replacement is HASHBYTES(), there isn’t an example on either page of how to replicate the functionality of PWDENCRYPT().
So, borrowing from Sebastian Meine who wrote an article titled Hash Algorithms – How does SQL Server store Passwords?, this is how you can replicate the functionality of PWDENCRYPT() to create a login, using the HASHBYTES() function instead:
-- This is code originally written by Sebastian Meine
-- Source: https://sqlity.net/en/2460/sql-password-hash/
DECLARE @pwd NVARCHAR(MAX) = 'plaintext-password';
DECLARE @salt VARBINARY(4) = CRYPT_GEN_RANDOM(4);
DECLARE @hash VARBINARY(MAX);
SET @hash = 0x0200 + @salt + HASHBYTES('SHA2_512', CAST(@pwd AS VARBINARY(MAX)) + @salt);
SELECT @hash;
There you have it: a SHA-2 512-bit hash with a random salt, just like PWDENCRYPT() gives you.
Then again, even if a function is deprecated and you’re just trying to replicate what it’s already doing to automate the creation of hashes for a specific purpose, there’s probably no harm in using the deprecated function if you are aware of its limitations.
Share your thoughts in the comments below.