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.