Linked Servers
Overview
A linked server is a standing outbound trust with an identity attached. It is one of the few pieces of SQL Server configuration that is both a performance concern and a security surface, and it is usually created once and never looked at again.
This report answers four different questions, because a linked server gets asked four different questions and no single view answers more than one of them:
| View | Question |
|---|---|
| Topology | What is out there? |
| Reach | Does it answer, and how fast? |
| Surface | What is switched on, and who does it connect as? |
| History | Has it been failing? |
Nothing expensive happens on load. Reading sys.servers and sys.linked_logins is trivial. The three things that are not trivial, probing a remote host, searching every database for four part names, and reading every retained error log, are each behind their own button and report what they cost. A report that hangs for thirty seconds when it opens is worse than a boring one.
There is no auto refresh. A linked server definition changes when somebody changes it.
Where to find it
| Route | How |
|---|---|
| Server tree | Right-click the server → Instance Level Reports → Linked Servers |
| Error Log report | Related Links bar → Linked Servers |
| Security report | Related Links bar → Linked Servers |
| AI panel | The action link for Linked Servers Without Data Access |
The page title reads Linked Servers for <server name>.
Requirements
VIEW ANY DEFINITIONor membership that allows readingsys.serversandsys.linked_logins.- Permission to execute
sp_testlinkedserverfor the Test links button, and rights on the remote end for the probe to succeed rather than report a login failure. - The definition read has a 90 second timeout, the uses search 300 seconds, and the log scan 120 seconds. Each probe is capped at 10 seconds, deliberately well under the default remote login timeout: a dead host should cost ten seconds of your time, not the thirty a link with
connect_timeout 0would spend before SQL Server gives up on it.
Nothing on this page changes a linked server. Every action reads or copies.
The six states
Every link is in exactly one state, and the worst thing about a link is what decides it.
| State | What it means |
|---|---|
| Not tested | Not probed. The state every link is in before the button is pressed. |
| Healthy | Answered, and nothing about the definition is remarkable. |
| Unreachable | Probed and did not answer. |
| Wide open | Answers, and anyone on this instance can use it to execute on the remote server. |
| Questionable | Deprecated provider, duplicate definition, data access off, or lazy schema validation. |
| Unused | Nothing on this instance refers to it. |
Not tested is the honest default, and it is what the page opens in. No state claims a link works until something has actually asked it.
Reading the four views
Topology
Hub and spokes. What is out there, and which hosts are one host twice.
Its one structural decision is that a node is a host rather than a link definition. Two links pointing at the same data source collapse into one node badged 2 definitions, which is how anyone finds out they have been maintaining two links to the same box under different credentials for two years. A grid sorted by name cannot say that, because the two rows are adjacent by accident and identical in every column that matters.
Unprobed spokes are dashed and all the same length, and the legend says they are unmeasured. A spoke whose length implied a latency nobody measured is exactly the kind of chart this product does not ship.
Reach
Whether it answers, and how fast.
Bullet lanes on a shared log axis, each measured against that link’s own connect_timeout. sys.servers stores a per link allowance that somebody chose on purpose, and dividing a measured trip by its own allowance is what makes two very different links comparable.
A link that fails draws a wall where it stopped, rather than a longer bar. A 30 second bar and a 30 second timeout are not the same fact and must not look alike.
Where connect_timeout is 0 the lane says so: the link uses the instance default.
Surface
What is switched on and who it connects as. A matrix, because the subject is a set of booleans across a set of rows and there is no magnitude in it.
Colour encodes risk rather than value, so ON is green under Data access and red under RPC out. One lets a query read; the other lets a caller execute.
The row this view exists for is RPC out enabled against a catch-all mapping to a privileged remote account. That is a lateral movement path, and it is invisible on any page that prints sys.servers and stops there.
A catch-all mapping is the row in sys.linked_logins whose local principal is 0. It applies to every login on this instance that has no mapping of its own. Combined with a stored remote credential, the page will say so plainly:
RPC out is on and every login here can execute remotely as …
every login here can query as the stored credential …
History
Whether it has been failing. Lanes across every retained error log rather than just the current one, one lane per linked server.
Vertical alignment across lanes is one event. A lane that is sparse and then solid from a date says the provider broke that day. A column of ticks across three lanes in the same four minutes is one directory outage rather than three problems.
Double-click a tick to open that entry in the Error Log report.
The scan reads up to 8 retained logs and keeps up to 20,000 matched lines per log. If anything was left unread the notice band says so, because a cap nobody is told about reads as all of it.
The toolbar
| Button | What it does |
|---|---|
| Topology / Reach / Surface / History | Switches the view. |
| Test links | Probes every link on the page. This is the only thing that leaves this instance. |
| Find uses | Searches every database for four part names, and reads the plan cache. |
| Scan logs | Reads the retained error logs for linked server failures. |
| System links | Shows or hides the system entries in sys.servers. |
| Refresh | Re-reads the definitions. |
The three work buttons are separate on purpose. Each is slow in a different way and each reports what it cost.
Reading the grid
| Column | What it is |
|---|---|
| Link | The linked server name. |
| State | One of the six states, with its colour. |
| Round Trip | Measured round trip through the provider. Empty until Test links has run. |
| Connects As | The login mapping, in words rather than ids. |
| Options On | Which of the notable options are enabled. |
| References | How many things on this instance mention this link. Empty until Find uses has run. |
| Product | The product string from the definition. |
| Provider | The OLE DB provider. |
| Data Source | The remote data source. |
| Catalog | The default catalog, where one is set. |
| Defined | When the definition was last modified. |
Defined is the least operationally useful timestamp available, and it is last in the grid for that reason. It is when somebody last edited the definition. A link edited in 2017 and working perfectly and a link edited in 2017 whose target host was decommissioned last spring have identical values in it.
What the page will tell you about a definition
Beyond the state, the report calls out the things that are worth knowing and are not obvious from sys.servers:
SQLOLEDBis out of support andMSOLEDBSQLreplaces it.- SQL Native Client is out of support and
MSOLEDBSQLreplaces it. - The Jet provider reaches a file, not a server. Same for the ACE provider.
- Data access is off, so no query can run through the link at all.
- Lazy schema validation is on, so a changed remote schema is not noticed.
- Neither collation compatible nor using the remote collation, which is a correctness and a performance problem at once.
- Nothing on this instance refers to it, once Find uses has run.
How to read the report
- Look at Topology first. Count the nodes against the number of definitions. Any node badged with more than one definition is a duplicate you probably did not know about.
- Press Test links. Until you do, every link is Not tested, and the page is describing definitions rather than reality.
- Read the Unreachable ones. A standing trust to a host that no longer answers is dead weight and usually a leftover.
- Switch to Surface. Look for RPC out enabled on the same row as a catch-all mapping. That is the finding this view was built for.
- Press Find uses. Anything with no references is a candidate for removal, and anything with many is a change you cannot make casually.
- Use History when something intermittent is suspected. One column of ticks across several lanes is one outage, not several faults.
Common patterns
Two nodes collapsed into one, badged 2 definitions. Two links to the same host under different credentials. Usually one is the one everything uses and the other is a forgotten experiment with a privileged account on it.
Wide open with zero references. The worst combination on the page. Nothing on this instance needs it, and everyone on this instance can execute through it.
Every link Unreachable at once. Not the links. Networking, DNS, or the service account. Check History for whether they all stopped on the same day.
A deprecated provider on a link that works fine. It works until the day the provider is not installed on a rebuilt server. Worth planning rather than worth panicking about.
Data access off. The link cannot serve queries. Either it exists for RPC only, deliberately, or somebody turned this off and nobody noticed why the queries started failing.
Where the data comes from
sys.servers, filtered to rows withis_linkedset, for the definitions.sys.linked_loginsfor the identity mappings, which is what makes the Surface view possible.sp_testlinkedserverplus a round trip through the provider, on demand, for Reach.- A search of every database plus the plan cache, on demand, for References.
- The retained SQL Server error logs, on demand, for History.
Nothing is stored. Every probe result lives for as long as the page is open.
Settings
| Setting | Default | Values |
|---|---|---|
LinkedServersView |
topology |
Which view the page opens on |
LinkedServersShowSystem |
off | Whether system entries in sys.servers are shown |
Per-user rather than per-instance.
Messages you may see
When the instance has no linked servers:
This instance has no linked servers. Nothing in sys.servers has is_linked set. That is one fewer standing outbound trust …
On Azure SQL Database:
Azure SQL Database does not support linked servers at all, so there is nothing here to show. External data sources for elastic query are a different feature and are not listed in sys.servers.
When the definition read times out:
Reading the linked server definitions did not finish in time. Try refreshing this report, or checking the status of the SQL Server.
Related reports
| Report | Why you would go there |
|---|---|
| Security | The rest of the instance’s permission surface. |
| Error Log | Where the History view’s ticks come from, in full. |
| Configuration Values | Whether Ad Hoc Distributed Queries is on, which is the other outbound path. |
| Sessions | Who is connected right now, including through a link. |
Frequently asked questions
Why does every link say Not tested when I open the page? Because nothing has probed them. Reading definitions is instant; reaching a remote host is not, so it is behind the Test links button.
What is a catch-all mapping? The row in sys.linked_logins with a local principal of 0. It applies to every login on this instance that has no mapping of its own. With a stored remote credential attached, every login here can use that credential.
Why is RPC out red when Data access is green? Because they are different risks. Data access lets a query read through the link. RPC out lets a caller execute on the remote server.
Why is a link marked Unused when I know an application uses it? Find uses searches this instance: four part names in database code, and the plan cache. An application that builds its own four part name in a string, or a link used only from another server, will not appear.
Why is Defined the last column? Because it is the least useful thing about a link, and it was one of only six columns this report used to have.
Does the probe change anything on the remote server? No. It runs sp_testlinkedserver and a round trip through the provider.