Microsoft KB Archive/249671

= Passwords unusable after SQL Server 6.5 to 7.0 upgrade with different sort order =

Article ID: 249671

Article Last Modified on 12/17/2004

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q249671



SYMPTOMS
Following a SQL Server 6.5 to SQL Server 7.0 upgrade, you may be unable to log in to SQL Server 7.0 using some SQL Server authenticated logins that were upgraded from SQL Server 6.5 if all of the following conditions are met:
 * The sort order of the SQL Server 6.5 server is case-sensitive.
 * The sort order of the SQL Server 7.0 server is case-insensitive.
 * The password of the SQL Server 6.5 login includes any lowercase letters.

Under these conditions, attempts to log in to the case-insensitive server with a transferred password that contains lowercase letters will fail with message 18456:

Login failed for user '%ls'.



CAUSE
Passwords in a case-insensitive SQL Server are converted to uppercase before being stored or used. Passwords in a case-sensitive SQL Server are not converted to uppercase. Because of this difference, passwords originally encrypted on a case-sensitive server and later transferred to a case-insensitive server cannot be used unless all alphabetic characters in the password are uppercase.

For more information about this behavior see the "Effect on Passwords of Changing Sort Orders" topic in either the SQL Server 6.5 or 7.0 Books Online.



WORKAROUND
In this situation, you can do either of the following to work around this problem:
 * Rebuild the SQL Server 7.0 master database with a case-sensitive sort order and upgrade the data again. For information on how to rebuild the master database, see the "Rebuilding the master Database" topic in the SQL Server 7.0 Books Online.
 * Reassign passwords to the affected login accounts. For details, see the "Modifying Logins" topic in SQL Server 7.0 Books Online.

Additional query words: convert security

Keywords: kbprb KB249671

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.