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

, 2018-10-30 (first published: )

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 = 0x0100E02F8267F60
81A5AB316F8A6891C81A5AB316F8A6891CCC 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!

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

2009-02-23

1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

2009-02-17

1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

2009-02-13

360 reads