SQL Server has a “data access” configuration option that enables and disables a linked server for distributed query access.

If you ever get a “Server is not configured for DATA ACCESS” error, you’ll probably need to enable data access for the linked server you’re trying to run the distributed query against. Conversely, there may also be times where you need to disable data access.

To enable or disable data access, use the sp_serveroption system stored procedure. Execute it against the server that you intend to run distributed queries from. The following example demonstrates how to do this.

Example 1 – Enable Data Access

Here’s how to enable data access.

EXEC sp_serveroption
  @server = 'sqlserver007',
  @optname = 'DATA ACCESS',
  @optvalue = 'TRUE';

In this case, the server is called sqlserver007, and I set the DATA ACCESS option to TRUE.

Here’s a more concise way of doing the same thing:

sp_serveroption 'sqlserver007', 'DATA ACCESS', 'TRUE';

Running either of those will enable data access on the specified linked server.

By the way, the specified linked server can be the local server if required. It doesn’t need to be a remote server.

To check the data access setting, run a query against the sys.servers system catalog view.

SELECT 
  name,
  is_data_access_enabled 
FROM sys.servers;

Result:

+--------------+--------------------------+
| name         | is_data_access_enabled   |
|--------------+--------------------------|
| sqlserver007 | 1                        |
| Homer        | 1                        |
+--------------+--------------------------+

Again, you run this against the local server – not the remote server.

Example 2 – Disable Data Access

To disable data access, simply set @optvalue to FALSE.

EXEC sp_serveroption
  @server = 'sqlserver007',
  @optname = 'DATA ACCESS',
  @optvalue = 'FALSE';

Now check the setting again.

SELECT 
  name,
  is_data_access_enabled 
FROM sys.servers;

Result:

+--------------+--------------------------+
| name         | is_data_access_enabled   |
|--------------+--------------------------|
| sqlserver007 | 0                        |
| Homer        | 1                        |
+--------------+--------------------------+

Which Server Do I Run the Code On?

Run the code against the server that you intend to run distributed queries from.

In my example, sqlserver007 is the name of the local server and Homer is a remote/linked server. If I wanted to run distributed queries against Homer, I’d run the code against sqlserver007.

I don’t need to jump over to Homer to change its setting. In fact, it could have a different setting to the one that I apply to sqlserver007.

To demonstrate this point, here’s what I get if I compare the linked server results with the actual setting on the remote server.

EXEC sp_serveroption
  @server = 'Homer',
  @optname = 'DATA ACCESS',
  @optvalue = 'TRUE';

SELECT 
  'From local',
  is_data_access_enabled 
FROM sys.servers
WHERE name = 'Homer'
UNION ALL
SELECT 
  'Remote setting',
  is_data_access_enabled 
FROM Homer.master.sys.servers
WHERE server_id = 0;

Result:

+--------------------+--------------------------+
| (No column name)   | is_data_access_enabled   |
|--------------------+--------------------------|
| From local         | 1                        |
| Remote setting     | 0                        |
+--------------------+--------------------------+

In this case, the local server has a different setting to its linked server counterpart.

And the fact that I was able to retrieve this information via a distributed query demonstrates that it was my own server’s setting that enabled the distributed query, not the remote server’s.