Menu & Search
Configuring Linked Servers in SQL Server

Configuring Linked Servers in SQL Server


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

This is a quick guide on how to link one SQL Server instance to another, which will include the following:

# Test Connectivity to Remote Server
# Create Linked Server Login
# Create Linked 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.

Test-NetConnection PowerShell

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 Linked Server Login

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.

Create Linked Server Login

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

Linked Server Login Details

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

Linked Server Login Permissions

Once we click OK the account is ready for use.


Create Linked Server

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

Create Linked Server SSMS

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

New Linked Server

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

New Linked Server Login SSMS

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

Create Linked Server SQL Script

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.

Query Linked Server

And finally, verify permissions.

Linked Server Permission Denied