Is it possible to change all users passwords to upper-case

Question

I'm currently migrating some servers from MS sql server 2000 to 2008. Some of the legacy apps are password protected by essentially checking if a user with that name and password exists on the server.

Each app converts the password to uppercase before sending it to the server, but yep, you guessed it, the passwords on the server are not necessarily stored in upper case: this was not a problem for 2000 but is in 2008 (and rightly so!).

The apps don't have any clever features that allow the user to change their password, and i don't want to have to ask every user to change their password or alter the applications, therefore i was wondering if it is possible for a script to change all users passwords to upper and how one might go about creating it (tables to modify, how to modify hashed fields etc)?

Answer 1

You can indeed script out passwords from sql server 2000 and import them into sql server 2008 so that they are recognised as upper case passwords. Here's some background reading that will be useful in understanding sql server password hashes, so you can following along with the example.

SQL Server 2000 passwords were actually case insensitive as described in this rant. Even so, the sql server hashes store a case sensitive and case insensitive copy of the password, so that when you migrate password hashes to sql server 2005/2008 your passwords are then case sensitive.

To test this out, you can run the following on your SQL Server 2000 Server:

exec sp_addlogin  @loginame= 'usera' , @passwd='password'

logging into SQL Server 2000 as usera works with 'PASSWORD' , even though it shouldn't. To migrate usera to SQL Server 2005/2008 we can use the following to copy the login keeping the sid & password hash:

select 
'exec sp_addlogin @loginame ='''
+ [name] + ''''
+ ', @passwd= ' 
+ master.dbo.fn_varbintohexstr([password])
+ ', @sid= ' 
+ master.dbo.fn_varbintohexstr([sid])
+ ', @encryptopt = ''skip_encryption_old'''
from sysxlogins where name='usera'

You will get the following output (with different sid & hash of course):

 exec sp_addlogin @loginame ='usera', 
@passwd= 0x01004409eb54922c0cd2bedbad754f37afad4053bdadf719ff80c8a8abf5801b813114be6ba0c2c8543b2db77b33, 
@sid= 0x06cf56eb108a12428712f8b7c66ca1cd, @encryptopt = 'skip_encryption_old'

What you can do is perform some 'processing' on the password hash before importing it into 2008 so that the second upper case hash overwrites the case sensitive hash. This will mean all your passwords are in upper case. Using the password has above, you can perform the following operation, which I've done here in t-sql:

declare @old_passwd char(94)  -- original hash from sql server 2000
declare @new_passwd char(94)  -- new upper case password for sql server 2008
declare @cs_hash char(40) -- case sentive part
declare @ci_hash char(40) -- case insentive part 
declare @salt char(14)
set @old_passwd = '0x01004409EB54922C0CD2BEDBAD754F37AFAD4053BDADF719FF80C8A8ABF5801B813114BE6BA0C2C8543B2DB77B33'

set @salt    = SUBSTRING(@old_passwd,1,14)
set @cs_hash = SUBSTRING(@old_passwd,15,40)  -- not used, but here for understanding
set @ci_hash = SUBSTRING(@old_passwd,55,40)

set @new_passwd = @salt + @ci_hash + @ci_hash

SELECT @new_passwd

using this @new_password as the @passwd parameter for sp_addlogin will mean the password is recognised as upper case!

Answer 2

There's no way of changing their existing passwords, they're effectively encrypted locally, but you can reset their passwords to something in upper-case and inform them

Answer 3

the correct answer is to fix the legacy apps so that they don't do that (that qualifies for a DailyWTF - deliberately reducing the search space for a brute force crack).

hopefully you have the source code and can do that.

if not, chopper3's answer will work.

Answer 4

Just FYI, the case sensitivity is dependent on the collation. There are case-insensitive colations you could use as a workaround, perhaps temporarily.

Answer 5

You may as well do as Chopper3 suggests because you're going to have to inform the users anyway. Even if you can script a change to uppercase it is still a password change and the users need to be told about it.

Answer 6

The problem is that the database collation setting that you had in the old 2000 database was case-insensitive but you migrated to a new database where the collation setting was case-sensitive. All you need to do is change the collation setting in you newer databases properties and then it will match the names regardless of the case.

Your app was coded to Uppercase the passwords because the developers didn't understand that the 2000 database was case-sensitive. They could have easily changed the 2000 database setting to case-insensitive to solve their problem but they obviously chose to "hack" around it instead.

I find it interesting that everyone elses answer to this question is following the same naive path. Imagine all the wasted time in history due to developers not understanding this simple issue.