Ad Hoc Distributed Queries – SQL Server

Ad Hoc Distributed Queries – SQL Server

2 Minutes Read Updated on May 21, 2025

What are Ad Hoc Queries?

An ad-hoc query is an unscheduled data inquiry, typically created in response to questions that cannot be addressed using predetermined or predefined datasets.

What is the use of Ad Hoc Distributed Queries?

Ad hoc distributed queries utilize the OPENROWSET (Transact-SQL) and OPENDATASOURCE (Transact-SQL) functions for establishing connections with remote data sources employing OLE DB. It’s advisable to employ OPENROWSET and OPENDATASOURCE solely for referencing OLE DB data sources that are accessed on an occasional basis.

Transact-SQL (T-SQL) is the language used for sending commands to communicate with a Microsoft SQL Server database and other tools and applications.

IT infrastructure

Ad Hoc Distributed Queries’ Server Configuration Option is set to ‘0’

Disabling this security setting is recommended. The reasoning is this feature enables remote access and exploitation of vulnerabilities on SQL Server instances, as well as the execution of unsafe Visual Basic for Application functions.

Vulnerability

Ad hoc distributed queries pose a security threat as they permit users to execute remotely arbitrary T-SQL statements against a linked server and to run unsafe application functions. This poses a risk if the linked server is connected to a sensitive data source like a production database, or if the T-SQL statement is maliciously designed to perform unintended actions.

SQL CIS Benchmark Guide

How to check if ad hoc distributed queries is enabled in SQL?

By default, SQL Server does not permit ad hoc distributed queries using OPENROWSET and OPENDATASOURCE. When this option is enabled (set to 1), SQL Server allows ad hoc access. If the option is disabled or not set (set to 0), ad hoc access is not permitted.

How to disable ad hoc distributed queries?

To disable ad-hoc distributed queries on a SQL Server instance, run the following T-SQL command:

Run the following command to enable the advanced options:

EXECUTE sp_configure ‘show advanced options’, 1;

RECONFIGURE;

Run the following command to disable ad-hoc distributed queries:

EXECUTE sp_configure ‘Ad Hoc Distributed Queries’, 0;

RECONFIGURE;

Run the following command to disable the advanced options:

EXECUTE sp_configure ‘show advanced options’, 0;

RECONFIGURE;

Restart the SQL Server service to apply the changes.

If you need to re-enable these features, you can use the same steps, but set the value to 1 instead of 0.

Successful Automated Configuration

This configuration should be set to ‘0’.

Automated hardening configurations can help organizations ensure that adhoc query in SQL is configured in compliance with industry standards, regulatory requirements, and internal security policies. This reduces the risk of non-compliance and potential fines or penalties.

Understanding SQL Hardening

Ben Balkin
Ben Balkin is a professional writer and blogger specializing in technology and innovation. As a contributor to the Calcom blog, Ben shares practical insights, useful tips, and engaging articles designed to simplify complex processes and make advanced technological solutions accessible to everyone. His writing style is clear, insightful, and inspiring, reflecting his strong belief in technology's power to enhance quality of life and empower businesses.

Related Articles

Understanding Allow anonymous SID/Name translation in Windows

Understanding Allow anonymous SID/Name translation in Windows

April 12, 2024

What is SID? In a network each user, whether verified or not, is given a…

X Display Manager Control Protocol (XDMCP) Explained

X Display Manager Control Protocol (XDMCP) Explained

December 22, 2024

What is XDMCP  X Display Manager Control Protocol (XDMCP) is a remote desktop protocol using…

How to disable TLS 1.0

How to disable TLS 1.0

September 9, 2024

Why Disable TLS 1.0 Transport Layer Security (TLS) is a cryptographic protocol designed to provide…

Ready to simplify compliance?

See automated compliance in action—book your demo today!

Share this article