| title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | helpviewer_keywords | ||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Server configuration: Ad Hoc Distributed Queries |
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. |
rwestMSFT |
randolphwest |
10/18/2024 |
sql |
configuration |
conceptual |
|
[!INCLUDE SQL Server]
By default, [!INCLUDE ssNoVersion] doesn't allow ad hoc distributed queries using OPENROWSET and OPENDATASOURCE. When this option is set to 1, [!INCLUDE ssNoVersion] allows ad hoc access. When this option isn't set or is set to 0, [!INCLUDE ssNoVersion] 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 are accessed more than several times, define a linked server.
Enabling the use of ad hoc names means that any authenticated [!INCLUDE ssNoVersion] account can access the provider. [!INCLUDE ssNoVersion] administrators should enable this feature for providers that are safe for any local account to access.
If you attempt to make an ad hoc connection with Ad Hoc Distributed Queries disabled, you 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.
See the Features comparison: Azure SQL Database and Azure SQL Managed Instance for reference.
The following example enables Ad Hoc Distributed Queries and then queries a server named Seattle1 using the OPENROWSET function.
USE master;
GO
EXECUTE sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXECUTE sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
SELECT a.*
FROM OPENROWSET ('MSOLEDBSQL', 'Server=Seattle1;Trusted_Connection=yes;', 'SELECT GroupName, Name, DepartmentID
FROM AdventureWorks2022.HumanResources.Department
ORDER BY GroupName, Name') AS a;
GO
EXECUTE sp_configure 'show advanced options', 0;
GO
RECONFIGURE;
GO