In SQL Server, there’s a “data access” setting that enables and disables a linked server for distributed query access. If you ever get the “Server is not configured for DATA ACCESS” error, it’s probably because you’re trying to run a distributed query against a linked server that isn’t configured for data access. This can also happen when you try to run OPENQUERY() against your local server.

You can use sp_serveroption to enable or disable data access on a given server. However, you might want to check the existing settings before you start changing them. The following examples show how to do that.

Example 1 – Query the sys.servers System View

Probably the best way to check if data access is enabled is to query the sys.servers system catalog view. You can either return all columns, or just those you want returned. Here’s an example of returning two columns:

SELECT 
  name,
  is_data_access_enabled 
FROM sys.servers;

Result:

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

In this case, data access is enabled for the server called Homer, but not for the server called sqlserver007.

Example 2 – Execute the sp_helpserver System Stored Procedure

The sp_helpserver system stored procedure will also give us this information, although in a different format:

EXEC sp_helpserver;

Result:

+--------------+--------------------------------+----------------------------------+------+------------------+-------------------+-----------------+
| name         | network_name                   | status                           | id   | collation_name   | connect_timeout   | query_timeout   |
|--------------+--------------------------------+----------------------------------+------+------------------+-------------------+-----------------|
| sqlserver007 | sqlserver007                   | rpc,rpc out,use remote collation | 0    | NULL             | 0                 | 0               |
| Homer        | NULL                           | data access,use remote collation | 1    | NULL             | 0                 | 0               |
+--------------+--------------------------------+----------------------------------+------+------------------+-------------------+-----------------+

If you look in the status column, you’ll see that data access is included in the row for Homer, but not for sqlserver007.

Which Server Do I Run the Code On?

You need to run the code against the local server, not the remote server. By this I mean, if you’re checking to see if you can run distributed queries against a linked server, 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 to see if data access is enabled for the Homer linked server.

I don’t need to jump over to Homer to check its setting. In fact, if I do jump over, it may actually have a different setting.

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

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.

The fact that I was able to retrieve this information via a distributed query supports the assertion that it was my own server’s setting that enabled the distributed query.