Scripting Linked Server Logins and Passwords
-
SQL Server 2000, SP3
Windows 2000, SP4
I'm working on a project where we 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.
> Nick Duckstein -
This is untested, but since you have already hacked the system tables, try this:
In sp_addlogins, there is a section of code:
if @encryptopt = 'skip_encryption_old'
begin
select @xstatus = @xstatus | 0x800, -- old-style encryption
@passwd = convert(sysname, convert(varbinary(30), convert(varchar(30), @passwd)))
end
The meaning of 'skip_encryption_old' is described in Books OnLine as:
The supplied password was encrypted by a previous version of SQL Server. SQL Server should store the value without re-encrypting it. This option is provided for upgrade purposes only.
So try to set xstatus as xstatus | 0x800
Since there is a UNIQUE INDEX on sysxlogins for columns srvid, name, and sid, try:
Update sysxlogins
set xstatus = xstatus | 0x800
where exists
(select 1
from #tsysxlogins
where #tsysxlogins.srvid = sysxlogins.srvid
and #tsysxlogins.name = sysxlogins.name
and #tsysxlogins.sid = sysxlogins.sid
 
SQL = Scarcely Qualifies as a Language
-
I tried that but the login still fails. Instead of exporting to a text file I'm going to export to a SQL Server table and import to that to see if it makes a difference.
I'll let you know how it turns out.
> Nick Duckstein -
Note: the system turned one of my Close parens into a weird happy face.
I have used this script to pull passwords off one server ( the old production server) and put them into the new production server. You of course have to configure your server to allow direct updates to the system tables. The convert(varbinary(255), XX) seems to be the magic command required to get the transffer to work. Just run this on the new server with the old server info name and password Put into the remort rowset querry. I would sugest running this as a select first to see what Logins will be updated.
update sx
set password = convert(varbinary(255), rem_sysxlogins.password)
/*select
* */
from sysxlogins sx
inner join syslogins s on
s.sid = sx.sid
inner join
OPENROWSET
(
'SQLOLEDB',
'severname';'sa';'XXXXXXXXXXX',
'select * from master.dbo.sysxlogins'
 
as rem_sysxlogins onrem_sysxlogins.name = s.name
inner join
OPENROWSET
(
'SQLOLEDB',
'servername';'sa';'xxxxxxxxx',
'select * from master.dbo.syslogins'
) as rem_syslogins on
rem_syslogins.sid = rem_sysxlogins.sid
where
sx.xstatus = 2
-
This is actually the query I want. The sid don't match and xstatus !=2. If I run your query everything BUT logins defined for linked servers. The query below returns the one record I want but with a NULL password.
select ss.srvname,
sx.name,
convert(varbinary(255), rem_sysxlogins.password),
rem_sysxlogins.password
from sysxlogins sx
inner join syslogins s on s.sid = sx.sid
inner join sysservers ss on sx.srvid = ss.srvid
inner join
OPENROWSET
(
'SQLOLEDB',
'devts061\tac';'sa';'xxx',
'select * from master.dbo.sysxlogins') as rem_sysxlogins on rem_sysxlogins.name = s.name
-- inner join
-- OPENROWSET
-- (
-- 'SQLOLEDB',
-- 'devts061\tac';'sa';'xxx',
-- 'select * from master.dbo.syslogins') as rem_syslogins on rem_syslogins.sid = rem_sysxlogins.sid
where 0=0
-- and sx.xstatus = 2
and sx.xstatus = 64
and sx.srvid is not null
and sx.sid is not null
and ss.srvname = 'DEVESQ01'
order by ss.srvname
> Nick Duckstein -
The query I wrote assumes:
1. The logins are already on both servers
2. The logins have the same "Name" on both servers
3. Only the password is transffered
4. It is transffered from the remote comuter that has the correct password, unto the local server where the query is actual run.
I am not sure from your response, but did you get this to work?
-
No I didn't get it to work. All of your assumptions are true except for the logins existing on both servers. They do and they don't.
Let me try explaining the problem a different way. On the ServerA, at some point in the past the following was executed:
EXEC sp_addlinkedserver
@server = 'DEVESQ01',
@provider = 'SQLOLEDB',
@srvproduct = '',
@datasrc = 'DEVESQ01'
EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'DEVESQ01',
@useself = 'false',
@locallogin = 'SAFECO_MASTER\NICDUC',
@rmtuser = 'Test',
@rmtpassword = 'TestPwd'
What I want to do is script out this linked server and login so that I can execute it on ServerB. Everything works great except for scripting out the value for @rmtpassword = 'TestPwd'. The remote login gets added to the linked server properly, but access is denied. If I then type in the password manually, it works fine.
In a nutshell, "Test" exists on
- DEVESQ01 as a SQL Server Login
- ServerA only as a remote login associated with the linked server DEVESQ01
- ServerB only as a remote login associated with the linked server DEVESQ01.
Is that more clear?
Nick
> Nick Duckstein -
Am not quite sure about this. The script I have is just for getting the encpyted password off one server and updating the password on another. We had other scripts as part of the backup and restore process that would transffer everything else, so I wrote this script to move only the actual password. The actual password is stored as binary. If you select it out it looks something like like '0X1A6B735B...'. to get it back into the table once you have matched up the right place to put it, you should be able to update the password like this:
set password = convert(varbinary(255), '0X1A6B735B....')
from sysxlogins sx
where sx.sid = xxx
try this with one password from your existing database by just selecting it out and copying the value. Then put it into the correct sid for the login you are trying to update. You should then be able to login to that server with the copied password. Once you get this to work for one login you should be able to write the script based on how you need to match the data up.
-
I looked at your script and I think I now see the problem:
change this table to
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] [varchar] (256) NULL ,
[dbid] [smallint] NOT NULL ,
[language] [sysname] NULL)
insert into sysxlogins(
srvid ,
sid ,
xstatus ,
xdate1 ,
xdate2 ,
name ,
password ,
dbid ,
language)
select
srvid ,
sid ,
xstatus ,
xdate1 ,
xdate2 ,
name ,
convert(varbinary(255), password) ,
dbid ,
language
from #tsysxlogins
the issue is that when the sever scripts out the password it converts it to a character string. You have to add the code to convert it back.
-
I gave that a whirl and it didn't work either. This time instead of exporting to a text file and importing that, I exported to a table and imported from there.
I tried both ways too, i.e. converting to varbinary and straight across.
You are a trooper for helping me with this.
Any other ideas. Can you get it to work on your end? It is a simple test. Just execute the two commands below on one server.
EXEC sp_addlinkedserver
@server = 'DEVESQ01',
@provider = 'SQLOLEDB',
@srvproduct = '',
@datasrc = 'DEVESQ01'
EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'DEVESQ01',
@useself = 'false',
@locallogin = 'SAFECO_MASTER\NICDUC',
@rmtuser = 'Test',
@rmtpassword = 'TestPwd'
On a second server the sp_addlinkedserver proc exactly the same. Execute the sp_addlinkedsrvlogin as follows:
EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'DEVESQ01',
@useself = 'false',
@locallogin = 'SAFECO_MASTER\NICDUC',
@rmtuser = 'Test',
@rmtpassword = <script from first server>
> Nick Duckstein
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply