Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Understanding SQL Server Linked Servers


By:   |   Last Updated: 2019-06-28   |   Comments   |   Related Tips: More > 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.

This screenshot shows the location of the New Linked Server... object within the menu as described in the text.

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".

On the wizard window that pops up there is a box that says Linked Server.  In that box type in the name of another instance of SQL Server.  There is a radio button to choose a type.  In this example we are choosing the SQL Server radio button.

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. This will attempt to use 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 delegation be set up.

This screenshot shows the top half of the security page with 2 rows filled out.  The first maps a windows login to a SQL Login and the second tells a SQL Login to impersonate.

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.

This screenshot shows the 4 radio button options available as described in the table below.
Option Description
Not be made This will give an error to any user attempting to use the Linked Server that isn’t in the list above. In order to select this option, there must be at least 1 login in the top half of the screen.

This is the error all other users will get.

Msg 7416, Level 16, State 1, Line 1
Access to the remote server is denied because no login-mapping exists.
Be made without using a security context This will send an anonymous connection to the Linked Server and likely won’t provide any access to the secondary server.
Be made using the login’s current security context This is the same as placing every single user in the top section with the impersonate checkbox checked.
Be made using this security context: This option will demand a login and password be entered and will function the same as if every single user was added to the top section and given the exact same username and password. It is the least secure method to set up a Linked Server as anyone with any access to the first SQL Server will be able to use the Linked Server utilizing the saved credentials.

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.

linked server options

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

Option Description
Collation Compatible Default is false.  This can be set to true if the target server uses the exact same collation as the principal server.  The change can improve performance of some queries by allowing remote column comparisons on text type columns.
Data Access Default is True.  When false all individual remote query executions will fail.  This setting is independent from RPC Out.  In a scenario where ONLY remote procedure calls need to be executed this setting may be changed to false while RPC Out remains true.
RPC / RPC Out This stands for Remote Procedure Call and allows you to run stored procedures on the Linked Server.  RPC enables Remote Procedure Calls from the specified server and RPC OUT enables Remote Procedure Calls to the specified server.
Use Remote Collation / Collation Name Default is False.  When false the local server collation will be used on all remote queries.  When true the collation of the remote server will be used on queries. If true and the remote server is NOT a SQL Server then the local server cannot automatically look up the proper collation value.  In this case use the Collation Name setting to type in the remote server collation.
Connection Timeout Default is 0.  When non-zero, this is the number of seconds to wait before timing out when connecting to a linked server. When 0 then the value is actually read from a server default that can be found by running this command.
exec sp_configure 'remote login timeout (s)'
Query Timeout Default is 0.  When non-zero, this is the number of seconds to wait before timing out a query running against a linked server. When 0 then the value is actually read from a server default that can be found by running this command.
exec sp_configure 'remote query timeout (s)'
Distributor / Publisher / Subscriber These settings are only used when the Linked Server is being used to support replication.
Lazy Schema Validation Default is False.  When false the schema of a remote query is checked before each query execution.  Any changes to the remote schema are detected in advance and a proper query plan can be recreated to account for the change. When true the schema is not validated until the moment of execution.  If the change in remote schema renders the query plan invalid that will cause a runtime error.
Enable Promotion of Distributed Transactions Default is True.  When enabled any remote query that changes data will invoke the DTC (Distributed Transaction Coordinator).  This process protects such queries by allowing both servers to have a shared transaction that either completes 100% or 0% - just like a regular transaction within a local query. When disabled the DTC is not invoked and a failed remote query can fail and not be completely rolled back.

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.

This error message states Msg 7411  Server configured for is linked name not rpc server

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 4th 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.

This screenshot shows the RPC Out setting on the properties window of the Linked Server.

Rerunning the procedure now produces a much better result.

This screenshot shows the EXEC command with the expected output in the results pane.

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


Last Updated: 2019-06-28


get scripts

next tip button



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

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools