locked
where does SQL record the linked server login credentials when creating a linked server RRS feed

  • Question

  • I'm trying to understand where does SQL record the linked server login credentials when creating a linked server
     
    EXEC master.dbo.sp_addlinkedserver @server = N'xxxx, @srvproduct=N'SQL Server'

    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'xxx',@useself=N'False',@locallogin=NULL,@rmtuser=N'loginname',@rmtpassword='password

    I've looked into sp_addlinkedsrvlogin and what it is trying to do when adding a new login.
     
    and I narrowed it down to

    EXEC%%LinkedServer(Name=@rmtsrvname).RemoveLinkedLogin(LocalID=@localid)

     

    SELECT @pwd = convert(varbinary(256), @rmtpassword)

    EXEC %%LinkedServer(Name=@rmtsrvname).NewLinkedLogin(

    LocalID=@localid, UseSelf=@useselfbit, RemoteName=@rmtuser, Password=@pwd)

     
     
    however don't understand what these statments do as they are not normal TSQL statements.
    Anyone has got any idea?
    Regards,
    Shaunt
    • Moved by Tom Phillips Thursday, March 7, 2013 5:16 PM Probably better answer from Security forum
    Wednesday, December 12, 2012 11:32 AM

All replies

    1. Linked server information is stored in the sys.servers.  Information can be found here: http://msdn.microsoft.com/en-us/library/ms178530(v=sql.105).aspx
    2. Linked server logins are stored in sys.linked_logins.  Information can be found here:http://msdn.microsoft.com/en-us/library/ms188018(v=sql.105).aspx
    3. Linked server remote logins are stored in sys.remote_logins.  Information can be found here: http://msdn.microsoft.com/en-us/library/ms177584(v=sql.105).aspx
    Wednesday, December 12, 2012 11:49 AM
  • Thanks Justin,

    however that was the obvious place to look before raising the question. I'm interested in where the password is stored!

    Wednesday, December 12, 2012 11:55 AM
  • The password is not visible through catalog views.  If you are using a SQL login you can associate the sys.linked_logins remote_name field with the sys.server_principals name field and then join against the sys.sql_logins where you find the password_hash.
    Wednesday, December 12, 2012 12:44 PM
  • it won't work as the local_principal_id column value for login used by linked server in sys.linked_logins is 0 therefore  no relevant login can be found  

    in sys.sql_logins  and  sys.server_principals 

    but the password is stored somewhere as the linkedserver works

    I'm trying to understand what 

    SELECT @pwd = convert(varbinary(256), @rmtpassword)

    EXEC %%LinkedServer(Name=@rmtsrvname).NewLinkedLogin(

    LocalID=@localid, UseSelf=@useselfbit, RemoteName=@rmtuser, Password=@pwd)

    does which is part of sp_addlinkedserver procedure

    Wednesday, December 12, 2012 1:03 PM
  • The linked server is connecting remotely to another SQL instance.  You want to view the sys.server_principals on the remote instance (not the local instance) and use the remote_name column.  local_principal_id column will not help you here.  If your linked server is connecting properly you can find the password_hash on the remote instance.  You won't find the password in clear text in any catalog views.
    Wednesday, December 12, 2012 1:07 PM
  • that makes sense, 

    I'm trying to recreate the linked server on an another server, without manually entering the password (using tsql ) - as I want to automate this process to be rolled out on other severs.

    the same way that I can script logins with the hash value. 

    Wednesday, December 12, 2012 1:34 PM
  • Interesting problem.  Rather than use the remote login and password when creating the linked server, why not use login mappings.  Create an identical login on the local and remote server using the password hash and then impersonating the local user remotely via sp_addlinkedsrvlogin?  I have not personally tried this but you can eliminate the need to enter an unknown password when creating the linked server on the new sql instance.
    Wednesday, December 12, 2012 1:55 PM
  • I agree again but these are historic servers and most of the passwords been forgotten!! 
    Wednesday, December 12, 2012 2:15 PM
  • but still knowing the SQL stores the password for the linked server local using

    EXEC %%LinkedServer(Name=@rmtsrvname).NewLinkedLogin(

    LocalID=@localid, UseSelf=@useselfbit, RemoteName=@rmtuser, Password=@pwd)

    but I need to understand how that works!

    Wednesday, December 12, 2012 2:17 PM
  • rmtpassword is stored  at C:\Windows\System32\config\software
    Tuesday, February 26, 2013 9:15 AM
  • Richard,

    Do you have some way to inspect that file and extract it?  When I try to copy it I find I can't and attempting to open it with something like NotePad++ does not work either.

    Joe


    Joe Moyle

    Thursday, March 7, 2013 5:09 PM
  • That's some an internal mumbojumbo which is undocumented.

    You can find the password hash in master.sys.syslnklgns. You need to login through the admin connection to be able to access this table.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, March 7, 2013 10:47 PM
  • I just analyzed the SQL Server 2000 encryption, which is a pretty weak xor-table-cryption.

    Here's my procedure to decrypt them:

    CREATE PROCEDURE decrypt_pwd (@loginame nvarchar(128),@srvid smallint=NULL,@plain_pwd nvarchar(128)='' OUTPUT)
    AS
    BEGIN
    	EXEC sp_addlinkedserver N'q9XzW',N'SQL Server'
    	DECLARE @pwd nvarchar(128);
    	SET @pwd=NCHAR(256) -- 0x0001
    	EXEC sp_addlinkedsrvlogin 'q9XzW', 'false', NULL, 'jY3q0',@pwd;
    
    	DECLARE @orig_pwd varbinary(256)
    	SELECT @orig_pwd =CONVERT(varbinary(256),rmtpassword) FROM master.dbo.sysoledbusers WHERE rmtloginame=@loginame AND rmtsrvid=ISNULL(@srvid,rmtsrvid)
    
    	DECLARE @xor_key int;
    	SELECT @xor_key=SUBSTRING(CONVERT(varbinary(256),rmtpassword),1,1) FROM master.dbo.sysoledbusers WHERE rmtloginame='jY3q0'
    
    	DECLARE @plain varbinary(256);
    	SET @plain=0x
    
    	DECLARE @i int
    	SET @i = 1
    
    	WHILE(@i <= DATALENGTH(@orig_pwd))
    	BEGIN
    		SET @plain=@plain+CONVERT(BINARY(1),SUBSTRING(@orig_pwd,@i,1)^@xor_key)
    		SET @pwd=CONVERT(nvarchar(128),@plain+CASE WHEN @i%2=0 THEN 0x00 ELSE 0x END);
    
    		EXEC sp_addlinkedsrvlogin 'q9XzW', 'false', NULL, 'jY3q0',@pwd;
    		SET @i=@i+1
    		SELECT @xor_key=SUBSTRING(CONVERT(varbinary(256),rmtpassword),@i,1) FROM master.dbo.sysoledbusers WHERE rmtloginame='jY3q0'
    	END
    
    	EXEC sp_dropserver 'q9XzW','droplogins'
    
    	SET @plain_pwd=CONVERT(nvarchar(128),@plain)
    END

    Thursday, July 18, 2013 12:40 AM
  • You saved my life
    Monday, April 27, 2015 8:29 PM