August 3, 2009 at 10:53 pm
Hi,
I have 2 sql server logins in S2K that I need to transfer across to S2K5.
When I run the above SP, I get the following:
-- login: xxx1
exec master..sp_addlogin 'xxx1', null, @sid = 0x937210etc, @encryptopt = 'skip_encryption'
-- login: xxx2
set @pwd = convert (varbinary(256), 0x01008E3D1C57C3etc)
exec master..sp_addlogin 'xxx2', @pwd, @sid = 0x6546CFDetc, @encryptopt = 'skip_encryption'
The second login works fine, but the first login doesn't work and seems to be missing the "set @pwd".
Any ideas?
thanks :unsure:
August 6, 2009 at 2:45 am
Null password? sp_help_revlogin includes
ELSE BEGIN -- SQL Server authentication
IF (@binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @binpwd, @txtpwd OUT
IF (@xstatus & 2048) = 2048
SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
ELSE
SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
PRINT @tmpstr
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
END
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
END
August 12, 2009 at 8:00 pm
yes....but I am wondering why it generates a command to run on SQL Server 2005 that has a null password but the command won't accept the null password...
August 13, 2009 at 2:32 am
I haven't done a migration from 2K to 2K5, so I'm speculating: perhaps the 2K5 server security policy will not accept a NULL password.
Just to get the accounts transferred, you could add a SET line with a generic password, and replace the NULL in the sp_addlogin line. At least all would then have passwords.
Or, since sp_addlogin is on the way out after SQL2K5, you could generate CREATE LOGIN statements instead, and use the CHECK_POLICY option to turn off security settings. But that would leave you with those NULL passwords, and part of the exercise ought to be to eradicate them. If your users are unwilling, you can always sell it as improved security from the new version
Edit - I say "all", but I see it is only 2 users; still, the principle applies
August 13, 2009 at 9:10 am
There is a revlogin sp specifically for migrations from 2000 to 2005.
Look at sp_help_revlogin_2000_to_2005 in this document:
http://support.microsoft.com/kb/246133
Not certain it will fix your problem, but definitely worth a try before debugging their sp.
Kyle
August 13, 2009 at 9:38 am
Nice catch - I should have checked: it uses CREATE LOGIN and turns off checking for null passwords. Though I would still rather correct that situation than leave it.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply