I still use sp_help_revlogin to transfer logins between servers. I know there are other options, including a set of Powershell commands contained in dbatools (#YouCanDoAnythingWithPowershell) but this is a case where the old method works so I don't mess with it.
 |
| http://wanna-joke.com/wp-content/uploads/2013/08/funny-pictures-too-olf-for-internet.jpg |
Having said that, I know a lot of people still use sp_help_revlogin, so I wanted to document something I tripped over.
--
This morning trying to use a saved script to transfer a login to a new server, I ran into this:
Msg 15021, Level 16, State 2, Line 1
Invalid value given for parameter PASSWORD. Specify a valid parameter value.
 |
| https://pics.me.me/um-ok-memecrunch-com-19987825.png |
This was a new one on me, but a little Google led me to the problem, and it was described in the notes for the sp_help_revlogin procedure itself at https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server:
--
A password can be hashed in the following ways:
- 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 and later versions.
--
Sure enough I checked and the server I was trying to deploy onto was a SQL 2008R2 server (yes our clients still have lots of 2005/2008/2008R2).
The script I had saved looked like this:
CREATE LOGIN [MyLogin]
WITH PASSWORD = 0x0200A52324E0FFEBABFAD9327A62EECD2A5D648FF3B98D97C5D020FC16552BA728464BAAC04C8EFDE3BB0235A1F3648E419B038C04C8EFDE3BB0235A1F3648E419B0385A5933 HASHED
/* Hash slightly character masked 🙂 */
, SID = 0x7FCDC703D88B5C48A47D7D13699658E2
, DEFAULT_DATABASE = [master]
, CHECK_POLICY = OFF
, CHECK_EXPIRATION = OFF
As a test, I ran an sp_help_revlogin for the same login (same password) on SQL 2008 server:
CREATE LOGIN [MyLogin]
WITH PASSWORD = 0x0100E02F8267F6081A5AB316F8A6891C81A5AB316F8A6891CCC HASHED
/* Hash slightly character masked 🙂 */
, SID = 0x14B5D0191E908D499C83C1695F13F7F3
, DEFAULT_DATABASE = [master]
, CHECK_POLICY = OFF
, CHECK_EXPIRATION = OFF
Note that the second password hash is significantly shorter (which makes perfect sense since SHA1 is a shorter hash than SHA2, as described here.) This is one of the key reasons SHA1 has fallen out of favor - there have been known issues with SHA1 for years.
(Another reason to get off SQL 2005/2008/2008R2...as if you should need another reason in the year 2018!)
 |
| http://img.izismile.com/img/img6/20130327/original640/old_technology_640_01.jpg |
The bottom line is that to have login scripts like this, you need two versions, a SQL2005-2008R2 SHA1 version, and a SQL 2012+ SHA2 version.
*OR*
If you truly only want one copy of your script, it needs to be a SHA1 (short hash) version. SQL 2005/2008/2008 R2 don't understand the 2012+ SHA2 long hash, but SQL 2012+ is backward-compatible to the SHA1 short hash.
Having said that, I would still use the SHA2 hash script whenever possible.
--
Hope this helps!
