Urgent : Linked server problem

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)

Urgent : Linked server problem

Author

Topic

The Enigma
Posting Yak Master

179 Posts

Posted - 2004-02-26 : 16:43:39

Again am in a kinda mess ... am shifting from an old server to a new server with lots of disk space .... have migrated the jobs ... backup and restored the db's ... everything seems going fine until I come up to the Linked Servers .... there is one linked server that has been there for a long long time and nobody at this time seems to know the password for that ...

Can I simply move over the records for that server from sysservers and sysxlogins ...

Is it advisable ???

Are there any more tables I need to look into ...

Enigma

X002548
Not Just a Number

15586 Posts

Posted - 2004-02-26 : 17:10:31

Cross post

http://www.dbforums.com/t985520.html

Brett

8-)

The Enigma
Posting Yak Master

179 Posts

Posted - 2004-02-26 : 17:26:19

Cross Member

http://sqlteam.com/forums/pop_profile.asp?mode=display&id=7198

http://www.dbforums.com/member.php?action=getinfo&userid=18805

Enigma

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-26 : 18:22:02

Enigma,

Did the dbforums solution work for you?

Tara

The Enigma
Posting Yak Master

179 Posts

Posted - 2004-02-26 : 18:35:05

Nope Tara ... the password is not being accepted ... seems strange ... I didnt think sql 2000 had that much security

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-26 : 19:10:41

Maybe putting the password in a variable would help:

DECLARE @pwd SYSNAME

SELECT @pwd = password
FROM OldServer.master.dbo.syslogins
WHERE ...

UPDATE NewServer.master.dbo.syslogins
SET password = @pwd
WHERE ...

That's what I do to transfer logins at least. See this code that transfers logins from one machine to the next (it uses linked servers but at least it'll give you an idea):


CREATE PROCEDURE isp_Transfer_Logins
AS

SET NOCOUNT ON

DECLARE @login sysname
DECLARE @pwd sysname
DECLARE @new_pwd varchar(255)

DECLARE cur_Users CURSOR FOR
SELECT l.name, l.password
FROM master.dbo.syslogins l
INNER JOIN DTS.dbo.sysusers u ON l.sid = u.sid
WHERE (l.isntname = 0) AND (u.islogin = 1 AND u.isaliased = 0 AND u.hasdbaccess = 1)
ORDER BY u.name

OPEN cur_Users

FETCH cur_Users INTO @login, @pwd

WHILE @@FETCH_STATUS = 0
BEGIN
	-- If the login does not exist on the destination server, then add it.
	IF ((SELECT count(*) FROM SDDEVSQL1.master.dbo.syslogins WHERE name = @login) = 0)
	BEGIN
        	EXEC SDDEVSQL1.master.dbo.sp_addlogin @loginame = @login, @passwd = @pwd, @encryptopt = skip_encryption, @defdb = 'QTRACS'
	END

	-- If the login does exist on the destination server, then synchronize the password.
	ELSE
	BEGIN
		EXEC SDDEVSQL1.master.dbo.sp_droplogin @login
		EXEC SDDEVSQL1.master.dbo.sp_addlogin @loginame = @login, @passwd = @pwd, @encryptopt = skip_encryption, @defdb = 'QTRACS'
	END


        FETCH cur_Users INTO @login, @pwd
END

CLOSE cur_Users
DEALLOCATE cur_Users

RETURN
GO

Tara

The Enigma
Posting Yak Master

179 Posts

Posted - 2004-02-26 : 19:24:18

Tara ... the syslogin has the field password as nvarchar while sysxlogins has field as varbinary

Moreover .. both use different encryption techniques ...
syslogins uses pwdencrypt function while sysxlogins uses encrypt function ...

so there's something different about encryption of a linked server login which i am not able to point out

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-26 : 19:27:06

But you don't need to decrypt the password. As a matter of fact, you probably can't. Just store the value in a variable. Then UPDATE the table using the variable.

Tara

The Enigma
Posting Yak Master

179 Posts

Posted - 2004-02-26 : 19:31:14

exec sp_configure 'allow updates', 1
reconfigure with override
go

DECLARE @pwd varbinary(256)

SELECT @pwd = password
FROM ndelntx5.master.dbo.sysxlogins
WHERE name = 'system'

UPDATE sysxlogins
SET password = @pwd
WHERE name = 'system'

This doesnt work ...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-26 : 19:33:53

You might consider running the stored procedure that I posted then to transfer the login. It works great in SQL 2000. If the login already exists, it drops it and recreates it. If it doesn't exist, it just creates it. This ensures that the passwords are in synch.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources