SQL SERVER – Transfer Logins Error: Msg 15021: Invalid Value Given for Parameter Password

One of my existing clients contacted me for assistance in fixing an error. Sometimes, if the error is easy and it doesn’t take more than 10-15 minute, I give them the benefit of being my existing customer and I give free consulting (not always though). They informed me that they need moving SQL logins (username and password) from one server to another. To achieve that they were using sp_help_revlogin. Here are the steps:

• Create sp_help_revlogin and sp_hexadecimal on the source server using KB
• Execute the procedure in text mode in SSMS.
• Copy the resulting SQL output (SQL Script)
• Paste output from the previous step to the destination server
• Execute the script.

Here is the error they were getting while moving logins.

Msg 15021, Level 16, State 2, Line 1
Invalid value given for parameter PASSWORD. Specify a valid parameter value.

When I looked at the password value supplied, it is a hashed value rather than text. That’s how we can see the password in sql_logins so that should be OK.

WORKAROUND/SOLUTION

While checking further, I found that they were using this method to move logins from SQL Server 2012 to SQL Server 2008 R2.  When I check on my machine the password_hash was starting with 0x01 in SQL Server 2008 R2 and in SQL Server 2012 it was starting with 0x02. Looks like that defines the SHA1 and SHA2.  I also found that error is already documented in the KB of sp_help_revlogin and we need to use a blank password in the script and set the password for the account later.

Reference: Pinal Dave (https://blog.sqlauthority.com)