Quick one today:
Earlier, during migration of databases Sql 2014 we ran into an interesting error:
Msg 15021, Level 16, State 2, Line 2 Invalid value given for parameter PASSWORD. Specify a valid parameter value.
The migration script was syntactically correct, but it still kept throwing this error. On further investigation and with some help from MSDN, the underlying issue became less cryptic. The problem is not with the databases, but the migration script. In particular the step where we migrate logins.
A section of login migration script looks like this:
-- -- Old migrate Logins script -- create login [SampleLogin] with password = 0x020015cb289be47ca5d2ccb3dd3c401b489lklnsdfklndf98fg9hsdkfnsdklfj89sdfhiopsdnfsdfh89ssdfsdf34tsdfverf34fsd98b0320c8ba205dc8c3a26c hashed , sid = 0xkjsdfnklsdf8sduiohsd9bnasd98y9sdsd , check_policy = OFF , default_language = us_english GO
This was an old script from the DBA’s library; The team continued to use the same script for several years. Now that we are going to newer versions of Sql Server the password hashed length is causing problems. In newer versions, the password hash has higher requirements.
There are two steps to resolving this:
Step 1: We re-created the same login that has a blank password as shown below.
-- -- Old migrate Logins script -- create login [SampleLogin] with password = '' , sid = 0xkjsdfnklsdf8sduiohsd9bnasd98y9sdsd , check_policy = OFF , default_language = us_english GO
Step 2: On login attempt, the user was able to change the password.