Securing Linked Servers

A linked server can turn one SQL Server login into access on another system. Securing linked servers starts with its mappings, remote permissions, and the features you actually need.

Securing Linked Servers Starts With an Inventory

sys.servers lists linked servers and their basic options. Record the remote destination, provider, product, and owner. A link that nobody recognizes can still support a scheduled job or report. Do not delete it from a quiet connection sample. Search dependencies, job steps, procedures, and application code first.

I ask who owns each remote system. A linked server is a cross-system trust path, so two teams can need to approve changes. The name can look local while the endpoint points to a different environment. Verify the actual data source and expected use. Which link would surprise you if it reached production today? Start that review first.

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

Read Login Mappings

sys.linked_logins shows local-to-remote login mapping metadata. A null local principal can indicate a default mapping that applies broadly. Review whether connections use the current security context, a specific remote login, or another mapping. The catalog does not expose remote passwords, and that is good. Confirm who manages their rotation.

I have seen a single broad default mapping turn an ordinary local user into a powerful remote user. The link worked smoothly, which delayed discovery. List every mapping and test effective remote permissions from representative local identities. A mapping’s convenience should not outrun the reason for access.

SELECT s.name AS linked_server,
       ll.local_principal_id,
       sp.name AS local_login,
       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
LEFT JOIN sys.server_principals AS sp
  ON sp.principal_id = ll.local_principal_id
ORDER BY s.name, sp.name;

Remove Broad Defaults

A default mapping can let more local logins reach the remote server than the owner intended. Replace it with explicit mappings or a controlled access pattern when the application permits. Test jobs and reports before removal. A successful local SELECT through the link does not show which remote account ran it unless you check the remote side.

I review the remote login’s permissions as carefully as the local mapping. A least-privilege local identity mapped to remote sysadmin is not least privilege. Ask the remote owner to verify the account and its roles. The security boundary crosses both instances. A local-only review sees half the path.

Limit RPC Features

RPC and RPC OUT allow remote procedure calls when enabled. Leave them off when the link only needs distributed queries. If a workload requires RPC OUT, document the procedure set, remote permissions, and reason. Review data access separately. Disabling an option can break a hidden job, so inventory actual use before changing it.

I avoid turning on every linked-server option to make one query pass. The first error usually tells you which capability is missing. Decide whether that capability belongs in the design. A four-part-name read and a remote procedure execution have different risk profiles. Configure only the path the workload needs.

Test With Real Identities

Run approved read-only tests as each local login type that uses the link. Confirm the remote identity and allowed objects. Do not test only from sysadmin, because that can mask mapping differences. Check both success and denial cases. If a link crosses environments, verify that a development login cannot reach production data without explicit approval.

I ask the remote team to observe the same test. Their audit log can show which account arrived. A connection that succeeds from the local side can still use an unexpected credential. Record the result in the link’s operations entry. Identity is a fact to test, not a property to infer from a connection string.

Securing Linked Servers Also Means Rotating Credentials

Stored remote credentials require secure rotation and ownership. Do not leave a shared password that nobody can change without breaking unknown jobs. Provider choice, driver support, and encryption settings affect the connection path. Review current supported provider guidance for the SQL Server version and remote platform. Test certificate validation where the remote endpoint uses TLS.

I keep credential location out of general runbooks while documenting the rotation process. The team should know who can update it and how to verify the link afterward. A linked server that fails only at password expiration needs a better lifecycle, not a longer-lived secret.

Watch Distributed Work

Cross-server queries can pull large data sets, use distributed transactions, or fail in ways that are hard to reproduce locally. Limit permissions and query scope. If the link supports a critical workflow, monitor errors and latency and document the dependency. A report that joins local and remote tables deserves performance testing under realistic data volume.

I treat a linked server as an integration, not as a free extension of the local database. Remote availability and network behavior become part of the application path. A local backup cannot restore the remote service. Keep that dependency visible in recovery plans and incident escalation.

Audit Changes and Use

Track changes to linked-server definitions, mappings, and options. Review remote login activity using the remote system’s audit tools. SQL Server catalog snapshots can show current state, while an audit or change record supplies history. Protect who can create or alter links. A new link can create a new data path without any change to application tables.

I compare the inventory each quarter and after migrations. Retired links should be removed only after owners confirm no job or procedure still uses them. An unused link with a privileged credential is unnecessary exposure. Its removal deserves a test and rollback just like any other integration change.

Keep Securing Linked Servers With Clear Ownership

For each link, record local owner, remote owner, endpoint, purpose, mapping model, remote privilege, RPC settings, credential rotation, and recovery dependency. Keep secrets in an approved store. Test the documented path after driver upgrades and remote server changes. A link that nobody can explain should become an investigation item.

What can a local application login reach through this link today? Answer it with a test, not a guess. Securing linked servers means controlling the full path from local identity to remote action. When that path is visible, least privilege and monitoring become practical.

Related reading on this blog: How to Create Linked Server to SQL Azure Database? and Quickest Way to Add LoopBack Linked Server for OpenQuery.

A linked server is not just a shortcut to another table, it is a cross-system access path.

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