Skip to content

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

Latest commit

 

History

History
68 lines (52 loc) · 3.47 KB

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

File metadata and controls

68 lines (52 loc) · 3.47 KB
title ad hoc distributed queries (server configuration option)
description Find out how to enable ad hoc distributed queries in SQL Server. You can then use OPENROWSET and OPENDATASOURCE to connect to remote OLE DB data sources.
author rwestMSFT
ms.author randolphwest
ms.date 04/18/2022
ms.service sql
ms.subservice configuration
ms.topic conceptual
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

ad hoc distributed queries (server configuration option)

[!INCLUDE SQL Server]

By default, [!INCLUDEssNoVersion] doesn't allow ad hoc distributed queries using OPENROWSET and OPENDATASOURCE. When this option is set to 1, [!INCLUDEssNoVersion] allows ad hoc access. When this option isn't set or is set to 0, [!INCLUDEssNoVersion] doesn't 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.

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

If you attempt to make an ad hoc connection with ad hoc distributed queries disabled, you'll see the following 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('MSOLEDBSQL', 'Server=Seattle1;Trusted_Connection=yes;',  
     'SELECT GroupName, Name, DepartmentID  
      FROM AdventureWorks2012.HumanResources.Department  
      ORDER BY GroupName, Name') AS a;  
GO

Azure SQL Database and Azure SQL Managed Instance

See the Features comparison: Azure SQL Database and Azure SQL Managed Instance for reference.

See also