0

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.

SQL Server 2000 Hashed password

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?

SQL Server 2008 Hashed password

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'
5
  • 6
    "I'm currently upgrading SQL Server from 2000 to 2008" - erm? SQL 2008 is long out of extended support. Commented Dec 28, 2022 at 14:49
  • 1
    Hopefully the OP is using as a "middle man" to then restore to a more recent version, @MitchWheat , as 2008 is the "newest" (for lack of better word) version that allowed SQL Server 2000 databases to be restored on it.
    – Thom A
    Commented Dec 28, 2022 at 14:51
  • hopefully doth butter no parsnips Commented Dec 28, 2022 at 14:51
  • 1
    That SQL 2000 password hash looks wrong. It's a SHA1 hash so it shouldn't be that long. See social.msdn.microsoft.com/Forums/en-US/… Laurentiu Cristofor worked security for the SQL Server product team at that time, so he's an authority. Commented Dec 28, 2022 at 15:27
  • Trust me , im not the man in charge to decide which servers to use. I know that these are out-dated and out of support, i've already pointed that out to my managers. Commented Dec 29, 2022 at 9:04

2 Answers 2

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.

1
  • Thank you for your answer, i have downloaded the scripts and tried them. The output is the same as the other scripts i found, the same long hashed password. Commented Dec 29, 2022 at 9:19
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.