SQL Server 2014 – Different Password Hash Issue

sql serversql server 2014

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.

Best Answer

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.

Related Question