Linked Server with No Data Access

What this check looks for

Linked servers in sys.servers where is_data_access_enabled is 0. The message names the linked server.

Why it matters

A linked server with data access disabled cannot be used for queries. Attempting one fails with an error that names the option, which is helpful, but the configuration should not be in that state in the first place.

The error looks like this:

Server ‘RemoteServer’ is not configured for DATA ACCESS.

There are two situations, and they need opposite responses.

It is meant to work and does not. Somebody created the linked server, tested the connection successfully, and never ran a query through it. The connection test does not exercise data access, so the misconfiguration survives until the first real query, which is often in a scheduled job at two in the morning. This is the case where the finding saves you an incident.

It is obsolete and should be removed. The remote system was decommissioned, the integration was replaced, or the link was created for a one-off migration. Disabling data access is sometimes used as a soft delete, on the reasoning that turning it off is safer than dropping it.

The second case is the more common one, and leaving it is not harmless:

• The stored credentials are still there. A linked server login mapping holds a password that SQL Server can decrypt. An obsolete link is a set of credentials for a remote system that nobody is rotating and nobody is watching, and if that remote system still exists, those credentials still work.
• RPC may still be enabled even with data access off, which is a separate permission allowing remote procedure calls. Data access off does not mean the link is inert.
• It clutters the security review. Every audit of what this instance can reach has to account for it.
• It misleads. Somebody writing a new integration sees a linked server to the system they need and assumes it works.

And there is a third possibility worth checking: the link is used only for remote procedure calls, in which case data access being off is deliberate and correct. is_rpc_out_enabled tells you, and that configuration is a legitimate least privilege choice.

How to confirm it yourself

Every linked server with its options:

SELECT s.[name]                     AS [linked_server],
       s.[product],
       s.[provider],
       s.[data_source],
       s.[catalog],
       s.[is_data_access_enabled],
       s.[is_rpc_out_enabled],
       s.[is_remote_login_enabled],
       s.[is_collation_compatible],
       s.[query_timeout],
       s.[connect_timeout],
       s.[modify_date]
  FROM sys.servers AS s WITH (NOLOCK)
 WHERE s.[is_linked] = 1
 ORDER BY s.[name];

is_data_access_enabled of 0 with is_rpc_out_enabled of 1 is the deliberate RPC-only configuration. Both at 0 is a link that does nothing at all.

How it authenticates, which is the part that matters for the obsolete case:

SELECT s.[name]                  AS [linked_server],
       ll.[local_principal_id],
       CASE WHEN ll.[local_principal_id] = 0 THEN '(all logins)'
            ELSE SUSER_NAME(ll.[local_principal_id]) END AS [local_login],
       ll.[uses_self_credential],
       ll.[remote_name],
       ll.[modify_date]
  FROM sys.servers            AS s  WITH (NOLOCK)
  LEFT JOIN sys.linked_logins AS ll WITH (NOLOCK) ON ll.[server_id] = s.[server_id]
 WHERE s.[is_linked] = 1
 ORDER BY s.[name];

A remote_name of sa on any linked server is a finding in its own right, whether or not data access is enabled.

Whether anything references it, which decides between fixing and removing:

-- four part names in module definitions
SELECT DB_NAME() AS [database_name],
       OBJECT_SCHEMA_NAME([object_id]) AS [schema_name],
       OBJECT_NAME([object_id])        AS [object_name],
       [definition]
  FROM sys.sql_modules WITH (NOLOCK)
 WHERE [definition] LIKE '%YourLinkedServerName%';

-- and in Agent job steps
SELECT j.[name] AS [job_name], st.[step_name], st.[command]
  FROM msdb.dbo.sysjobsteps AS st
 INNER JOIN msdb.dbo.sysjobs AS j ON j.[job_id] = st.[job_id]
 WHERE st.[command] LIKE '%YourLinkedServerName%';

Run the first one in each database, since a four part name can appear in any module.

And test whether the remote end is even reachable, which is often the real answer:

EXEC sp_testlinkedserver N'YourLinkedServerName';

That tests the connection, not data access. It is exactly the test that gives a false sense of a working link, which is why this check exists. A query is the real test:

SELECT TOP (1) * FROM [YourLinkedServerName].[master].[sys].[databases];

How to fix it

Decide which of the two cases you are in, then enable it or remove it.

If it should work, turn data access on:

EXEC sp_serveroption @server   = N'YourLinkedServerName',
                     @optname  = N'data access',
                     @optvalue = N'true';

Then actually test a query, not just the connection:

SELECT TOP (1) [name] FROM [YourLinkedServerName].[master].[sys].[databases];

Set the other options deliberately while you are there:

-- only if remote procedure calls are genuinely needed
EXEC sp_serveroption @server = N'YourLinkedServerName',
                     @optname = N'rpc out', @optvalue = N'false';

-- a timeout that suits this link
EXEC sp_serveroption @server = N'YourLinkedServerName',
                     @optname = N'query timeout', @optvalue = N'600';

EXEC sp_serveroption @server = N'YourLinkedServerName',
                     @optname = N'connect timeout', @optvalue = N'15';

If it is obsolete, remove it properly, which means dropping it rather than leaving it disabled:

-- record the definition first
SELECT s.[name], s.[product], s.[provider], s.[data_source], s.[catalog],
       ll.[remote_name], ll.[uses_self_credential]
  FROM sys.servers AS s
  LEFT JOIN sys.linked_logins AS ll ON ll.[server_id] = s.[server_id]
 WHERE s.[name] = N'YourLinkedServerName';

-- then drop it and its login mappings
EXEC sp_dropserver @server = N'YourLinkedServerName', @droplogins = N'droplogins';

@droplogins is the important part. Dropping the server without it can leave the login mappings, and the point of removing an obsolete link is to remove the stored credentials with it.

Then tell the remote system. If the link used a dedicated account on the remote server, that account should be disabled there too. Removing your end leaves a credential that still works for anyone who has it.

And fix the security posture of the links you keep:

• Do not map to sa or to a sysadmin account. A linked server login should have the minimum permissions the queries need on the remote side.
• Avoid mapping “all logins” to a single remote credential, which makes every local user act as that account remotely.
• Prefer Windows authentication with delegation where the environment supports it, so no password is stored at all.

How long it takes

About half an hour to review the links, test them and remove the obsolete ones.

Related reports

Related checks

Frequently asked questions

Why would data access be off deliberately? For a link used only for remote procedure calls. is_rpc_out_enabled of 1 with data access off is a legitimate least privilege configuration, and worth recording so it is not “fixed” later.

The connection test passes but queries fail. sp_testlinkedserver tests the connection, not data access. That is exactly how this misconfiguration survives until a real query runs, usually in a scheduled job.

Is a disabled linked server harmless? Not entirely. The stored credentials remain and can still be valid on the remote system, and RPC may still be enabled. If it is obsolete, drop it with @droplogins rather than leaving it disabled.

How do I know whether anything uses it? Search module definitions and Agent job steps for the linked server name. Application code outside the database will not appear, so check with whoever owns the integrations before dropping one.