Database Mail XPs
Job details
| Name: | Database Mail XPs | |
| Platform: | Sqlserver | |
| Category: | Security | |
| Premium package: | Security and compliance package | |
| Description: | Checks the ability to generate and transmit email messages from SQL Server. | |
| Long description: | Checks the ability to generate and transmit email messages from SQL Server. | |
| Version: | 1.2 | |
| Default schedule: | 8 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 “Database Mail XPs” parameter is enabled. |
| disable Database Mail XPs | NO | If set to “YES“ the alert will disable “Database Mail XPs” (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 check and manage the status of the Database Mail XPs in SQL Server, which are extended procedures that enable database mail functionalities.
- Why: It is critical to ensure that the Database Mail XPs setting is correctly configured to prevent unauthorized mail activities and ensure the system’s communication capabilities are functioning as intended. Monitoring changes and the current state of this setting helps maintain security and compliance standards.
- Manual checking: To check the status of Database Mail XPs manually within the database, the following SQL command can be used:
SELECT value_in_use FROM sys.configurations WHERE name = 'Database Mail XPs';Dependencies
- This job has several dependencies crucial for its execution:
- Main procedure named “dbw_database_mail_xps_proc,” needed for assessing the current state of Database Mail XPs.
- Tables “dbw_database_mail_xps_status_info” (stores current status) and “dbw_database_mail_xps_status_histr” (stores historical status data) are essential for tracking changes and attempt history of Database Mail XP configurations.
Implementation Details
The job includes SQL script implementations for:
- Creating necessary tables to hold current and historical configuration states.
- A stored procedure that performs checks on the Database Mail XPs setting and acts accordingly (enabling or disabling) depending on the job parameters set.
- Inserting or updating records in configuration and status-tracking tables as required.
Reporting
This job generates several reports for different purposes:
- “Current configuration” – Displays current settings of Database Mail XPs.
- “Last day configuration history” – Shows historical data of the settings to track changes over time.
- “Aggregated statistics” – Aggregates data to see trends and to assess frequency of changes in the settings.
Tables
| Column Name | Description | Data Type |
|---|---|---|
| configuration_id | ID of the configuration | Integer |
| name | Name of the parameter | String |
| value | Configured value | Integer |
| minimum | Minimum acceptable value | Integer |
| maximum | Maximum acceptable value | Integer |
| value_in_use | Value currently in use | Integer |
| description | Description of the configuration | String |
| is_dynamic | Indicates if the setting is dynamic | Boolean |
| is_advanced | Indicates if the setting is advanced | Boolean |
Upgrade Notes
- The procedure from version 1.1 to 1.2 includes improvements in managing Database Mail XPs, especially concerning handling exceptions and cleaning up old statistics to ensure performance and maintainability.
- Previous configurations and methods are maintained with improvements focusing on robust handling and error reporting.
This script is essential for administrators to ensure SQL Server’s capacity to send database mail, which is often used for alerting and notifications, remains compliant with security policies and performance standards.