Recently, during the development of the Windows security agent, there is a feature to perform weak password detection on SQL Server. To obtain a feasible solution, we need to first understand how SQL Server stores and verifies passwords.

Hash algorithm and verification function

The calculation and verification of password hash are actually reverse processes and former can deduce the latter. This article only focuses on versions after SQL Server 2012. Since that, SQL Server has been using the same algorithm, which is more universal. Hash Algorithms – How does SQL Server store Passwords? This article provides a detailed introduction to this algorithm and presents its implementation using SQL statements.

DECLARE @pswd NVARCHAR(MAX) = 'APassword';
DECLARE @salt VARBINARY(4) = CRYPT_GEN_RANDOM(4);
DECLARE @hash VARBINARY(MAX);
SET @hash = 0x0200 + @salt + HASHBYTES('SHA2_512', CAST(@pswd AS VARBINARY(MAX)) + @salt);
SELECT @hash AS HashValue, PWDCOMPARE(@pswd,@hash) AS IsPasswordHash;

You can see that the password hash value is a hexadecimal string presented in the following format:

{0x200 (fixed prefix)} + {4 bytes random salt} + {SHA_512(password+salt)}

Since the salt is random 4 bytes each time, even for the same plain text password, the hash will always differ.

Then I translated this algorithm into the go code:

func hashPassword(password string, salt []byte) []byte {
	saltedPassword := append([]byte(password), salt...)
	sha512hash := sha512.Sum512(saltedPassword)
	hashedPassword := append(salt, sha512hash[:]...)
	return hashedPassword
}

However, there is a problem, even if the same salt is used in the go code and SQL statements, the resulting hash values are different.

And finally I noticed that the data type for ‘pswd’ was NVARCHAR instead of VARCHAR. Both data types are utilized for storing strings with variable lengths, but NVARCHAR encodes each character using 2 bytes (16 bits), while VARCHAR uses only 1 byte (8 bits) per character.

So the issue of the go code is that when I use []byte(password) to convert the string into a byte slice, Go actually use UTF8 encoding, which is a variable-length encoding that utilizes 1-4 bytes for encoding each character. The data was wrong here because we need to encode strings with a fixed 2 bytes for each character, such as UTF16. Here is the code after GPT fixed it for me:

func utf16Bytes(input string) []byte {
	utf16bytes := []rune(input)
	bytes := make([]byte, len(utf16bytes)*2)
	for i, v := range utf16bytes {
		bytes[i*2] = byte(v)
		bytes[i*2+1] = byte(v >> 8)
	}

	return bytes
}

func hashPassword(password string, salt []byte) []byte {
	saltedPassword := append(utf16Bytes(password), salt...)
	sha512hash := sha512.Sum512(saltedPassword)
	hashedPassword := append(salt, sha512hash[:]...)
	return hashedPassword
}

func verifyPassword(hashedPassword []byte, password string) bool {
	salt := hashedPassword[:4]
	originalHash := hashedPassword[4:]
	saltedPassword := append(utf16Bytes(password), salt...)
	sha512hash := sha512.Sum512(saltedPassword)
	return bytes.Equal(originalHash, sha512hash[:])
}

I finished the verification function by the way, and as I said above, this is actually a reverse process.

Extract user password hash

Now we have the hash algorithm and verification function, we need to figure out a way to get password hashes from SQL Server for our upcoming auditing work.

Extracting SQL Server Hashes From master.mdf This article shows us a way to extract password hashes from the master.mdf file and provides the PowerShell script. We can locate the MDF file from this location and extract hashes from it. mdf dump The ‘test’ user is an account I created it for test, its plaintext password is 123456, and its hash is

0x0200CC6368C75E9C63B876C62F18457C6A0EB96E3E1D602540057045FBC77054F5D72B9E43900E2FC72D5130FD1DF9FBD0784923B39BF57C600BA8B977F8E5A0B035E93F5F0B

We can use this code to verify the plaintext password:

bytes, _ := hex.DecodeString("CC6368C75E9C63B876C62F18457C6A0EB96E3E1D602540057045FBC77054F5D72B9E43900E2FC72D5130FD1DF9FBD0784923B39BF57C600BA8B977F8E5A0B035E93F5F0B")

fmt.Println(verifyPassword(bytes, "123456"))

The above code will output true.

The final step

The last step is to match the hash against each plaintext password contained within a weak password dictionary file.