In this post I talk some more about SQL Server 2025’s new PBKDF2 and demo a method to replicate it using T-SQL.
Table of contents
Intro
Back in June I wrote a post about SQL Server 2025’s new PBKDF2 hashing algorithm, in which I’ve mentioned that “I don’t yet have way to build the new PBKDF2 password hash from parts”.
So, almost 5 months later, after some more reading and a decent amount of trial and error, I finally have it.
SQL Server 2025’s PBKDF2 hash structure
The password hash you see in sys.sql_logins‘ password_hash column is a 70‑byte binary comprised of:
- 0x0300 (hash version marker for SQL Server 2025) .
- 4‑byte salt (generated with
CRYPT_GEN_RANDOM(4)). - 64‑byte PBKDF2 HMAC‑SHA‑512 derived key that is the result of multiple SHA‑512 HMAC iterations.
Performance considerations
Touching on this before going further:
Pure T‑SQL PBKDF2 is slow (each iteration runs a full SHA‑512 HMAC).
And, while the host’s single-core performance has a big impact in the execution time (more on that later), I do not recommend actually using this outside of testing purposes even with the best CPU available.
For production purposes, you’d typically use the built‑in CREATE LOGIN command, or PWDENCRYPT if you need to generate PBKDF2 hashes outside of the ones used for SQL logins.
On pre-2025 versions of SQL Server you could resort to a CLR-based implementation.
This is just a PoC meant for learning and testing. And, while it allows you to create the SQL Server 2025 version of the hash on older versions of SQL Server, I don’t see why you’d want actually to do that.
Objects involved
Note: I generally try to lump all the logic into a single procedure. But, in this case, I opted to split the logic between a main stored procedure and 2 helper scalar functions.
This helps make things easier to read and manage. Especially for me, since I’m not anywhere near a cryptography expert.
The DDL for the objects used to replicate SQL Server 2025’s PBKDF2 in T-SQL can be found in my blog’s GitHub repo.
sp_Pbkdf2HashMssql2025
A stored procedure that acts as the main entry point of the clear text password.
It calls the helper UDFs multiple times, with each iteration building on the results of the previous one, and eventually returns the SQL Server 2025-equivalent PBKDF2 hash.
Parameters
| Parameter | Description |
|---|---|
@ClearTextPassword | The password the user typed (max 128 Unicode characters) and that should be hashed. |
@Iterations | How many times the algorithm should repeat (defaults to 100,000 based on the iterations count specified in this MS blog post). |
@OutHash (output) | This is the output returned by the procedure. It should have the same structure as the password hashes stored in sys.sql_logins |
Steps
Note that the first 2 steps are the same as the perquisites for the pre-2025 hashing algorithm.
- Create a random salt.
This is a random piece of data (4 bytes in this case) that is mixed into the hash.
It’s done so that two different users with the same password get a different hash, and it defeats pre‑computed rainbow‑table attacks. - Turn the password into raw bytes by casting it to VARBINARY.
This is done because the subsequent operations cannot be done a string, but on the binary representation of said string - Set up the PBKDF2 block index (
@BlockIdx).
This is 1 represented as a 4 byte big-endian binary value. And it’s needed for the initial HMAC (U1). - First call to
fn_HmacSha512.SET @U = [dbo].[fn_HmacSha512](@PasswordBin, @Salt + @BlockIdx);
This is where the real cryptographic work starts and is the initial HMAC (U1).
Thefn_HmacSha512function mixes the password (the “key”) with the message (salt + block‑index) and runs SHA‑512. - Initialize the accumulator (
@T).
PBKDF2 defines a variable T that will hold the XOR of all the HMAC results.
We start by setting T = U1 - Repeat the HMAC many times (the loop).
The loop runs exactly the number of times (c) specified via@Iterations(e.g. 100,000).
Each pass making the derived key exponentially harder to reverse.
For each remaining iteration (from 2 up to@Iterations):- Re‑hash the previous HMAC: Ui = HMAC‑SHA‑512( password‑bytes , Ui-1 )
SET @U = [dbo].[fn_HmacSha512](@PasswordBin, @U); - XOR the new Ui into the accumulator: T = T XOR Ui (byte by byte).
SET @T = [dbo].[fn_XorVarbinary](@T, @U);
- Re‑hash the previous HMAC: Ui = HMAC‑SHA‑512( password‑bytes , Ui-1 )
- At the end of the loop,
@Tis the derived key and it now contains the XOR of all the HMAC outputs (U1 XOR U2 XOR …. Uc).
It is a 64‑byte block representing the cryptographic core of the login hash. - Assemble the final 70‑byte login hash in the format that SQL Server 2025 expects (version marker + salt + derived key) and return it as an output.
The code
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 | SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON; GO IF ( OBJECT_ID(N'dbo.sp_Pbkdf2HashMssql2025', N'P') IS NOT NULL ) BEGIN DROP PROCEDURE [dbo].[sp_Pbkdf2HashMssql2025]; END; GO CREATE PROCEDURE [dbo].[sp_Pbkdf2HashMssql2025] (@ClearTextPassword NVARCHAR(128), /* clear text password */ @Iterations INT = 100000, /* number of hashing iterations */ @OutHash VARBINARY(70) OUTPUT /* password hash */ ) /* Returns the 70‑byte login hash used by SQL Server 2025: 0x0300 | 4‑byte salt | 64‑byte PBKDF2‑HMAC‑SHA‑512 derived key */ AS SET ANSI_PADDING ON; SET ANSI_WARNINGS ON; SET ARITHABORT ON; SET CONCAT_NULL_YIELDS_NULL ON; SET NUMERIC_ROUNDABORT OFF; SET NOCOUNT ON; BEGIN DECLARE @BlockIdx BINARY(4) = 0x00000001, /* block index = 1 (big‑endian) */ @U VARBINARY(64), /* holds the latest HMAC output */ @T VARBINARY(64) = 0x, /* accumulator (the result of the XOR operation)*/ @i INT = 1, /* starts with i ends in teration */ @PasswordBin VARBINARY(256), @Salt VARBINARY(4) = CRYPT_GEN_RANDOM(4); /* 4 byte random salt just like in the pre-2025 versions of SQL Server*/ /* first we need the password to be converted to varbinary(256) (max 256 bytes because NVARCHAR(128) = 256 bytes) */ SET @PasswordBin = CAST(@ClearTextPassword AS VARBINARY(256)); /* initial HMAC (U1) copy-pasta from Wikipedia: "U1 = PRF(Password, Salt + INT_32_BE(i)) The first iteration of PRF uses Password as the PRF key and Salt concatenated with i encoded as a big-endian 32-bit integer as the input. (Note that i is a 1-based index.) "*/ SET @U = [dbo].[fn_HmacSha512](@PasswordBin, @Salt + @BlockIdx); SET @T = @U; /* subsequent HMACs U2 = PRF(Password, U1) ...... Uc = PRF(Password, Uc-1) where c = @Iterations */ WHILE @i < @Iterations BEGIN SET @U = [dbo].[fn_HmacSha512](@PasswordBin, @U); SET @T = [dbo].[fn_XorVarbinary](@T, @U); SET @i += 1; END; /* when done iterating, build the resulting hash: hash version + salt + derived key */ SET @OutHash = 0x0300 + @Salt + @T; END; GO |
fn_HmacSha512
A scalar UDF that implements the standard HMAC construction using the SHA‑512 hash algorithm.
It takes a password (in this case the contents of the @PasswordBin variable) and a message (the contents of the previously populated @U variable).
Pads the key to a 128‑byte block, XORs it with the standard inner/outer constants, hashes the inner combination, then hashes the outer combination.
And finally returns the 64‑byte HMAC‑SHA‑512 digest that PBKDF2 relies on.
Parameters
| Parameter | Description |
|---|---|
@Key | The secret key. In this case the password (already turned into a binary string). |
@Msg | The message we want to protect. For PBKDF2 this is either salt + block‑index or the previous HMAC output. |
Steps
- Make sure the key fits the SHA‑512 block size.
If the key is longer than 128 bytes it’s hashed once as per RFC 2104.
This is done to ensure that the@Keyis <= 128 bytes. - Pad the (now‑short) key to a full block.
HMAC works with a full‑size block (128 bytes for SHA‑512). So, if the key is shorter, we pad the right side with zero bytes until it reaches exactly 128 bytes.
This padded key (@KeyPadded) is what we’ll XOR with the inner and outer pad constants. - Build the inner padding (
@InnerPadding) and outer padding (@OuterPadding)
HMAC uses two fixed 1‑byte constants: 0x36 for the inner pad and 0x5C for the outer pad.
Since, in SQL Server, XOR cannot be applied between twoVARBINARYvalues, it first converts that single‑byte binary toTINYINTso that^can be applied.
It loops 128 times, constructing the two 128‑byte binary strings stored in@InnerPaddingand@OuterPadding. - Perform the inner hash.
Concatenates@InnerPaddingwith the supplied@Msgand calls HASHBYTES.
HASHBYTES(N'SHA2_512', @InnerPadding + @Msg)
The result is a SHA‑512 digest. - And the outer hash.
Takes the@OuterPadding, appends the inner hash, and hashes the whole thing.HASHBYTES(N'SHA2_512', @OuterPadding + HASHBYTES(N'SHA2_512', @InnerPadding + @Msg)); - The resulting 64-byte HMAC is then returned and stored in the
@Uvariable of the sp_Pbkdf2HashMssql2025 procedure.
The code
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 | SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON; GO IF ( OBJECT_ID(N'dbo.fn_HmacSha512', N'FN') IS NOT NULL ) BEGIN DROP FUNCTION [dbo].[fn_HmacSha512]; END; GO CREATE FUNCTION [dbo].[fn_HmacSha512] (@Key VARBINARY(256), /* password (max 256 bytes after conversion) */ @Msg VARBINARY(64) /* salt+counter or previous @U value */ ) RETURNS VARBINARY(64) WITH SCHEMABINDING /* returns VARBINARY(64) = HMAC‑SHA‑512(key, message) */ AS BEGIN DECLARE @BlockSize INT = 128, /* SHA‑512 block size */ @KeyPadded VARBINARY(128), @InnerPadding VARBINARY(128) = 0x, @OuterPadding VARBINARY(128) = 0x, @i INT = 1; /* if the key is longer than a block, hash it first (as per RFC 2104) */ IF ( DATALENGTH(@Key) > @BlockSize ) BEGIN SET @Key = HASHBYTES(N'SHA2_512', @Key); END; /* pad the key to exactly one block (append zeros) */ SET @KeyPadded = @Key + CAST(REPLICATE(0x00, @BlockSize - DATALENGTH(@Key)) AS VARBINARY); WHILE @i <= @BlockSize /* build @InnerPadding and @OuterPadding by XOR‑ing each byte of the padded key with 0x36 (@InnerPadding) and 0x5C (@OuterPadding) */ BEGIN SET @InnerPadding += CAST(CAST(SUBSTRING(@KeyPadded, @i, 1) AS TINYINT) ^ 0x36 AS BINARY(1)); SET @OuterPadding += CAST(CAST(SUBSTRING(@KeyPadded, @i, 1) AS TINYINT) ^ 0x5C AS BINARY(1)); SET @i += 1; END; /* HMAC = H( outer padding + H( inner padding + message ) ) */ RETURN HASHBYTES(N'SHA2_512', @OuterPadding + HASHBYTES(N'SHA2_512', @InnerPadding + @Msg)); END; GO |
fn_XorVarbinary
A scalar UDF that takes two binary values (the contents of the @T variable from the previous iteration and the @U variable from the current iteration), walks through them byte‑by‑byte, XORs each pair of bytes (padding the shorter value with zeros), and returns the combined binary result.
This makes it possible to perform the XOR step required by PBKDF2 entirely within T‑SQL.
Parameters
| Parameter | Description |
|---|---|
@T | The contents of the @T variable passed by sp_Pbkdf2HashMssql2025. |
@U | The contents of the @U variable passed by sp_Pbkdf2HashMssql2025. |
Steps
- Finds out how many bytes each input actually contains.
- Chooses the longer of the two lengths.
The XOR result must be as long as the longer operand. - Loop over every position.
This walks through the binary data one byte at a time. - Extract the current byte from each operand.
If we’ve run past the end of a shorter operand we substitute a zero byte (in theCASEstatement), which leaves the other operand’s byte unchanged after the XOR. - The actual XOR of the two bytes that gets appended to the
@Result. - Returns
@Resultand passes its contents to the@Tvariable of the sp_Pbkdf2HashMssql2025 procedure.
The code
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 | SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON; GO IF ( OBJECT_ID(N'dbo.fn_XorVarbinary', N'FN') IS NOT NULL ) BEGIN DROP FUNCTION [dbo].[fn_XorVarbinary]; END; GO CREATE FUNCTION [dbo].[fn_XorVarbinary] (@T VARBINARY(64), @U VARBINARY(64)) RETURNS VARBINARY(64) WITH SCHEMABINDING /* byte‑wise XOR of two varbinary values */ AS BEGIN; DECLARE @LenT INT = DATALENGTH(@T), @LenU INT = DATALENGTH(@U), @MaxLen INT, @i INT = 1, @Result VARBINARY(64) = 0x, @ByteT TINYINT, @ByteU TINYINT; SET @MaxLen = CASE WHEN @LenT > @LenU THEN @LenT ELSE @LenU END; WHILE @i <= @MaxLen /* pull the @i‑th byte from each operand, if the operand is shorter, treat the missing byte as 0 (zero‑padding on the right) */ BEGIN SET @ByteT = CASE WHEN @i <= @LenT THEN CAST(SUBSTRING(@T, @i, 1) AS TINYINT) ELSE 0 END; SET @ByteU = CASE WHEN @i <= @LenU THEN CAST(SUBSTRING(@U, @i, 1) AS TINYINT) ELSE 0 END; /* XOR the two bytes and append to @result. */ SET @Result += CAST(@ByteT ^ @ByteU AS BINARY(1)); SET @i += 1; END; RETURN @Result; END; GO |
Generating a SQL Server 2025-like PBKDF2 hash using sp_Pbkdf2HashMssql2025
To generate a SQL Server 2025-like PBKDF2 using sp_Pbkdf2HashMssql2025’s T-SQL implementation, just run the following after creating the 3 objects:
| 1 2 3 4 5 6 7 8 9 | DECLARE @Hash VARBINARY(70); EXEC [dbo].[sp_Pbkdf2HashMssql2025] @ClearTextPassword = N'$up3R-S3Cur3P@22', @Iterations = 100000, @OutHash = @Hash OUTPUT; SELECT DATALENGTH(@Hash) AS [length_in_bytes], CONVERT(NVARCHAR(256), @Hash, 1) AS [hashed_password]; |
Performance
Note, I’m not running SET STATISTICS TIME ON because that will end up returning time statistics for every loop.
I’ll be using the old-school method of just looking at the execution duration in SSMS :).
The duration is consistently 100 seconds (+/-1 second) on both my VM-based SQL Server 2025 Preview instance as well as my container-based one.
The execution time drops down 37 seconds on my PC where single-core speeds are higher (the CPU is an AMD Ryzen 9 5950X).
For comparison’s sake, using PWDENCRYPT to generate the same version of the hash in SQL Server 2025 takes 150 milliseconds on the VM and container-based instance, and 0ms on my PC.
| 1 2 3 4 | SET STATISTICS TIME ON; GO SELECT PWDENCRYPT(N'$up3R-S3Cur3P@22') AS [hashed_password]; GO |
(1 row affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 150 ms.
Update:
The eagle-eyed Sean Bloch pointed out in a LinkedIn comment that I’ve defined the input variables in fn_XorVarbinary’s as VARBINARY(MAX), which was correct since I’ve missed updating them after my “just get this working first” faze.
Addressing that as well as switching the @Msg variable in fn_HmacSha512 to VARBINARY(64) instead of 256, improved the execution times as follows:
- VM and container – 67 seconds
- PC – 28 seconds
Adding WITH SCHEMABINDING to the two functions brought an additional drop in execution times:
- VM and container – 65 seconds
- PC – 27 seconds
Testing the resulting PBKDF2 hash
So far, so good, but all this is pointless if the resulting PBKDF2 isn’t valid.
To make sure that the resulting hash is actually a valid SQL Server 2025 PBKDF2 hash, I’ll run the following two tests.
Testing the PBKDF2 hash with PWDCOMPARE
Similar to what I do in my Cracking SQL Server login passwords online post, I use PWDCOMPARE to check the resulting PBKDF2 hash against the plain text password used to generate the hash.
| 1 2 3 4 5 6 7 8 9 | /* Generate a hash for a sample password */ DECLARE @Hash VARBINARY(70); EXEC [dbo].[sp_Pbkdf2HashMssql2025] @ClearTextPassword = N'$up3R-S3Cur3P@22', @Iterations = 100000, @OutHash = @Hash OUTPUT; /* compare using PWDCOMPARE */ SELECT PWDCOMPARE(N'$up3R-S3Cur3P@22', @Hash) AS [password_match]; |
And the generated PBKDF2 hash matches the password when compared via PWDCOMPARE.
Double-checking with a 128 character password to make sure there’s no silent truncation going on.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 | DECLARE @Hash VARBINARY(70), @LongPass NVARCHAR(128), @PassBase NVARCHAR(128) = N'$up3R-S3Cur3P@22'; SET @LongPass = @PassBase + REPLICATE(N'a', 128 - LEN(@PassBase)); EXEC [dbo].[sp_Pbkdf2HashMssql2025] @ClearTextPassword = @LongPass, @Iterations = 100000, @OutHash = @Hash OUTPUT; SELECT @LongPass AS [that_long_password], PWDCOMPARE(@LongPass, @Hash) AS [password_match]; |
Bonus: what happens if I use another value for @Iterations
I’ll be honest, I initially expected to have to mess around with diferent values for the @Iterations paramter.
I was half expecting the folks over at Microsoft to mention 100k more as a rounded down value, but the actual one to be slightly higher than that.
So, when I saw that 100k just works I decided to do a soundness check.
For this, I ran another test but with @Iterations set to 100001.
| 1 2 3 4 5 6 | DECLARE @Hash VARBINARY(70); EXEC [dbo].[sp_Pbkdf2HashMssql2025] @ClearTextPassword = N'$up3R-S3Cur3P@22', @Iterations = 100001, /*yup, it's just 100k+1*/ @OutHash = @Hash OUTPUT; SELECT PWDCOMPARE(N'$up3R-S3Cur3P@22', @Hash) AS [password_match]; |
But PWDCOMPARE returned false this time.
Meaning that 100k is indeed the correct number of iterations and everything was working as expected.
Using the resulting PBKDF2 hash as a SQL login’s password hash in SQL Server 2025
I considered this as being the ultimate test.
If a SQL login that uses the PBKDF2 hash generated with sp_Pbkdf2HashMssql2025 can be used to actually log into a SQL Server 2025 instance, then it means that I really did nail this.
First, I create a new login on my SQL Server 2025 Preview instance.
| 1 2 3 4 | USE [master] GO CREATE LOGIN [test_login] WITH PASSWORD = N'T3$t-pAs2'; GO |
Then I use a similar approach to the one in my migrating sa’s password post to update test_login’s password hash with one generated through sp_Pbkdf2HashMssql2025.
And yes, I am using that 128 character password for this test too.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | DECLARE @Hash VARBINARY(70), @LongPass NVARCHAR(128), @PassBase NVARCHAR(128) = N'$up3R-S3Cur3P@22', @SQL NVARCHAR(800), @LineFeed NVARCHAR(5) = CHAR(13) + CHAR(10); SET @LongPass = @PassBase + REPLICATE(N'a', 128 - LEN(@PassBase)); EXEC [dbo].[sp_Pbkdf2HashMssql2025] @ClearTextPassword = @LongPass, @Iterations = 100000, @OutHash = @Hash OUTPUT; SELECT @SQL = N'ALTER LOGIN ' + QUOTENAME([name]) + N' WITH CHECK_POLICY= OFF,' + @LineFeed + N' PASSWORD = ' + CONVERT(NVARCHAR(256), @Hash, 1) + N' HASHED;' + @LineFeed + N'GO' + @LineFeed + CASE WHEN [is_policy_checked] = 1 THEN N'ALTER LOGIN ' + QUOTENAME([name]) + N' WITH CHECK_POLICY= ON;' + @LineFeed + N'GO' ELSE N'' END FROM sys.[sql_logins] WHERE [name] = N'test_login'; PRINT @SQL; |
And this is the resulting T-SQL which paste and execute in another query editor tab.
| 1 2 3 4 5 | ALTER LOGIN [test_login] WITH CHECK_POLICY= OFF, PASSWORD = 0x0300BA50074C12A34FDA30314CFCB9D93646790392422E420ABB30A03F7A3EAB0A27E9658D988F46BA95C95D9E182F99CC0FDC9B3CF5F072F0E770488335202CFB5C24DEBB69 HASHED; GO ALTER LOGIN [test_login] WITH CHECK_POLICY= ON; GO |
So… can I now use the long password to connect to the SQL Server instance?
I open a PowerShell window and connect via sqlcmd.
| 1 2 3 4 | $BasePass = '$up3R-S3Cur3P@22' $BaseLen = $BasePass.Length $LongPass = $BasePass + $('a' * (128 - $BaseLen)) sqlcmd -S nas1,1433 -U test_login -P $LongPass |
And I can authenticate successfully with test_login and the password whose hash was updated via sp_Pbkdf2HashMssql2025.
Conclusion
SQL Server 2025’s PBKDF2 hashing algorithm works and then applying it in a pure T-SQL implementation was a very interesting exercise from which I’ve learned a lot.
Also, congrats on making it to the end, I know this ended up being a very lengthy post.