ad-hoc-distributed-queries-server-configuration-option.md

title	ad hoc distributed queries Server Configuration Option | Microsoft Docs
ms.custom
ms.date	2017-03-02
ms.prod	sql-server-2016
ms.reviewer
ms.suite
ms.technology	database-engine
ms.tgt_pltfrm
ms.topic	article
helpviewer_keywords	OPENROWSET function, ad hoc distributed queries option Ad Hoc Distributed Queries option ad hoc distributed queries 7415 (Database Engine Error) OPENDATASOURCE function, ad hoc distributed queries option ad hoc access
ms.assetid	5b982015-e196-44c3-83b8-275fb9d769b2
caps.latest.revision	29
author	BYHAM
ms.author	rickbyh
manager	jhubbard

ad hoc distributed queries Server Configuration Option

[!INCLUDEtsql-appliesto-ss2008-xxxx-xxxx-xxx_md]

By default, [!INCLUDEssNoVersion] does not allow ad hoc distributed queries using OPENROWSET and OPENDATASOURCE. When this option is set to 1, [!INCLUDEssNoVersion] allows ad hoc access. When this option is not set or is set to 0, [!INCLUDEssNoVersion] does not allow ad hoc access.

Ad hoc distributed queries use the OPENROWSET and OPENDATASOURCE functions to connect to remote data sources that use OLE DB. OPENROWSET and OPENDATASOURCE should be used only to reference OLE DB data sources that are accessed infrequently. For any data sources that will be accessed more than several times, define a linked server.

Important

Enabling the use of ad hoc names means that any authenticated login to [!INCLUDEssNoVersion] can access the provider. [!INCLUDEssNoVersion] administrators should enable this feature for providers that are safe to be accessed by any local login.

Remarks

Attempting to make an ad hoc connection with Ad Hoc Distributed Queries not enabled results in error: Msg 7415, Level 16, State 1, Line 1

Ad hoc access to OLE DB provider 'Microsoft.ACE.OLEDB.12.0' has been denied. You must access this provider through a linked server.

Examples

The following example enables ad hoc distributed queries and then queries a server named Seattle1 using the OPENROWSET function.

sp_configure 'show advanced options', 1;  
RECONFIGURE;
GO 
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  

See Also

Server Configuration Options (SQL Server)
Linked Servers (Database Engine)
OPENROWSET (Transact-SQL)
OPENDATASOURCE (Transact-SQL)
sp_addlinkedserver (Transact-SQL)