Microsoft KB Archive/249671

From BetaArchive Wiki
< Microsoft KB Archive
Revision as of 13:51, 21 July 2020 by X010 (talk | contribs) (Text replacement - """ to """)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Knowledge Base

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


  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 6.5 Standard Edition

This article was previously published under Q249671


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'.


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.


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