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:
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)
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 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.
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:
We can verify the link by querying the new linked database.
And finally, verify permissions.