SQL Server Login password hash

In this article we will look at how SQL Server stores passwords and how we can go about working them out.

As a developer/administrator you are probably accessing SQL Server via a windows login, however the other option is when the instance is changed into mixed mode allowing SQL Logins. These logins are created within the master database and shown in sys.server_principals.

There is additional information in sys.sql_logins which itself inherits from sys.server_principals.  The 3 additional columns are called is_policy_checked,is_expiration_checked and password_hash.

If you take a hash of a known piece of text using the HASHBYTES function, and then compare it with an identical password you have entered when creating a sql login you will notice that they do not match. (Looking at the password_hash columns in sys.sql_logins)

The difference is that an addition piece of data called a salt has been added to the process.

x = Hash(PlainText + Salt) instead of x = Hash(PlainText)

SELECT HASHBYTES('SHA2_512',CAST(N'JonGurgul' AS VARBINARY(MAX)))
--0x605334B588BA046B9EA3FD2F7C501ABD549D2F698A57E78EAE525553F72CBA0B710FBD928FB1AE05FD6FAEECB9A957C2EEF0323EA6BC75FE92A60C7D4FAA7AD2

SELECT HASHBYTES('SHA2_512',CAST(N'JonGurgul' AS VARBINARY(MAX))+0xF1202F8A)
--0x0C9748812054A27F0209CD5DCA57EFE33C496A112BA6EBB048B91D35FA784F385A1625228777164719565A02612255B83F6BD37DE096DEBE74AF9B936BB8C02D

This helps to add additional security, however SQL server stores the salt as part of the hash. If you look at the password_hash column you will have something that looks like:

0x0200F1202F8A0C9748812054A27F0209CD5DCA57EFE33C496A112BA6EBB048B91D35FA784F385A1625228777164719565A02612255B83F6BD37DE096DEBE74AF9B936BB8C02D

Now you will see that the bold 4 bytes match the salt that I had used in the previous example, and the red data is the actual hash of the plain text.

Cracking/Hacking/Guessing SQL Login passwords

Now for an example and with a bit of guess work lets see if I can work out any of your passwords. Note that the hash algorithm changes from SHA1 to SHA2-512 from SQL 2012.

--http://jongurgul.com/blog/sql-server-login-password-hash
USE [tempdb]
GO
IF NOT EXISTS(SELECT * FROM [tempdb].sys.tables WHERE name = 'WordList')
BEGIN
 CREATE TABLE [dbo].[WordList]([Plain] NVARCHAR(MAX))
 
 --USERNAME//PASSWORD COMBOS
 INSERT INTO [WordList]([Plain])
 SELECT [name] FROM sys.sql_logins
 UNION
 SELECT REPLACE(REPLACE(REPLACE([name],'o','0'),'i','1'),'e','3') FROM sys.sql_logins
 UNION
 SELECT REPLACE(REPLACE(REPLACE([name],'o','0'),'i','1'),'e','3')+'.' FROM sys.sql_logins --example added character
 UNION
 SELECT REPLACE(REPLACE(REPLACE([name],'o','0'),'i','1'),'e','3')+'!' FROM sys.sql_logins --example added character
 
 --No Comment
 INSERT INTO [WordList]([Plain]) VALUES (N'')
 INSERT INTO [WordList]([Plain]) VALUES (N'password')
 INSERT INTO [WordList]([Plain]) VALUES (N'sa')
 INSERT INTO [WordList]([Plain]) VALUES (N'dev')
 INSERT INTO [WordList]([Plain]) VALUES (N'test')
END
--DECLARE @Algorithm VARCHAR(10)
--SET @Algorithm = CASE WHEN @@MICROSOFTVERSION/0x01000000 >= 11 THEN 'SHA2_512' ELSE 'SHA1' END
 
SELECT
 s.[name]
,s.[password_hash]
,SUBSTRING(s.[password_hash],1,2) [Algorithm]
,SUBSTRING(s.[password_hash],3,4) [Salt]
,SUBSTRING(s.[password_hash],7,(LEN(s.[password_hash])-6)) [Hash]
,HASHBYTES(a.[Algorithm],CAST(w.[Plain] AS VARBINARY(MAX))+SUBSTRING(s.[password_hash],3,4)) [ComputedHash]
--,HASHBYTES(@Algorithm,CAST(w.[Plain] AS VARBINARY(MAX))+SUBSTRING([password_hash],3,4)) [ComputedHash]
,w.[Plain]
FROM sys.sql_logins s
INNER JOIN (
SELECT 0x0100 [AlgorithmVersion],'SHA1' [Algorithm] UNION ALL
SELECT 0x0200,'SHA2_512'
) a ON a.[AlgorithmVersion] = SUBSTRING(s.[password_hash],1,2)
INNER JOIN [tempdb].[dbo].[WordList] w
ON SUBSTRING(s.[password_hash],7,(LEN(s.[password_hash])-6)) = HASHBYTES(a.[Algorithm],CAST(w.[Plain] AS VARBINARY(MAX))+SUBSTRING(s.[password_hash],3,4))
--ON SUBSTRING([password_hash],7,(LEN([password_hash])-6)) = HASHBYTES(@Algorithm,CAST(w.[Plain] AS VARBINARY(MAX))+SUBSTRING([password_hash],3,4))

IF EXISTS(SELECT * FROM [tempdb].sys.tables WHERE name = 'WordList')
BEGIN
 DROP TABLE [tempdb].[dbo].[WordList]
END
GO
SELECT
[name]
,[password_hash]
,SUBSTRING([password_hash],1,2) [Algorithm]
,SUBSTRING([password_hash],3,4) [Salt]
,SUBSTRING([password_hash],7,(LEN([password_hash])-6)) [Hash]
FROM sys.sql_logins
GO

I have used a table to store some plain text example passwords, but you can obviously add your own guesses to try to determine the obvious ones.

Hopefully the above simple code has not worked out any of your passwords, but if it has I would suggest taking the opportunity to change them.

Another option instead of manually extracting the salt and rehashing is to use the function PWDCOMPARE which will do the work for you.

SELECT [name],[password_hash]
FROM sys.sql_logins
WHERE PWDCOMPARE(N'JonGurgul',[password_hash]) = 1