Copying Linked Server Logins and Passwords

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Copying Linked Server Logins and Passwords

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-03-03 : 08:19:00

Nick Duckstein writes "SQL Server 2000, SP3
Windows 2000, SP4

I'm working on a project where upgraded SQL Server from one box to the next. We didn't want to retore master to the new box because we were suspect of problems in master.

We were able to script out all the logins and reattach databases. We also scripted out all the linked servers and added those as well.

The problem comes when attempting to run the scripts to restore the linked server logins. Everything works great except for the passwords.

The sp_addlinkedsrvlogin doesn't allow for an encrypted password. I can probably dig the passwords up and enter them in manually but I wanted to script the whole thing out.

I also tried exporting sysxlogins data to a text file using the following query in a DTS package from the old server:

select l.srvid,
l.sid,
l.xstatus,
l.xdate1,
l.xdate2,
l.name,
l.password,
l.dbid,
l.language
from sysxlogins l
inner join sysservers s on l.srvid = s.srvid
where l.srvid is not null
and l.sid is not null
and s.srvname != @@SERVERNAME

Then I reimported them into the new server using the following code.

create table #tsysxlogins (
[srvid] [smallint] NULL ,
[sid] [varbinary] (85) NULL ,
[xstatus] [smallint] NOT NULL ,
[xdate1] [datetime] NOT NULL ,
[xdate2] [datetime] NOT NULL ,
[name] [sysname] NULL ,
[password] [varbinary] (256) NULL ,
[dbid] [smallint] NOT NULL ,
[language] [sysname] NULL
)

--Look at options for BULK INSERT
BULK INSERT #tsysxlogins
FROM '\\devts061\e$\nicduc\RemoteLogins.txt'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n'
)

--(I made sure the srvid matched in both server first)

EXEC sp_configure 'allow updates', '1'
go
RECONFIGURE WITH OVERRIDE
go
insert into sysxlogins
select * from #tsysxlogins
go
EXEC sp_configure 'allow updates', '0'
go
RECONFIGURE WITH OVERRIDE

The remote logins get added to the correct linked servers but the access fails. I know it a password issue because if I change the password manually it works fine.

Any help would be much appreciated."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources