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

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

Job Details

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

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

Report Templates and Visualizations

Upgrade Path

Compatibility and Installation