How to enable Ad Hoc Distributed Queries

Question

When I run a query with OPENROWSET in SQL Server 2000 it works.

But the same query in SQL Server 2008 generates the following error:

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

By the way, you would be better off to heed the warning and change your SQL code to use Linked Servers instead of OPENROWSET. — RBarryYoung, Jan 27 '13 at 04:25
@RBarryYoung it's not really a "warning" - it's just a notice saying it's not enabled. What is the danger of enabling this? — Conrad, Jun 04 '14 at 14:39
if a malicious user was able to inject SQL somewhere, turning on this option could allow them to probe data files of their choosing. If you set it up as a Linked Server, only the specific files - Linked Servers - are exposed (and you have your integrated SQL Server security you could be using, etc. ) . — Mike M, Sep 25 '16 at 08:13
but I do like this option for my internal testing and data migration... hence looking for how to use it again :) — Mike M, Sep 25 '16 at 08:13
This is termed as vulnerability, but is there any alternative to achieve this ? — rinilnath, Jun 12 '23 at 10:42

score 252 · Accepted Answer · edited Jan 27 '13 at 04:16

252

The following command may help you..

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO

edited Jan 27 '13 at 04:16

Aaron Bertrand

272,866
37
466
490

answered Jan 27 '13 at 03:54

Hasib Tarafder

5,773
3
30
44

3

The only thing missing is that you should change 'show advanced options' back to 0 – cusman Nov 15 '20 at 23:33

score 17 · Answer 2 · edited Nov 04 '15 at 08:07

17

You may check the following command

sp_configure 'show advanced options', 1;
RECONFIGURE;
GO  --Added        
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=Seattle1;Trusted_Connection=yes;',
     'SELECT GroupName, Name, DepartmentID
      FROM AdventureWorks2012.HumanResources.Department
      ORDER BY GroupName, Name') AS a;
GO

Or this documentation link

edited Nov 04 '15 at 08:07

Félix Adriyel Gagnon-Grenier

8,422
10
52
65

answered Jan 27 '13 at 18:36

5

you need to add a 'GO' after the 1st 'RECONFIGURE;' otherwise it's a perfect solution – Aug 30 '13 at 07:08
1

don't you need to link the server before your SELECT ? – Sebastien H. Mar 21 '14 at 12:06

score 5 · Answer 3 · answered Jan 27 '17 at 14:41

If ad hoc updates to system catalog is "not supported", or if you get a "Msg 5808" then you will need to configure with override like this:

EXEC sp_configure 'show advanced options', 1
RECONFIGURE with override
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE with override
GO

score 3 · Answer 4 · edited Oct 22 '14 at 04:19

3

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

edited Oct 22 '14 at 04:19

Igor Borisenko

3,806
3
34
49

answered Jun 17 '13 at 15:59

user2129794

2,388
8
33
51

2

You don't need a GO after every line – Rob Aug 19 '15 at 07:14

How to enable Ad Hoc Distributed Queries

4 Answers4

Linked