Linked Servers to Another SQL Server Instance

Linked servers have a bad name, and I've earned some of it myself. The first one I ever built used a fixed sa password because the SSMS dialog made it easy, and it dragged whole tables over the network. Done properly, though, they're the quickest way to pull a lookup table from another system or compare data between environments without an ETL tool. You query them with four-part names like [SQLPROD02].[Sales].[dbo].[Orders].

I'll create one between two instances in T-SQL, get the security mapping right, and write remote queries that don't hurt. You need two instances (2012 to 2017) that can reach each other on TCP 1433, ALTER ANY LINKED SERVER (or sysadmin) locally, and a remote login with only the permissions the link needs.

Creating the linked server

For SQL Server to SQL Server, the simplest form uses the native provider and the remote server name as the data source:

EXEC master.dbo.sp_addlinkedserver
    @server = N'SQLPROD02',
    @srvproduct = N'SQL Server';

When the remote instance is a named instance or on a non-standard port, or you want a friendlier alias, specify the provider explicitly. SQLNCLI11 (SQL Server Native Client 11) is installed with every SQL Server from 2012 on; the newer MSOLEDBSQL provider works if you have installed the Microsoft OLE DB Driver for SQL Server separately.

EXEC master.dbo.sp_addlinkedserver
    @server = N'REPORTING',
    @srvproduct = N'',
    @provider = N'SQLNCLI11',
    @datasrc = N'sqlrep01.example.com,1450',
    @catalog = N'Warehouse';

Security mapping

Most installations go wrong here. sp_addlinkedsrvlogin decides how local logins are authenticated remotely.

Preferred option in a domain: pass through the caller's Windows credentials.

EXEC master.dbo.sp_addlinkedsrvlogin
    @rmtsrvname = N'REPORTING',
    @useself = N'True',
    @locallogin = NULL;

This requires Kerberos delegation when the user is connected from a third machine (the double-hop problem, see the end). If that is not possible, map to a dedicated remote SQL login with minimal rights, and restrict which local principals may use it:

-- remove the default "all logins" mapping
EXEC master.dbo.sp_droplinkedsrvlogin @rmtsrvname = N'REPORTING', @locallogin = NULL;

-- only members of this local login may use the link, and they arrive as linked_reader
EXEC master.dbo.sp_addlinkedsrvlogin
    @rmtsrvname = N'REPORTING',
    @useself = N'False',
    @locallogin = N'CONTOSO\ReportUsers',
    @rmtuser = N'linked_reader',
    @rmtpassword = N'Str0ng-Remote-Pass!';

On the remote server, linked_reader gets db_datareader on Warehouse and nothing else. Never map everyone to sa; every local user, including public, would inherit sysadmin on the remote server.

Server options

EXEC master.dbo.sp_serveroption N'REPORTING', N'rpc out', N'true';
EXEC master.dbo.sp_serveroption N'REPORTING', N'remote proc transaction promotion', N'false';
EXEC master.dbo.sp_serveroption N'REPORTING', N'query timeout', N'600';
EXEC master.dbo.sp_serveroption N'REPORTING', N'collation compatible', N'true';

• rpc out is needed to call remote stored procedures and to use EXEC (...) AT REPORTING.
• remote proc transaction promotion off avoids escalating to a distributed transaction through MSDTC when a remote procedure is called inside a local transaction; turn it on only if you really need two-phase commit and have MSDTC configured.
• collation compatible tells the optimizer that both servers use the same collation, so it can push WHERE clauses on string columns to the remote side. Only set it if that is true.

Querying without pain

Four-part names are convenient, but the local optimizer often decides to fetch the whole remote table and filter locally, especially with joins:

-- convenient, sometimes slow
SELECT o.OrderID, o.OrderDate
FROM REPORTING.Warehouse.dbo.FactOrders o
WHERE o.OrderDate >= '20190201';

OPENQUERY sends the string to the remote server verbatim, so the filtering and joins happen there and only the result comes back:

SELECT * FROM OPENQUERY(REPORTING, 'SELECT OrderID, OrderDate FROM Warehouse.dbo.FactOrders WHERE OrderDate >= ''20190201''');

For anything with parameters, EXEC ... AT is the cleanest:

EXEC ('SELECT OrderID, OrderDate FROM Warehouse.dbo.FactOrders WHERE OrderDate >= ?', '20190201') AT REPORTING;

If you join local and remote tables, I pull the remote rows into a local temp table first, then join. More code, but I know what it'll do.

Checking the link

EXEC sp_testlinkedserver N'REPORTING';
SELECT * FROM sys.servers WHERE is_linked = 1;
SELECT * FROM sys.linked_logins WHERE server_id = (SELECT server_id FROM sys.servers WHERE name = 'REPORTING');
SELECT * FROM REPORTING.master.sys.databases;

sp_testlinkedserver returns without output on success. Also confirm on the remote server (via sys.dm_exec_sessions) that connections arrive as the login you intended.

When it goes wrong

• "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'." The classic double hop. You connected to server A with Windows authentication from your workstation, A cannot forward your ticket to B. Fix with constrained delegation on A's service account (setspn and "Trust this user for delegation to specified services only" in AD), or use a mapped SQL login.
• "Msg 7202: Could not find server 'REPORTING' in sys.servers." The four-part name uses the alias exactly as created; check for typos and for a missing @server when the local server was renamed (sp_dropserver/sp_addserver ... 'local').
• "Msg 7391: The operation could not be performed because OLE DB provider 'SQLNCLI11' for linked server 'REPORTING' was unable to begin a distributed transaction." A remote modification ran inside a local transaction and MSDTC is not configured. Either enable Network DTC Access on both servers and open the firewall for it, or restructure so remote writes happen outside a local transaction.
• "Msg 7411: Server 'REPORTING' is not configured for RPC." rpc out is false; set it as in the server options above.

Almost every complaint I hear about linked servers traces back to the defaults.