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

Article translations Article translations
Article ID: 249671 - View products that this article applies to.
This article was previously published under Q249671
This article has been archived. It is offered "as is" and will no longer be updated.
Expand all | Collapse all

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.

Properties

Article ID: 249671 - Last Review: November 2, 2013 - Revision: 5.0
Applies to
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 6.5 Standard Edition
Keywords: 
kbnosurvey kbarchive kbprb KB249671

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com