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.
REFERENCES
For more information about the login transfer, click the following article number to view the article in the Microsoft Knowledge Base:
249671 PRB: Passwords unusable after SQL Server 6.5 to 7.0 upgrade with different sort order
For more information about the SQL Server 2000 Copy Database Wizard, click the following article number to view the article in the Microsoft Knowledge Base:
274463 Understanding and troubleshooting the Copy Database Wizard
Keywords: kbbug kbpending KB285025
