Linked Server with No Data Access

What Happens When a Linked Server Has “No Data Access” Enabled?

A Linked Server in SQL Server allows you to connect to external databases, such as another SQL Server instance, Oracle, MySQL, or even file-based data sources. However, one important configuration setting is “Data Access”.

If Data Access is turned off for a linked server, it means:
✅ You can still define the linked server
❌ You cannot run queries against it (i.e., SELECT, INSERT, UPDATE, DELETE will fail)
❌ You cannot execute remote stored procedures or distributed queries

How to Check if Data Access Is Enabled for a Linked Server

Run the following query to check the data access setting for all linked servers:

SELECT name, data_access 
FROM sys.servers;
  • If data_access = 1, the linked server allows queries.
  • If data_access = 0, the linked server does not allow queries.

How to Enable Data Access for a Linked Server

If you find that data access is turned off, you can enable it using:

EXEC sp_serveroption 'YourLinkedServerName', 'data access', 'true';

After enabling data access, you should be able to run queries like:

SELECT * FROM YourLinkedServerName.DatabaseName.SchemaName.TableName;

Why Would Data Access Be Turned Off?

There are several reasons why Data Access might be disabled for a linked server:

  1. Security Restrictions
    • Disabling data access prevents unauthorized users from querying remote databases.
    • It can act as a safeguard against accidental or malicious data modifications.
  2. Performance Considerations
    • Some linked servers are set up only for executing remote stored procedures, not for querying tables.
    • Disabling data access can prevent distributed queries that might impact performance.
  3. Misconfiguration
    • Sometimes, data access is accidentally left disabled when creating the linked server.
  4. Migration or Decommissioning
    • If a linked server is being retired or migrated, disabling data access can prevent further use.

Final Thoughts

If you’re unable to query a linked server, check whether data access is disabled using sys.servers. If needed, enable it with sp_serveroption.

Need Help Troubleshooting Linked Servers?
At Stedman Solutions, we specialize in SQL Server performance tuning, troubleshooting, and linked server configuration.

Contact us today: Stedman Solutions Contact