Copy Linked Server Password

Is there a way in SQL2005 to copy the linked server password from SQL2000?

We are about to copy our SQL 2000 database to SQL 2005 (and change operating systems and make other changes to the system). We can't do an upgrade in place because of the other changes, plus we want to have the original in case we can't get it updated before the start of business the next day.

I plan to move the databases by copying the files and attaching them, and I have a program that pulls users and encrypted passwords and adds them to the new system without encrypting the already encrypted passwords. Jobs I can script and run on the new system, DTS jobs I can open and save to the new system, and linked servers I can get the names of and link on the new server.

In SQL 2000 I would have "cheated" and copied the "DTS" and "Jobs" tables from one server to the other, but SQL 2005 has made it more complicated, so I will do it the slower way.

The problem comes with the linked server passwords. While I can see the linked server data in the master system tables, including the names and that there is an encrypted password. The problem is I don't know the passwords, so I can't enter it myself, and I can't update the system table, so I can't insert it as is. And sp_addlinkedsrvlogin does not have an option to not re-encrypt the password (like @.encryptopt = 'skip_encryption' in sp_addlogin).

Is there a way to figure out the password, or copy/insert the password without having it re-encrypt? In SQL2000 I would have done:

update A set
name = b.name,
password = b.password
from sysxlogins A, sysxlogins B
where A.srvid = 22
and B.srvid = 11

But SQL 2005 will not allow you to do this kind of update (allow updates has no effect).

Thanks, Tim

PS - Anything I might have missed that anyone can suggest, or any faster method of copying the database? We have a limited maintenance window to do this upgrade, so anything I can do ahead of time will help. Thanks again. Tim

Unfortunately, there is no supported method for doing this. You will have to recreate and respecify the passwords for those accounts.

Thanks
Laurentiu

|||Or, consider copying (backing up/restoring) everything to another machine and upgrading there from SQL Server 2000 to SQL Server 2005.