migrate linked servers with passwords

This is one that I've seen asked often but without a definitive answer:
Is it possible to migrate linked servers including passwords to a different
server. I can get this to work on the same server with the script below but
I
can't get it to work on a different server.
--set up
sp_addlogin 'remotelogin','password'
go
use northwind
go
sp_grantdbaccess 'remotelogin'
go
sp_addrolemember 'db_datareader', 'remotelogin'
go
sp_addlinkedserver @.server='TestLinkedServer',
@.srvproduct='',@.provider='SQLOLEDB', @.datasrc='ServerName'
go
sp_addlinkedsrvlogin 'TestLinkedServer', 'false', NULL, 'remotelogin',
'password'
go
select count(1) from TestLinkedServer.Northwind.dbo.Customers
--get password and paste below
select password from master.dbo.sysxlogins
where name = 'RemoteLogin' and xstatus = 64
--delete linked server
exec sp_dropserver 'TestLinkedServer' , 'droplogins'
go
EXEC sp_configure 'allow updates', '1'
RECONFIGURE WITH OVERRIDE
go
sp_addlinkedserver @.server='TestLinkedServer',
@.srvproduct='',@.provider='SQLOLEDB', @.datasrc='ServerName'
go
sp_addlinkedsrvlogin 'TestLinkedServer', 'false', NULL, 'remotelogin',
'AnyPassword'
go
select count(1) from TestLinkedServer.Northwind.dbo.Customers
update master.dbo.sysxlogins
set password = 0xDEB389AB6A4C1DAEC599EF26C3392578
where name = 'RemoteLogin' and xstatus = 64
select count(1) from TestLinkedServer.Northwind.dbo.Customers
EXEC sp_configure 'allow updates', '0'
RECONFIGURE WITH OVERRIDE
go
--drop
exec sp_dropserver 'TestLinkedServer' , 'droplogins'
go
use northwind
go
EXEC sp_revokedbaccess 'remotelogin'
go
EXEC sp_droplogin 'remotelogin'
goHi
It is unlikely to work on other servers as the encryption key will differ
between them. if you know the passwords or used windows authentication it
would not be an issue.
John
"DBA72" wrote:

> This is one that I've seen asked often but without a definitive answer:
> Is it possible to migrate linked servers including passwords to a differen
t
> server. I can get this to work on the same server with the script below bu
t I
> can't get it to work on a different server.
> --set up
> sp_addlogin 'remotelogin','password'
> go
> use northwind
> go
> sp_grantdbaccess 'remotelogin'
> go
> sp_addrolemember 'db_datareader', 'remotelogin'
> go
> sp_addlinkedserver @.server='TestLinkedServer',
> @.srvproduct='',@.provider='SQLOLEDB', @.datasrc='ServerName'
> go
> sp_addlinkedsrvlogin 'TestLinkedServer', 'false', NULL, 'remotelogin',
> 'password'
> go
> select count(1) from TestLinkedServer.Northwind.dbo.Customers
> --get password and paste below
> select password from master.dbo.sysxlogins
> where name = 'RemoteLogin' and xstatus = 64
> --delete linked server
> exec sp_dropserver 'TestLinkedServer' , 'droplogins'
> go
>
> EXEC sp_configure 'allow updates', '1'
> RECONFIGURE WITH OVERRIDE
> go
> sp_addlinkedserver @.server='TestLinkedServer',
> @.srvproduct='',@.provider='SQLOLEDB', @.datasrc='ServerName'
> go
> sp_addlinkedsrvlogin 'TestLinkedServer', 'false', NULL, 'remotelogin',
> 'AnyPassword'
> go
> select count(1) from TestLinkedServer.Northwind.dbo.Customers
>
> update master.dbo.sysxlogins
> set password = 0xDEB389AB6A4C1DAEC599EF26C3392578
> where name = 'RemoteLogin' and xstatus = 64
> select count(1) from TestLinkedServer.Northwind.dbo.Customers
>
> EXEC sp_configure 'allow updates', '0'
> RECONFIGURE WITH OVERRIDE
> go
> --drop
> exec sp_dropserver 'TestLinkedServer' , 'droplogins'
> go
> use northwind
> go
> EXEC sp_revokedbaccess 'remotelogin'
> go
> EXEC sp_droplogin 'remotelogin'
> go