0

I want to SELECT some data from a remote SQL Server database which has "Ad Hoc Distributed Queries" enabled. The remote server is not a linked server to my local instance and its not in my ODBC data sources. I didn't think it had to be anyway.

However I don't understand how to get to it. I have tried this (replacing the xx.xx.xx.xx part with the IP address of the remote server):

SELECT *
FROM OPENDATASOURCE(
'SQLNCLI',
'Data Source=xx.xx.xx.xx\MSSQLSERVER;User ID=SA;Password=SA;').MainDB.dbo.Product

I get the following error:

Msg 15281, Level 16, State 1, Line 1: SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', search for 'Ad Hoc Distributed Queries' in SQL Server Books Online.

The remote server has 'Ad Hoc Distributed Queries' for sure 100%. What am I missing? I turned SQL Server Browser on the remote server as well.

volume one
  • 6,800
  • 13
  • 67
  • 146
  • What's the message, level and state number? Also, you are certain that Ad Hoc has been enabled, are the server instance, user id and password correct as well? – SQLHound Jun 17 '15 at 16:47
  • @SQLHound It is Msg 15281, Level 16, State 1, Line 1. When I go to Surface Area Configuration it says Ad Hoc is set to true. I can connect to the remote SQL Server just fine through SSMS and see all its databases and tables. – volume one Jun 17 '15 at 17:41
  • And you tried the sp_configure commands before this query (even though I agree with you that you shouldn't have to...if it says it's open it should mean what it says)? – SQLHound Jun 17 '15 at 17:57
  • @SQLHound Yes I ran `sp_configure 'show advanced options', 1; RECONFIGURE; GO sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE; GO`. I am able to connect to the remote server as a linked server and it works fine. – volume one Jun 17 '15 at 18:09
  • Still looking... when you `EXEC sp_configure go` on it's own, the `config_value` and `run_value` are both 1? – SQLHound Jun 17 '15 at 18:38
  • For `Ad Hoc Distributed Queries` it says `minimum:0; maximum: 1; config_value: 1; run_value: 1` – volume one Jun 17 '15 at 18:45
  • 1
    Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/80819/discussion-between-sqlhound-and-volume-one). – SQLHound Jun 17 '15 at 18:51

0 Answers0