Understanding SQL Server Linked Servers

By: Eric Blinn   |   Updated: 2022-04-07   |   Comments (7)   |   Related: > Linked Servers

Problem

I see there is an option in SQL Server Management Studio (SSMS) for Linked Servers and I want to learn more about what they do and how they work.

Solution

Linked Servers are a method by which a SQL Server can talk to another ODBC compliant database, such as another SQL Server instance or an Oracle database, with a direct T-SQL query.

There are several important settings to know when setting up a Linked Server. This tip will cover creating a Linked Server to another SQL Server instance using the SSMS GUI along with appropriate security options. It will also show how to use the Linked Server in a T-SQL statement. There is a great library of more advanced topics available once the basics have been mastered.

Creating a SQL Server Linked Server

In SQL Server Management Studio, in the Object Explorer pane, expand the "Server Objects" section, right click on "Linked Servers" and choose "New Linked Server…" from the menu.

New Linked Server - General Page

That will bring up a screen that looks like the screen below. Choose the server type as SQL Server and type in the name of the other instance of SQL Server. In this example the connection will be made to a named instance of SQL Server on the same machine. This is certainly allowed, but more commonly the connection is made to another machine entirely. The Linked Server name is specified as the ServerName\InstanceName.  In the example below I am connecting to the local server and the instance name is "SECURITY_TEST".

New Linked Server - Security Page

Next, move from the General page to the Security page using the menu on the left. This page allows the administrator to tell this SQL Server how to authenticate to the other instance. The top half of the screen allows for individual logins to be given access to use the Linked Server.

There are 2 methods to authenticate users. The first is to select a login for them to use when using the Linked Server. In the screenshot below the Eric user will automatically log in to the secondary server as the login LinkedServerTest on the other instance. This credential is stored within the SQL Server Engine and is encrypted. One the second row, the LinkedServerTest login (a SQL Login) the Impersonate box is checked. For a SQL account, this tells SQL Server to attempt to log in using the same username and password on the Linked Server as it did on the original server. Impersonation of a Windows Login can also be done but requires proper Kerberos, SPNs, and proper delegation rules be set up on the domain.

Now focus on the bottom half of the screen. There are 4 options available. Whichever option is selected here will apply to any user not listed in the top section.

New Linked Server - Server Options Page

There are also settings on the Options page, but for simple queries these settings can be left as the default values as shown below.

The settings do not need to be changed in most use cases and this table will explain what the settings do.

exec sp_configure 'remote login timeout (s)'

exec sp_configure 'remote query timeout (s)'

Querying Data Over a SQL Server Linked Server

To read data from a Linked Server any tables or views must be referenced using a 4-part identifier consisting of the Linked Server name, database name, schema name, and object name – in that order.

For example:

SELECT * FROM [.\SECURITY_TEST].master.sys.databases

SELECT * FROM [.\SECURITY_TEST].WideWorldImporters.Sales.Orders	

• First the Linked Server name is provided which is [.\SECURITY_TEST] for our example. In this example it must be bracketed due its format. Not all Linked Server names must be bracketed.
• Next is the database name, master and WideWorldImporters respectively.
• Third is the schema name, sys and Sales respectively.
• Lastly, the object name is listed. In these examples the objects are databases and Orders.

That example is pretty ugly due to the nature of the Linked Server name. Luckily, this great tip shows how to make the names easier to read in scenarios like this.

Once listed in a FROM clause these tables and views can be treated in the exact same way any as other local table.  They can be queried, aliased, joined, and if proper permissions are set, they can be modified using insert, update, and delete statements.

Calling Stored Procedures Over a Linked Server

For the purposes of this demo, this small procedure was created in the master database on the target linked server.

USE master
GO

CREATE PROCEDURE dbo.SQLTips1 
AS 
SELECT 'I was able to be called'; 

The procedure is then executed on the primary instance using this command which follows the same 4 part identifier schema as the statements in the prior section.

EXEC [.\Security_Test].master.dbo.SQLTips1			

However, instead of the expected result an error is raised.

RPC stands for Remote Procedure Call.  By default, Linked Servers are not able to make stored procedure calls.  This is easily remedied by enabling a single setting on the Linked Server.  Browse to the Linked Server properties, choose Server Options and the 4^th option in the list will be RPC Out.  Simply change that value to True and click OK.

The setting labeled RPC is NOT the value that needs to be changed.  This can seem confusing since the error message seems to name it specifically, but that is actually a legacy setting that does not enable RPC calls.

Rerunning the procedure now produces a much better result.

SQL Server Linked Server Limitations

Linked Servers can be very useful, but there are a few limitations of which to be aware before implementing them.  Linked Servers cannot be used to call table valued or scalar functions.  Also, the truncate command is not allowed to be called using a Linked Server.

SQL Server Linked Server Use Cases

The performance of Linked Servers is not always great, especially with large data sets and lots of joins between local and linked tables. But they can be very useful for querying smaller datasets.

Sometimes an ETL requirement for a smaller dataset can be met using a Linked Server rather than an ETL tool like SSIS. When the rows involved number in the thousands or lower 10s of thousands a Linked Server based ETL might be able to complete in less time than it takes to start the SSIS engine.

Next Steps

• There is a whole family of advanced Linked Server tips on MSSQLTips.com
• Making Linked Server names easier to read

About the author

Eric Blinn is the Sr. Data Architect for Squire Patton Boggs. He is also a SQL author and PASS Local Group leader.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips