ad hoc distributed queries (server configuration option)
Applies to:
SQL Server
By default, SQL Server doesn't allow ad hoc distributed queries using OPENROWSET and OPENDATASOURCE. When this option is set to 1, SQL Server allows ad hoc access. When this option isn't set or is set to 0, SQL Server 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 SQL Server can access the provider. SQL Server 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 AdventureWorks2022.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
Povratne informacije
https://aka.ms/ContentUserFeedback.
Stiže uskoro: Tijekom 2024. postupno ćemo ukinuti servis Problemi sa servisom GitHub kao mehanizam za povratne informacije za sadržaj i zamijeniti ga novim sustavom za povratne informacije. Dodatne informacije potražite u članku:Pošaljite i pogledajte povratne informacije za