Ad Hoc Distributed Queries – SQL Server

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.

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