none
password hash algorithm in SQL Server 2012

    Question

  • Hello,

    for a book about SQL Server 2012 security I am currently writing, I am trying to show how a dictionary attack could be done quickly. And it seems fortunately that this can't be done. But I find some interesting results I'd like to be able to explain. Here is my demo code :

     

    CREATE DATABASE security;
    GO
    
    USE security;
    GO
    
    CREATE TABLE dbo.dictionary (word nvarchar(100) PRIMARY KEY);
    GO
    
    -- BULK INSERT a word list
    INSERT INTO dbo.dictionary VALUES (N'My Pa$$w0rd');
    
    -- the password was hashed before in SHA1, not anymore it seems 
    ALTER TABLE dbo.dictionary
    ADD hash AS HASHBYTES('SHA1', word) PERSISTED;
    
    -- using the "obsolete" PWDENCRYPT
    ALTER TABLE dbo.dictionary
    ADD hash2 AS PWDENCRYPT(word) PERSISTED;
    -- error : non deterministic, so :
    
    ALTER TABLE dbo.dictionary
    ADD hash2 AS PWDENCRYPT(word);
    
    SELECT * FROM dbo.dictionary;
    -- hash2 is changing at all execution ...
    
    CREATE LOGIN MyLogin WITH PASSWORD = N'My Pa$$w0rd';
    
    SELECT word
    FROM dbo.dictionary
    WHERE hash = LOGINPROPERTY('MyLogin', 'PasswordHash');
    
    SELECT word
    FROM dbo.dictionary
    WHERE hash2 = LOGINPROPERTY('MyLogin', 'PasswordHash');
    
    -- SELECT LOGINPROPERTY('MyLogin', 'PasswordHash')
    SELECT word, hash, hash2, LOGINPROPERTY('MyLogin', 'PasswordHash') as PasswordHash,
    	PWDENCRYPT(word) as [PwdEncrypt]
    FROM dbo.dictionary
    WHERE word = N'My Pa$$w0rd';
    
    
    SELECT HASHBYTES('SHA1', N'My Pa$$w0rd');
    SELECT LEN(HASHBYTES('SHA1', N'My Pa$$w0rd')); -- 20 --> 160 bits of course.
    SELECT PWDENCRYPT(N'My Pa$$w0rd');
    SELECT LEN(PWDENCRYPT(N'My Pa$$w0rd')); -- 70 --> 560 bits ...
    SELECT LEN(CAST(LOGINPROPERTY('MyLogin', 'PasswordHash') as varbinary(100))); -- the same
    
    SELECT HASHBYTES('SHA2_256', N'My Pa$$w0rd');
    SELECT HASHBYTES('SHA2_512', N'My Pa$$w0rd'), LEN(HASHBYTES('SHA2_512', N'My Pa$$w0rd')); -- 64 obviously ...
    
    -- cleaning
    USE Master;
    GO
    
    DROP LOGIN MyLogin;
    DROP DATABASE security;
    

     


    We can see that it is not anymore SHA1 (I have seen SHA1 in previous version BOL, I am not sure), nor other variants of SHA, and nothing that can be reproduced by a HASHBYTES existing algorithm. PWDENCRYPT generates non-consistent hashes, so it does not help to retrieve the PasswordHash. This PasswordHash needs to be deterministic, otherwise how to check it against the hash of the password provided at login ?

    Does anybody know what kind of algorithm is used in 2012 ?

    thanks,

    Rudi


    Tuesday, January 10, 2012 4:58 PM

Answers

  • btw this works :

    SELECT l.name, d.word as Password
    FROM dbo.dictionary d
    JOIN sys.sql_logins l ON PWDCOMPARE(d.word, l.password_hash) = 1
    


    Tuesday, January 10, 2012 5:22 PM

All replies

  • btw this works :

    SELECT l.name, d.word as Password
    FROM dbo.dictionary d
    JOIN sys.sql_logins l ON PWDCOMPARE(d.word, l.password_hash) = 1
    


    Tuesday, January 10, 2012 5:22 PM
  • If you're looking at fast dictionary and hybrid dictionary attacks against SQL Server (certainly up to and including 2008 R2), go to Hashcat's web site and check into that; GPU assisted rules based dictionary attacks with a modern GPU operate at a rate of billions of hashes per second.  SQL 2012 may have a different format; I'm not yet sure what that is.

    Thursday, July 19, 2012 6:25 PM
  • Regarding it no longer being SHA-1, a post on the Hashcat forums by Incisive states

    "Please include the SQL 2012 password hash; it's identical to 2005, except 0x0200 as an ID instead of 0x0100

    SHA-512 instead of SHA1"
    Thursday, December 20, 2012 6:08 PM