What’s the issue?
The Ad Hoc Distributed Queries server configuration option controls whether SQL Server allows the use of OPENROWSET and OPENDATASOURCE functions to access external data sources without first defining a linked server. When enabled, queries can connect to other databases, files, and OLE DB providers on the fly using credentials and connection strings supplied directly in the query.
This finding identifies instances where Ad Hoc Distributed Queries is set to 1. The setting is off by default and is enabled either intentionally to support specific data integration patterns or as a side effect of installation scripts and vendor applications that use these functions.
The feature provides flexibility for one off data access scenarios, but it also expands the attack surface of the instance in ways that warrant review.
Why is this a problem?
Ad hoc distributed queries bypass the normal linked server configuration, which means there is no central place to review or audit which external sources can be accessed. Any user with the necessary permissions can construct a connection string and reach external data sources directly, including network shares, other database servers, and any OLE DB accessible target.
The feature is a known SQL injection amplifier. When an injection vulnerability exists in an application connecting with sufficient privileges, ad hoc distributed queries give the attacker a path to reach external systems, exfiltrate data to attacker controlled endpoints, or read local files through providers such as the file system OLE DB provider.
Connection strings used with OPENROWSET and OPENDATASOURCE often contain embedded credentials, which means passwords appear in plain text in query text, plan cache entries, and any logs that capture queries. This produces credential leakage that is hard to detect and remediate, particularly when the same credentials are reused across multiple queries or applications.
Linked servers, while not without their own security considerations, provide a defined configuration with explicit security mappings, auditable definitions, and centralized management. Ad hoc distributed queries provide essentially the same external access without any of these controls, which is why disabling the feature is the recommended default.
What should you do about this?
Determine whether the feature is actually being used by reviewing application code, stored procedures, scheduled jobs, and ETL processes for references to OPENROWSET or OPENDATASOURCE. Capture any uses found and identify whether they are still required.
If the feature is not in active use, disable it with EXEC sp_configure ‘Ad Hoc Distributed Queries’, 0; RECONFIGURE;. The change takes effect immediately and prevents future ad hoc queries to external sources without affecting existing linked server based access.
For each remaining use case, evaluate whether the requirement can be met by replacing ad hoc queries with linked servers. Linked servers provide the same external connectivity with defined security context, central configuration, and proper auditing, and they avoid the credential exposure that comes with embedded connection strings.
If the feature must remain enabled, restrict who can use it by limiting the ADMINISTER BULK OPERATIONS permission and reviewing which logins have the elevated privileges (sysadmin, CONTROL SERVER) that allow unrestricted ad hoc query access. Add the configuration to your standard health check process so its state remains intentional and any unexpected enablement is detected promptly.
Read more…
Server Configuration: Ad Hoc Distributed Queries – SQL Server | Microsoft Learn