Invalid value given for parameter PASSWORD. Specify a valid parameter value.

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!