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!
You can select the UIDs >= 500 to make sure you only get real users and not system accounts and then use the option --stdin of passwd to change the passowrd.
Something like this should work:
while IFS=: read u x nn rest; do if [ $nn -ge 500 ]; then echo "YOURSTRONGPASSWORD" |passwd --stdin $u; fi done < /etc/passwd
Best Answer
I think I might be in the minority on this (based on my limited experience dealing with IT departments at school and work), but I think mandatory, time-based password change policies are worthless at best, and harmful at worst. People tend to be very bad at choosing good passwords and keeping them secret. Password expiration policies are designed to mitigate this by limiting the amount of time any one password can be cracked/social engineered/stolen; however, they fail to achieve this in practice, primarily because they force users to relearn their password on a continuous basis. By making it harder for user to commit their passwords to memory, you end up causing many of them to choose weaker passwords, and/or write their passwords down someplace where prying eyes can find them.
Furthermore, when forced to change their password on a regular basis, many users will choose passwords that follow a very recognizable pattern, such as
[base string][digit]. Let's say a user wants to use their cat's name Fluffy as their password. They might start out with a password offluffy, then change it tofluffy1,fluffy2,fluffy3and so on. In this case, the policy doesn't really help security; even if the user chooses a more secure base string thanfluffy, and even if they keep their password safely memorized, the single suffix character that changes every few months does very little to mitigate cracking or social engineering attacks.See also: Password Expiration Considered Harmful, a short article (not written by me) which I think gives a good introduction to these problems.