Linked Servers in SQL Server allow you to query external databases, such as remote SQL Server instances, Oracle, ODBC or MS Access databases.

This blog post contains a demo on how to create a Linked Server to another SQL Server, with links to Microsoft Documentation throughout.

I also have more Linked Server demos if of interest:
-> Create a Linked Server with a MySQL Database
-> Create a Linked Server with a Postgres Database

The following steps should help walk you through creating a Linked Server to another MS SQL Server:
# Test Connectivity to Remote Server
# Create a SQL Login for Linked Server
# Create a Linked Server to SQL Server

Test Connectivity to Remote Server

An important step before we begin any SQL Server stuff, we should confirm line-of-sight to the remote server. On this local setup, I have nothing to worry about, but if configuring this in the real world, you may have to arrange network rules to be added.

The network information we’ll need is the server address (IP Address or FQDN) and port number. The SQL Server default port is 1433, so we just need the server address to test the connection using PowerShell’s Test-NetConnection cmdlet.

The SQL Server default port is 1433, so we just need the server address to test the connection using PowerShell’s Test-NetConnection cmdlet.

Both the Ping and TCPTest failed as expected.

Use this script if running PowerShell on an OS older than Windows 8/Windows Server 2012.

Other note-worthy default database server ports include:
# PostgreSQL (5432)
# MySQL (3306)
# Oracle (1521)
# Sybase (5000)
# DB2 (50000)

Create a SQL Login for the Linked Server

We need an account on the server we’re linking to. I’m using my SQL Server 2019 (PETE-PC\SQL2019) as my master server, creating the new login on the PETE-PC instance as shown on the image below.

I’m selecting SQL Server authentication as I’m not on a domain and unticking the password reset on the next login.

I’ll test a read-only case by selecting db_datareader on the AdventureWorks database.

Once we click OK the account is ready for use.

Create a Linked Server to SQL Server

Open Server Objects, right-click Linked Servers and select New Linked Server.

Select SQL Server as the server type and enter the ‘remote’ server name. 

Into the Security tab next, enter the remote login details (as created above).

We can hit OK, or script it out before running. This is a condensed version.

TSQL can be found here.

The 3 system stored procedures used are:

# sp_addlinkedserver
# sp_serveroption
# sp_addlinkedsrvlogin

We can verify the link by querying the new linked database.

And finally, verify permissions.