Understanding SQL Server Linked Servers
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.
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. 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.
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.
|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.
The settings do not need to be changed in most use cases and this table will explain what the settings do.
|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
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
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.
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.
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 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.
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.
- There is a whole family of advanced Linked Server tips on MSSQLTips.com
- Making Linked Server names easier to read
Last Updated: 2019-06-28
About the author
View all my tips