The Effect on Passwords of Changing Sort Orders

Because of the introduction of one-way encrypted passwords with SQL Server 6.0, changing the sort order has an impact on the use of passwords.

When a login ID is added or a password is changed, the password encryption algorithm takes the supplied password and generates a numeric representation of it, which is stored in the syslogins table. (If you look in master..syslogins.password, what looks like garbled text is actually a textual representation of the binary encrypted password).

• If the password is generated on a case-sensitive server, the original password text, as typed, is supplied to the password algorithm. When the password is subsequently checked, the user must supply the same characters in the appropriate case for authentication.
• If the password is generated on a case-insensitive server, the password is converted to uppercase before generating and checking the encrypted form. When the password is subsequently checked, the user can supply the characters in upper- or lowercase.

When changing sort orders, if a user transfers syslogins out of the server with one sort order and into a new server with another sort order, the encrypted passwords will be moved as generated. This has the following effects:

• If moving from case-insensitive to case-sensitive servers, users will need to type passwords in uppercase to be authenticated.
• If moving from case-sensitive to case-insensitive servers, users will not be able to log in unless the password generated in the case-sensitive server was entirely in uppercase.

When changing sort orders, take the appropriate steps to ensure that users will be able to log in to the server when the sort order conversion is complete. If the system administrator (SA) is not able to log in because of the effects described, you will need to restore the original sort order to correct the problem.

For information about changing character sets and sort orders and for information about login IDs and passwords, see the Microsoft SQL Server Administrator's Companion.