The “Server is not configured for DATA ACCESS” error in SQL Server is a common error when trying to run a distributed query against a server that has its data access setting disabled.
The error will have the name of the server that you’re trying to access. For example, if your server name is SQL01, the error will read something like this:
Msg 7411, Level 16, State 1, Line 1 Server 'SQL01' is not configured for DATA ACCESS.
“Data access” is a setting that enables and disables a linked server for distributed query access.
A common cause of this error is when you try to run OPENQUERY() against the local server. If you really want to run OPENQUERY() against the server, you’ll need to ensure data access is enabled for that server – even if it’s your local server.
This article walks through an example of running a query that generates the error, checking whether a server has data access enabled, enabling data access, re-checking, and finally running the query again. If you don’t want to walk through the whole scenario, scroll down to the “The Solution” heading below. Alternatively, check out How to Enable & Disable Data Access in SQL Server for a quick example of enabling and disabling data access.
Also check out 2 Ways to Check if Data Access is Enabled if you just want to check the setting.
Otherwise, read on – it’s all covered in this article.
Example 1 – The Error
Here’s an example of a scenario that causes the error.
SELECT COLUMN_NAME, TYPE_NAME, PRECISION, LENGTH FROM OPENQUERY ( sqlserver007, 'EXEC WideWorldImporters.[dbo].[sp_columns] Cities, Application;' );
Result:
Msg 7411, Level 16, State 1, Line 1 Server 'sqlserver007' is not configured for DATA ACCESS.
In this case, I’m trying to run OPENQUERY() against my own local server called sqlserver007, but it fails, because the server doesn’t have data access enabled.
You might be wondering why I’m running OPENQUERY() against my own server when I could just call the stored procedure locally? That’s true, but in this case the stored procedure returns more columns than I need, so I figured it was easy enough to just run it through OPENQUERY() so that I could choose the columns I need. Doh! Not as easy as I thought!
But this minor hiccup is easy enough to solve, so let’s continue.
Example 2 – Check the Data Access Setting
We can see whether a server has data access enabled by checking the sys.servers system catalog view.
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.
In case you’re interested, the sp_helpserver system stored procedure will also give us this information:
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.
Example 3 – The Solution
Here’s how to allow data access.
EXEC sp_serveroption @server = 'sqlserver007', @optname = 'DATA ACCESS', @optvalue = 'TRUE';
Result:
Commands completed successfully.
Example 4 – Re-Check the Setting
Now we can re-check the data access setting.
SELECT name, is_data_access_enabled FROM sys.servers;
Result:
+--------------+--------------------------+ | name | is_data_access_enabled | |--------------+--------------------------| | sqlserver007 | 1 | | Homer | 1 | +--------------+--------------------------+
Now my local server has data access enabled.
And here’s what it looks like with sp_helpserver:
EXEC sp_helpserver;
Result:
+--------------+--------------------------------+----------------------------------------------+------+------------------+-------------------+-----------------+ | name | network_name | status | id | collation_name | connect_timeout | query_timeout | |--------------+--------------------------------+----------------------------------------------+------+------------------+-------------------+-----------------| | sqlserver007 | sqlserver007 | rpc,rpc out,data access,use remote collation | 0 | NULL | 0 | 0 | | Homer | NULL | data access,use remote collation | 1 | NULL | 0 | 0 | +--------------+--------------------------------+----------------------------------------------+------+------------------+-------------------+-----------------+
We can now see that data access has been added under the status column.
Example 5 – Re-Run the Original Query
Now that we’ve enabled data access, let’s re-run the original query.
SELECT COLUMN_NAME, TYPE_NAME, PRECISION, LENGTH FROM OPENQUERY ( sqlserver007, 'EXEC WideWorldImporters.[dbo].[sp_columns] Cities, Application;' );
Result:
+--------------------------+-------------+-------------+------------+ | COLUMN_NAME | TYPE_NAME | PRECISION | LENGTH | |--------------------------+-------------+-------------+------------| | CityID | int | 10 | 4 | | CityName | nvarchar | 50 | 100 | | StateProvinceID | int | 10 | 4 | | Location | geography | 2147483647 | 2147483647 | | LatestRecordedPopulation | bigint | 19 | 8 | | LastEditedBy | int | 10 | 4 | | ValidFrom | datetime2 | 27 | 54 | | ValidTo | datetime2 | 27 | 54 | +--------------------------+-------------+-------------+------------+
This time it runs without error.
Although this example used an OPENQUERY() to my local server, the same fix would apply if I was trying to run a distributed query against a (remote) linked server. Regardless, the above steps are still done on my local server (no need to touch the remote server).