Microsoft KB Archive/285025

= BUG: SQL 6.5 Logins upgraded to SQL 7.0 and subsequently moved to SQL 2000 through CDW cannot log in =

Article ID: 285025

Article Last Modified on 11/2/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Developer Edition
 * Microsoft SQL Server 2000 Enterprise Edition
 * Microsoft SQL Server 2000 Personal Edition
 * Microsoft SQL Server 2000 Enterprise Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q285025



BUG #: 351700 (SHILOH_BUGS)



SYMPTOMS
If SQL Server Standard Logins that were upgraded from Microsoft SQL Server 6.5 to Microsoft SQL Server 7.0 or SQL Server 2000 are subsequently moved to a SQL Server 2000 server through the Copy Database Wizard (CDW), users may not be able to connect using their SQL Server Login account.



CAUSE
After the Copy Database Wizard has completed copying a database from SQL Server 7.0 or SQL Server 2000, the system catalog in the destination SQL Server 2000 server is not updated to reflect the SQL Server 6.5 source of password encryption. Thus, the password is assumed to be in SQL Server 7.0 or SQL Server 2000 encryption mode and an attempt to log in using that login or password fails. Please note that if you are performing an upgrade from SQL Server 6.5 to SQL Server 2000 and all the users that logged in at least once to SQL Server 2000, followed by a CDW transfer of the logins, do not experience the problem and are able to connect on the new SQL Server 2000 server. The reason for this is that after upgrading to SQL Server 2000, and after the users connect using their login, the password is re-encrypted using SQL Server 2000 encryption. Then, CDW moves a SQL Server 2000 encrypted password to a SQL Server 2000 server, hence there is no issue.



WORKAROUND
To work around this behavior, use either of the following methods:  Use the procedure from the following Microsoft Knowledge Base article to move the logins associated with the database that is being copied:

246133 How to transfer logins and passwords between instances of SQL Server

 Log in to the SQL Server 2000 server with a sysadmin account (for example, sa) and manually reset the passwords. You can reset the passwords to any of the following: NULL Passwords of your choice The current passwords, if you know that information.

Note Make sure that you perform this step on a SQL Server 2000 server.



STATUS
Microsoft has confirmed that this is a problem in SQL Server 2000.