Connecting Linked Servers on a new MSSQL

I was setting up a second MSSQL server for a client and hit a problem with configuring Linked Servers on it. The first server had two linked MSSQL instances. The sa account was used for the connection.

In principle there is nothing hard about dumping the info about these servers into the query window. Look at the screenshot:

I think the sequence of actions in Management Studio is clear and there is no need to write it all down step by step.

The main dirty trick is that the passwords of the users will be shown as ######, which is what this message at the very top of the query window tells you:

/* For security reasons the linked server remote logins password is changed with ######## */

And indeed the line with the user looks like this:

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'_mssqlserver.net_',@useself=N'False',@locallogin=NULL,@rmtuser=N'sa',@rmtpassword='**########**'

To get hold of the passwords we use this Powershell script

As an option you can download the ready file here

Do not close SQL Management Studio.

Upload the file to the first server. (unpack it if needed).

Start PowerShell with administrator rights

In the console type:

Set-executionpolicy unrestricted

We get an ugly thing in response:

Press Enter while looking it in the eyes.

Then import the uploaded script:

import-module C:\Get-MSSQLLinkPasswords.psm1

Run the function:

Get-MSSQLLinkPasswords

In response we get a small table with the linked servers, the users and the passwords.

Go back to SQL Management Studio and copy the content of the Query Editor window. Connect to the second server with Management Studio. With the second server selected, create a new query window and paste the content of the clipboard into it.

Find the line that looks like this:

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'_mssqlserver.net_',@useself=N'False',@locallogin=NULL,@rmtuser=N'sa',@rmtpassword='**########**'

Change ######## to the password of the user you need. Press Execute.

Ta-da: the new server shows up in the list.