One SQL Server 2014 giving different password hash

Question

I've been using script based off Microsoft's EXEC sp_help_revlogin script to synchronise SQL Server usernames and passwords between hosts. This has been working fine for a long time now on SQL Server 2008 R2, but with us planning our move to SQL Server 2014 I've run into a snag.

I have three staging SQL Server 2014 machines. On all the servers I run the following command:

CREATE LOGIN [TestAccount] 
WITH PASSWORD = N'1234567890', 
     DEFAULT_DATABASE = [master], 
     CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF

For two of them, I get a generated "Create" command of:

CREATE LOGIN [TestAccount] 
WITH PASSWORD = 0x010075DDCA54FCEF12CB11C4E64040E877B9FF5872C41EB98095 HASHED, 
     SID = 0xCA78345104805C4E89004969D05D551B, 
     DEFAULT_DATABASE = [master], 
     CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

Cool. So far, so good. But, on the third server, I get a "Create" command of:

CREATE LOGIN [TestAccount] 
WITH PASSWORD = 0x02002F6CB52E7F571AD422689021EB9EC1BE2AB4576AE6EC12485333A4CB892A9197B440E1471376A5AAC5160847F636A637D1F499880D7653ABC4DB4714746856E01DE41E09 HASHED, 
     SID = 0x19337EA32254A64F8FC018474B58DBA5, 
     DEFAULT_DATABASE = [master], 
     CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

Which doesn't work. The hash is way too big.

Is there some compatibility level flag or something that I've somehow got turned on on this one server that is generating old fashioned password hashes or something?

---

Giving the checkmark to Ryan as he was right. I was actually connecting to the wrong instance installed on the same physical machines as the SQL Server 2014 instances. I was very tired on Friday night.

Answer 1

This is because since SQL Server 2012 the hash algorithm has changed.

Starting from this version, the hash is twice as long.
You can read up on the three hash versions that are used for the various versions of SQL Server in the sp_help_revlogin microsoft article, under Remarks:

A password can be hashed in three ways:
VERSION_LEGACY: This hash is a 16-byte pre-SQL Server 2000 hash.
VERSION_SHA1: This hash is generated by using the SHA1 algorithm and is used in SQL Server 2000 through SQL Server 2008 R2.
VERSION_SHA2: This hash is generated by using the SHA2 512 algorithm and is used in SQL Server 2012.

Which means that if you want to move a SQL Server 2012 or higher user to a 2008r2 instance, you'll need to find a different method.

---

I tested it quite a bit now. Any user created under SHA1 when moved using the password hash to a new version server (2012+) can then be moved back as long as the password isn't changed. However any new user created in 2012+ will not be moveable to 2008r2 or prior.

Answer 2

When you create a login, SQL Server add a 4 bytes salt key to it (i.e. Password '12345678' + key 0x1234abcd) and hash it using SHA algorithm.

Because the salt key is randomly generated, each hash will be different unless it use the same key twice. (1 out of 65k possible salt keys)

You can see the salt key at the beginning of the hash. First 2 bytes are used to store the version of the hash algorithm used. The 4 next bytes are the salt key. The remaining bytes save the hash of your password.

When you login, it takes the salt key out of the stored hash, add it to the input from the login windows and hash it. It then compare the result to the password portion of the stored hash.

With 0x02002F6CB52E7F571AD422689021EB9EC1BE2AB4576AE6EC12485333A4CB892A9197B440E1471376A5AAC5160847F636A637D1F499880D7653ABC4DB4714746856E01DE41E09 we have:

• Header version = 0x0200

• Salt = 0x2F6CB52E

• Password = 0x7F571AD422689021EB9EC1BE2AB4576AE6EC12485333A4CB892A9197B440E1471376A5AAC5160847F636A637D1F499880D7653ABC4DB4714746856E01DE41E09