Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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 are accessed more than several times, define a linked server.
Enabling the use of ad hoc names means that any authenticated SQL Server account can access the provider. SQL Server 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
Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayTraining
Module
Introduction to SQL Server 2022 data virtualization - Training
Learn about data virtualization, how to use Polybase to access and query external data, and enhanced Polybase features in SQL Server 2022.
Certification
Microsoft Certified: Azure Database Administrator Associate - Certifications
Administer an SQL Server database infrastructure for cloud, on-premises and hybrid relational databases using the Microsoft PaaS relational database offerings.
Documentation
Configuring permissions to access remote data - SQL Server
This article describes how to disable ad hoc queries that use the OPENROWSET or the OPENDATASOURCE functionality in SQL Server.
Create linked servers - SQL Server
Create linked servers (SQL Server Database Engine)
Database Engine events and errors (7000 to 7999) - SQL Server
Consult this SQL Server error code list (between 7000 and 7999) to find explanations for error messages for SQL Server database engine events.