Linked Server Modification

• Terry B-214928

  Say Hey Kid

  Points: 696

  More actions

  September 23, 2020 at 7:58 pm

  #3790470

  I have a linked server and need to change only the @svrproduct and the @datasrc.  I cannot figure out how to do this.  I do not want to drop the object and recreate.

  @server = N'EAUTO', @srvproduct=N'1.2.3.4', @provider=N'SQLNCLI', @datasrc=N'1.2.3.4'

  Thanks,

  Terry

  A great day starts with a great attitude

• Site Owners

  SSC Guru

  Points: 80342

  More actions

  September 24, 2020 at 8:10 pm

  #3790812

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  September 24, 2020 at 10:22 pm

  #3790834

  There is no supported procedure to change those - dropping and recreating with the updated information is the supported method. I would guess you could hack the system table to update the information sys.servers but you'd have to search for how to do that as it's not something I would post. And it's really, really, really not recommended. But the only reason I can think of for not wanting to drop and recreate would be if you didn't have the password for a login being used and creating the a new login and password or resetting the existing password on the other data source  may not be an option. If that's the case, there are a few powershell scripts on the internet to get linked server passwords or script them out with passwords. I haven't tried it on 2016 but it did work on 2014 and earlier versions. I would test that method before trying to hack system tables. All the scripts I've seen to get the linked server passwords use the same method from this post:

  Decrypting MSSQL Database Link Server Passwords

  If you do a search on something like "linked server password recovery", you can find some of the ways people have used that Powershell script.

  Sue

• webrunner

  SSC-Dedicated

  Points: 32020

  More actions

  September 29, 2020 at 4:24 pm

  #3791704

  Hello,

  You could try some of the ideas mentioned here:

  https://stackoverflow.com/questions/11866079/changing-properties-of-a-linked-server-in-sql-server

  HTH.

  -- webrunner

  -------------------
  A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
  Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html