Here I go again

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 seems to know the remote login and 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 ...I don't know if I'd mess with the system tables...

How long does it take to build the new box...everything all scripted out?

Doing a dump and restore ?

How many linked servers do you have?

Is any process even using the server?

As part of this migration did you do any housekeeping (like getting rid of unused links)?|||And are you in the office?

What the hell time is it over there?|||Can you clone the user on the remote system, with a new password? To me that would be easier. I have not played with linked servers, since they are such a pain to use. Especially when you try to move code from a QA server to a prod server. Too easy to hard code names in there.|||Yup .. I am in the office .. time is just 3:21 AM ...

And yes .. the linked server is being used for a table that is being transferred daily at 7 AM ...

Right now ... nobody is around who can provide me with the password ...

so any suggestions ?|||-Usa -Pblank?

-Usa -PServernme

-Usa -PBoss's daughters name

I'm suprised they're not all the same....

Do you know the other ones? Di d you try any of those?|||The Linked server is at a different location ... and its a oracle database ...

The userid is system as i can make out from sysxlogins ...
password :(|||Wake them up!|||Originally posted by blindman
Wake them up!

Certainly more helpful then my suggestions...

Is this new box have to ready for the 7:00am dump?

Or is the old box still handling the load?

In other words did you cut over already?|||There may be a way, but it is very dangerous. I hope you have admin access to the SQL Server you are dealing with?

Step one

select password from sysxlogins where name = 'remote user's name'
copy this result to the next query

On the server you want to work

exec sp_configure 'allow updates', 1
reconfigure with override
go
update sysxlogins
set password = 0x130270E06743495DE22414DAFA0CAA12
where name = 'Remote User's name' -- <-- This is EXTREMELY IMPORTANT
go
exec sp_configure 'allow updates', 0
reconfigure with override

Forgive me for being a little emphatic about the where clause on that update query, but I know how I get at 3:00 in the morning.

This depends on you being able to add the login in the first place, of course.|||I'm LOL...mostly out of fear...

TIMBER

I'm going to go home and slip in to a margaritta...

Good Luck Enigma...I'll logon from there to see how it's going...|||I agree, Brett. I would be nervous doing this myself, but it may be that Enigma has little to no choice. Unless he is able to wake up one of the guys he is workinig with. Hmm. 3 AM...Hong Kong, maybe?|||Hong Kong? No, Enigma is what we Yanks call a "Red Coat".|||Did the following :
Created a linked server from newserver to oldserver ...

exec sp_configure 'allow updates', 1
reconfigure with override
go
update sysxlogins
set password = (select distinct password from sysxlogins where lname ='SYSTEM')
where name = 'System'
go
exec sp_configure 'allow updates', 0
reconfigure with override

Didnt work out :(

OLE DB provider 'MSDAORA' reported an error. Authentication failed.
[OLE/DB provider returned message: ORA-01017: invalid username/password; logon denied
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].|||By the way, Enigma. Did you take your username from the famous code machine deciphered by Turing at Bletchley Park during WWII? Or are you just hard to figure out?|||Yup ... saw the movie once ...

Have been using this nick for a long time though ... now i dont even remember where i got this from :D|||Sorry to hear it did not work out. So you must have missed the 7 AM load. Are any of the regulars there able to help you clone the user with a known password, or get the old password, then?|||Will get the password only on monday ... when I get to talk to the client IT guy ...

Till then .. Only thing I can do is Enjoy !!! :)

Bring on the Whiskeys :D|||You can try to restore MASTER database from the old server to your new one under a different name of course, and then instead of creating a linked server/remote login on the new one - just insert...select from master_old.dbo...|||...make sure you do that for sysservers as well as sysxlogins. Of course the local login should exist before you do that.|||I have not tried that in a while, rdjabarov, but I remember it complained that master can only be restored as "master". I guess that keeps passwords safer, in case someone happens to steal a backup of your master database. But, like I say, it has been a long time since I tried that.|||Originally posted by blindman
(...) famous code machine deciphered by Turing at Bletchley Park during WWII? (...)
Just to be clear - historicians have problems to agree who broke Enigma code. Some historians say three Polish mathematicians - Marian Rejewski, Jerzy Rozycki and Henryk Zygalski - broke the Enigma code in 1933. In July 1939, just before the war, Poland gave Britain and France replicas of the Enigma encoding machine, helping the Allies decipher secret Nazi messages. According to British accounts, British cryptographers cracked the code a year before war broke out.

Movies aren't best source for infos like this.|||http://www.pbs.org/wgbh/nova/decoding/enigma.html|||Originally posted by Brett Kaiser
http://www.pbs.org/wgbh/nova/decoding/enigma.html

http://www.pan.net/history/enigma/|||Originally posted by MCrowley
I have not tried that in a while, rdjabarov, but I remember it complained that master can only be restored as "master". I guess that keeps passwords safer, in case someone happens to steal a backup of your master database. But, like I say, it has been a long time since I tried that.

You must have been trying to do something...Enigmatic...because it works like a charm. Do yourself a favor and try it :cool:|||Originally posted by MST78
Just to be clear - historicians have problems to agree who broke Enigma code. Some historians say three Polish mathematicians - Marian Rejewski, Jerzy Rozycki and Henryk Zygalski - broke the Enigma code in 1933. In July 1939, just before the war, Poland gave Britain and France replicas of the Enigma encoding machine, helping the Allies decipher secret Nazi messages. According to British accounts, British cryptographers cracked the code a year before war broke out.

Movies aren't best source for infos like this.

well ... now i remember ... i kept the nick when I broke the Enigma code ... how could I forget that !!! :D|||Score one for rdjabarov. You can restore a clone of the master database. I wonder what version I tried that on last...|||Originally posted by MCrowley
Score one for rdjabarov. You can restore a clone of the master database. I wonder what version I tried that on last...

Well ... Its been a long long day again and what i find is that even that does not work ... I mean .. I can restore master backup as another database ... but it seems that the passwords for the linked server are encrypted using some algo that takes the machine config... or something like that into account ..

Tara (http://www.sqlteam.com/forums/pop_profile.asp?mode=display&id=6645) at www.sqlteam.com provided me with this code to transfer logins from one server to another .. but ... this code does not work for linked servers .. probably because sp_addlinkedsrvlogin uses the function encrypt to encrypt the passwords and sp_addlogin uses pwdencrypt ... and there seems to be a lot of diff between the two ...



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?

Who's she?|||Originally posted by Brett Kaiser
Tara?

Who's she?

I dont know ... maybe someone else would be able to answer ...maybe some body called X002548 (http://www.sqlteam.com/forums/pop_profile.asp?mode=display&id=7198)|||Originally posted by Enigma
well ... now i remember ... i kept the nick when I broke the Enigma code ... how could I forget that !!! :D
Daddy?? ;)|||My Son !!!! where were you till now ;D|||You guys are WEIRDING ME OUT!