Microsoft KB Archive/249671

From BetaArchive Wiki
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.
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



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