Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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)?

share|improve this question
    
are these passwords SQL Server login? or are they applications passwords where the usernames/password are stored on a table in SQL Server? –  Nick Kavadias Aug 14 '09 at 8:30
    
They're SQL Server logins –  blahblahblahblahblah Aug 14 '09 at 9:24
    
Any chance of having the previous devs brought up on charges of incompetence and gross negligence? –  Adrien Aug 18 '09 at 16:38

6 Answers 6

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!

share|improve this answer

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

share|improve this answer

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.

share|improve this answer
    
I know, it took me quite a while to realise what was going on, i'm still trying to work out why! Especially as the apps are so limited in functionality they don't really need to be password protected anyway (IMHO). I know that eventually i'll have to fix the apps, but the project is to migrate the server. I'm just trying to tighten things up while i'm doing it. –  blahblahblahblahblah Aug 14 '09 at 13:10

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

share|improve this answer

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.

share|improve this answer
    
No i wouldn't, as at the moment whatever the user enters is being converted to uppercase by the client app without the users knowledge –  blahblahblahblahblah Aug 14 '09 at 10:07
    
I understand that but if you change the users password to upper case won't the users then need to know that when they log in themselves? –  John Gardeniers Aug 14 '09 at 12:17
    
Hmmm, i see where you are coming from, but the users don't log in to the server with the user name and password that they have for the application. Oh no, they have another username and password for that or they could use the well known sa password that they are given on their first day of working here!!! I feel it's going to be a long process getting this mess sorted out! –  blahblahblahblahblah Aug 14 '09 at 13:08

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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