SQL Server 2000 to 2008 security logins transfer

Question

I'm currently upgrading SQL Server from 2000 to 2008, the database was successfully transferred, the issue remains with the security logins, I have found the stored procedures that extracts the logins with Sid/Password and so on.

The issue I'm facing is the password length is 94-128 character in SQL Server 2000.

When I tried to create the security login in SQL Server 2008, only 54 characters are used not the whole password hash, is there anything I'm missing?

Here is the creation query:

DECLARE @pwd sysname
SET @pwd = CONVERT (varbinary(256), 'Password hash')

EXEC master..sp_addlogin 'Name', @pwd, @sid = Sid, @encryptopt = 'skip_encryption'

Answer 1

Wow. Blast from the past. Insanely enough, you want to use some ancient tools/scripts that Microsoft created for these purposes:

• sp_hexadecimal
• sp_help_revlogin

They're no longer cool/sexy or even something I would recommend (at all). But, cough, for a 2000 to 2008 migration, they should be a fairly straightforward fix.

You can grab the "Code Download" from this page: http://sqlservervideos.com/video/copying-and-moving-sql-server-logins/

Where I also show how to use these ancient scripts/etc.

Answer 2

I'd expect truncation to 54-characters for a typical SQL Server 2000 password imported to SQL Server 2005+.

Password case-sensitivity was not enforced in SQL Server 2000 with case-insensitive collations.

Passwords were stored in the encrypted form with both the hash of the password + salt, appended with the hash of the uppercase password + salt.

As an example, the Login password "passWORD" might be stored as:-

0x01006CFD28AB35182C48F84186095FA1DE65BFB6A4EEA5424A32DD0FB828CED4F2E0DD2DDEB2D6751750CBFE165F

This is comprised of:

• 0x0100 means hash algorithm SHA-1
• 6CFD28AB is the salt
• 35182C48F84186095FA1DE65BFB6A4EEA5424A32 is the hash of the password + salt
• DD0FB828CED4F2E0DD2DDEB2D6751750CBFE165F is the hash of the uppercase password + salt

If you create a Login with an old-style 'long' hashed password, SQL Server 2005+ automatically discards the redundant uppercase portion of the hash. It's redundant because password case-sensitivity is always enforced, so it's not needed for comparison.

You can prove this by creating Logins with the two versions and confirming that only the short version is persisted:-

CREATE LOGIN [Long] WITH 
PASSWORD = 0x01006CFD28AB35182C48F84186095FA1DE65BFB6A4EEA5424A32DD0FB828CED4F2E0DD2DDEB2D6751750CBFE165F HASHED;
    
CREATE LOGIN [Short] WITH 
PASSWORD = 0x01006CFD28AB35182C48F84186095FA1DE65BFB6A4EEA5424A32 HASHED;
    
SELECT [name] AS LoginName,
       LOGINPROPERTY([name], 'PasswordHash') AS PasswordHash
FROM dbo.syslogins
WHERE [name] IN ( 'Long', 'Short' );

LoginName PasswordHash
--------- ------------------------------------------------------
Long      0x01006CFD28AB35182C48F84186095FA1DE65BFB6A4EEA5424A32
Short     0x01006CFD28AB35182C48F84186095FA1DE65BFB6A4EEA5424A32

Both the 'long' and 'short' versions of the hashed password are valid for the same password:-

SELECT PWDCOMPARE('passWORD', 0x01006CFD28AB35182C48F84186095FA1DE65BFB6A4EEA5424A32DD0FB828CED4F2E0DD2DDEB2D6751750CBFE165F);
SELECT PWDCOMPARE('passWORD', 0x01006CFD28AB35182C48F84186095FA1DE65BFB6A4EEA5424A32);

Furthermore, you can take the second (uppercase) half of the 'long' version and successfully validate it with the all uppercase password:-

SELECT PWDCOMPARE('PASSWORD', 0x01006CFD28ABDD0FB828CED4F2E0DD2DDEB2D6751750CBFE165F);

The [string] length of the 'long' version is 94-characters and the [string] length of the 'short' version is 54-characters.

SELECT LEN('0x01006CFD28AB35182C48F84186095FA1DE65BFB6A4EEA5424A32DD0FB828CED4F2E0DD2DDEB2D6751750CBFE165F');
SELECT LEN('0x01006CFD28AB35182C48F84186095FA1DE65BFB6A4EEA5424A32');

The enforcement of password case-sensitivity has caused me huge problems in the past upgrading from SQL Server 2000 to SQL Server 2005+. Application servers configured using multiple cases of the same password in their connection strings, for example "PassWord", "PaSsWoRd", etc. will work fine with SQL Server 2000. But once upgraded to SQL Server 2005+, suddenly these application servers will suffer password-related login failures, despite the passwords having not been changed in the Instance nor in the connection strings. In this situation, you need to determine the actual case of the stored password and amend all connection strings to use it correctly and consistently.