Ole Automation Procedures

Job details

Name:	Ole Automation Procedures
Platform:	Sqlserver
Category:	Security
Premium package:	Security and compliance package
Description:	Checks whether OLE Automation objects can be instantiated within Transact-SQL batches.
Long description:	Checks whether OLE Automation objects can be instantiated within Transact-SQL batches which allow SQL Server users to execute functions external to SQL Server.
Version:	1.2
Default schedule:	18 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 “Ole Automation Procedures” parameter is enabled.
disable Ole Automation Procedures	NO	If set to “YES“ the alert will disable “Ole Automation Procedures” (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: The primary function of this specific dbWatch monitoring job is to check the status and govern the usability of OLE Automation Procedures within SQL Server.
Why: This job is crucial to ensuring the security and operational effectiveness of the database system. OLE Automation can introduce potential risks if misused, as it allows SQL procedures to execute external processes. By monitoring its activation, the system mitigates unintended or malicious uses, potentially preventing security breaches.
Manual Checking: You can check the status of OLE Automation Procedures manually by querying the SQL Server system view with the following command:

SELECT value_in_use FROM sys.configurations WHERE name = 'Ole Automation Procedures';

Job Details

Implementation Components

The task involves creating and updating several SQL Server objects and procedures. This includes:
- Tables:
  - “dbw_ole_automation_procedures_status_info” for current status data.
  - “dbw_ole_automation_procedures_status_histr” for historical data.
- Indexes:
  - Clustered Indexes on the aforementioned tables for improved query performance.
- Stored Procedures:
  - “dbw_ole_automation_procedures_proc” to handle the checks and potential deactivation of OLE Automation Procedures.

Table: Dependency Overview

Object	Type	Description
dbw_ole_automation_procedures_proc	Main procedure	Manages the core checking and logging
dbw_ole_automation_procedures_status_info	Current status table	Stores current activation status
dbw_ole_automation_procedures_status_histr	History table	Aggregates and archives data over time

Operational Logic

Current Status Check:
- Checks if the “Ole Automation Procedures” configuration is active within the SQL Server instance. Initiates appropriate response based on current settings and defined parameters.
Parameter Management:
- Allows for the automation setting to be disabled depending on the job configuration if found enabled, ensuring compliance with security policies.
Historical Data Management:
- Maintains and periodically purges the historical records based on a retention threshold.

Exception Handling

The procedure includes comprehensive TRY and CATCH SQL blocks to manage any exceptions during runtime, ensuring that all errors are logged and appropriately handled without crashing the system process.

Reporting and Monitoring

Report Templates: Incorporated within the job are SQL scripts designed to fetch configurations and historical data relating to the OLE Automation Procedures. This includes current settings, day-wise status changes, and aggregated statistics.
Visualizations and Tables: Provide visual and tabular representations of the data collected by the monitoring job, offering readable insights for system audits or security assessments.

By continuously checking and managing the OLE Automation Procedures, this dbWatch job plays an essential role in maintaining system security and operational integrity, enabling database administrators to have fine control over database configurations.