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.