Problem
There is an option in SQL Server Management Studio (SSMS) for Linked Servers. 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 covers 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. A great library of more advanced topics is available after mastering the basics.
Creating a SQL Server Linked Server
In SSMS’s 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, it is connecting to a named instance of SQL Server on the same machine. This is acceptable, but more commonly, the connection is with another machine entirely. The Linked Server name is specified as the ServerName\InstanceName. In the example below, I am connecting to the local server. 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 gives access to individual logins 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. The SQL Server engine stores and encrypts the credential. On the second row, the Impersonate box shows a checkmark for the LinkedServerTest login (a SQL Login). 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 occur but requires proper Kerberos, SPNs, and proper delegation rules set on the domain.
Options
Now focus on the bottom half of the screen. There are 4 options available. The selected option will apply to any user not listed in the top section.
| 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 |
| 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 remain as the default values as shown below.
Settings
The settings do not need changing in most use cases. The following table explains 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, SQL Server requires a 4-part identifier reference for any table or view. It must consist 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, due to its format, you must bracket it. Brackets are not necessary for all Linked Server names.
- Next is the database name, master, and WideWorldImporters respectively.
- Third is the schema name, sys, and Sales respectively.
- Last is the object name. 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, treat these tables and views the same as any local table: query, alias, and join them. With the proper permissions, you can modify them using insert, update, and delete statements.
Calling Stored Procedures Over a Linked Server
To start, let’s execute the following procedure 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 executes 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.SQLTips1However, an error appears instead of the expected result.
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 value that needs changing is NOT the setting labeled RPC. 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. However, there are a few limitations to be aware of before implementation. SQL Server prohibits using Linked Servers to call table values or scalar functions. Also, you cannot call the truncate command using a Linked Server.
SQL Server Linked Server Use Cases
The performance of Linked Servers is not always great, especially in cases with large data sets and lots of joins between local and linked tables. But they can be very useful for querying smaller datasets.
Sometimes you can meet an ETL requirement for a smaller dataset using a Linked Server rather than an ETL tool like SSIS. When the rows involve numbers in the thousands or lower 10s of thousands, a Linked Server-based ETL might complete in less time than it takes to start the SSIS engine.
Next Steps
