Ad Hoc Distributed Queries

Job details

Name:	Ad Hoc Distributed Queries
Platform:	Sqlserver
Category:	Security
Premium package:	Security and compliance package
Description:	Checks if the OPENROWSET and OPENDATASOURCE functions can be used to connect to remote data sources that use OLE DB (DB2, Host File systems, Oracle, etc.).
Long description:	Checks if the OPENROWSET and OPENDATASOURCE functions can be used to connect to remote data sources that use OLE DB (DB2, Host File systems, Oracle, etc.).
Version:	1.2
Default schedule:	0 1 1 *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2005′ & hasengine=‘YES’ & eng_inst_priv = 0 & (engine_edition = ‘Microsoft SQL Server’	engine_edition = ‘Azure SQL Managed Instance’)]

Parameters

Name	Default value	Description
return status	1	Return status value (ALARM – 2, WARNING – 1, or OK – 0) when the “ad hoc distributed queries” parameter is enabled.
disable ad hoc distributed queries	NO	If set to “YES“ the alert will disable “ad hoc distributed queries” (if it is enabled) by running sp_configure stored procedure.
history threshold	365	The maximum number of days to keep statistics for in the historic tables.

Job Summary

Purpose: This monitoring job is designed to assess the status of “Ad Hoc Distributed Queries” in SQL Server instances to ensure it aligns with security best practices.
Why: “Ad Hoc Distributed Queries” enable the use of functions like OPENROWSET and OPENDATASOURCE for connecting to remote data sources. If improperly configured, this can present a significant security risk, allowing any authenticated login to access remote providers. This job helps in monitoring and potentially disabling this setting if deemed unsafe.
Manual checking: You can manually check the status of this setting in the SQL Server by using the following command:

SELECT value_in_use FROM master.sys.configurations WHERE name = 'Ad Hoc Distributed Queries'

Job Details

“Ad Hoc Distributed Queries”: Allows SQL Server to execute queries that use OLE DB providers to access remote databases and other data sources.
Monitoring Function: Checks if this feature is enabled and provides an alert based on configured thresholds.
Actions: Optionally disables this feature if it is found to be enabled, using the sp_configure stored procedure.

Dependencies

Object	Description	Cleanup On Fail
dbw_ad_hoc_distr_queries_proc	Main procedure carrying out the check.	true
dbw_ad_hoc_distr_queries_status_info	Table to store current status information.	true
dbw_ad_hoc_distr_queries_status_histr	History table for status information.	true

Tables and Indices Implementation

Creation of status and history tables to store monitoring data over time.
Implementation of clustered indexes to optimize queries on dates.

CREATE TABLE dbw_ad_hoc_distr_queries_status_info (value_in_use int, histr_date datetime)

CREATE CLUSTERED INDEX dbw_ad_hoc_dq_idx01 ON dbw_ad_hoc_distr_queries_status_info(histr_date)

CREATE TABLE dbw_ad_hoc_distr_queries_status_histr (value_in_use int, first_check datetime, last_check datetime, check_count int, histr_date datetime)

CREATE CLUSTERED INDEX dbw_ad_hoc_dq_h_idx01 ON dbw_ad_hoc_distr_queries_status_histr(histr_date)

Monitoring Logic Implementation

Procedure that executes the checks and reports based on the “Ad Hoc Distributed Queries” setting.
Logic includes querying configuration settings, updating history tables, handling exceptions, and possibly disabling the feature.
Security and compliance reporting is integrated to track changes and status.

Report Templates and Visualizations

Detailed current and historical configurations of the “Ad Hoc Distributed Queries” parameter.
Graphs and tables are configured to visually represent changes in the configuration state over time.

Upgrade Path

Includes an upgrade implementation that adjusts processes for procedural enhancements or bug fixes, retaining compatibility with earlier job versions.
Specific focus on ensuring parameter checks, dependencies, and compliance integrations are current and effective.

Compatibility and Installation

The job is designed for SQL Server instances (post-2005 versions) and adheres to security and configuration best practices.
Deployment checks ensure the SQL Server instance meets prerequisites before installation resumes.