Linked Servers and What Goes Wrong With Them

A linked server makes remote data look convenient, but it does not make the remote server local. Performance, security, and failure behavior still cross a network and an administrative boundary.

Inventory the Connection Before the Query

Identify the provider, data source, and enabled options before changing the SQL. A linked server may point to another SQL Server instance or a different data source. Provider capabilities affect what the optimizer can send remotely.

SELECT name, product, provider, data_source,
       is_data_access_enabled, is_rpc_out_enabled,
       is_remote_proc_transaction_promotion_enabled
FROM sys.servers
WHERE is_linked = 1
ORDER BY name;

This reads local configuration and does not test the remote connection. Inspect provider versions and the destination's availability separately. Avoid printing provider strings because configuration can include sensitive details.

For SQL Server 2025, review the OLE DB Driver 19 encryption changes during upgrade planning. Existing connections can fail when certificates do not meet the new requirements. Use an explicit, supported encryption configuration with valid certificate trust.

Find Where the Work Happens

A four-part query can combine local and remote objects. SQL Server may push suitable work to the remote source, but that is not guaranteed. Provider support, expressions, estimates, and query shape all influence the plan.

Capture the actual execution plan for a representative query in a safe test. Inspect remote operators and the remote statement where available. Compare rows returned across the boundary with rows finally required.

-- Replace ReportingLink with an approved existing SQL Server link.
SELECT name, state_desc
FROM [ReportingLink].[master].[sys].[databases]
WHERE name = N'tempdb';

This small example uses a remote catalog instead of an application table. Permissions determine which metadata is visible. Apply the same investigation to the real query without assuming this example predicts its plan.

A selective condition written locally does not prove that filtering happens remotely. Large intermediate transfers can dominate elapsed time. Check both servers and the network boundary before adding a local index.

Use Pass-Through Queries Deliberately

OPENQUERY sends its inner query to the configured remote source. Put the intended remote filter inside that query when you need it evaluated there. The outer query still has its own local work.

SELECT name, state_desc
FROM OPENQUERY([ReportingLink],
  'SELECT name, state_desc
   FROM master.sys.databases
   WHERE name = ''tempdb''');

Run this only after replacing the link name with the same approved test connection. OPENQUERY does not accept ordinary variables for its arguments. Avoid solving that restriction by concatenating untrusted input.

For parameterized remote operations, consider a controlled remote procedure or another supported parameter-binding approach. Review provider and RPC requirements before adopting it. The easiest query to type may not be the easiest interface to maintain.

Verify the Identity at the Remote End

The caller's local permissions do not automatically describe remote permissions. A linked-server login mapping may use a fixed remote account or the caller's security context. Windows delegation can introduce another dependency.

SELECT s.name AS linked_server,
       ll.local_principal_id, ll.uses_self_credential,
       ll.remote_name
FROM sys.linked_logins AS ll
JOIN sys.servers AS s ON s.server_id = ll.server_id
ORDER BY s.name, ll.local_principal_id;

Review mappings with an authorized administrator and test using the real application identity. A successful sysadmin test does not prove an application can connect. A broad default mapping can also grant more access than intended.

Do not store remote credentials in query text or troubleshooting notes. Use the supported configuration path and least necessary remote permissions. Authentication success should be followed by an access test against the intended objects.

Account for Transactions and Outages

Remote writes can introduce distributed-transaction requirements depending on the operation and configuration. A network interruption can leave a more complicated recovery problem than a local statement failure. Test the complete transaction behavior before deployment.

Set deliberate timeout and retry policies at the application boundary. A retry after an uncertain write needs duplicate protection. Do not assume a timeout proves that the remote change never happened.

Also consider what happens when a frequently used report depends on a remote server during an outage. Live dependency may be acceptable, but it should be intentional. Document the failure response and ownership across both teams.

Choose the Smallest Dependable Interface

A scheduled local copy can work well when a report tolerates delayed data. An application call or controlled remote procedure may provide a clearer operational contract. A linked server remains useful for suitable bounded workloads.

Compare freshness, data volume, transaction needs, and ownership before choosing. Keep the remote interface narrow and observable. Convenience should reduce work without concealing the cost of crossing servers.

A linked server is not a local table with a longer name, it is a remote dependency with an operating cost.

This post was rewritten from scratch in September 2026. The original, published on 2016-12-17, was a short announcement about something that no longer exists. The address is the same, the subject is now something worth keeping.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.