Default trace enabled

Job details

Name:	Default trace enabled
Platform:	Sqlserver
Category:	Security
Premium package:	Security and compliance package
Description:	Checks that ‘Default Trace Enabled’ server configuration option is set to ‘1′.
Long description:	Checks that ‘Default Trace Enabled’ server configuration option is set to ‘1′. The default trace provides audit logging of database activity including changing of accounts and privileges.
Version:	1.2
Default schedule:	9 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’)]

Name	Default value	Description
return status	1	Return status value (ALARM – 2, WARNING – 1, or OK – 0) if the instance is exposed by SQL Browser (for Non-clustered SQL Server instances).
enable default trace	NO	If set to “YES“ the alert will enable “default trace enabled” by running sp_configure stored procedure.
history threshold	365	The maximum number of days to keep statistics for in the historic tables.

Purpose: The purpose of this job is to ensure that the “Default Trace Enabled” server configuration setting is active (set to “1″) on SQL Server. This setting is crucial for maintaining audit trails and monitoring database change activities, which include, but are not limited to, account changes and privilege modifications.
Why: This job is important as it ensures that the default system trace for auditing is enabled, which is vital for security and compliance monitoring. Not having this setting enabled can lead to lacking crucial logging information which, in event of an audit or security breach, can be detrimental to understanding the scope and point of entry of the breach.
Manual checking: To check this setting manually in the SQL Server database, you can use the following SQL command:

SELECT value_in_use FROM sys.configurations WHERE name = 'default trace enabled';

The job utilizes several SQL objects to monitor and enforce the default trace status:
- Main stored procedure (dbw_default_trace_enabled_proc): This procedure runs checks, updates trace status if required, and logs results.
- Information table (dbw_default_trace_enabled_info): Stores current config information.
- History table (dbw_default_trace_enabled_histr): Logs changes over time to maintain a history of the default trace status.

The monitoring procedure assesses the current status of the default trace setting.
If the trace is disabled and the alert parameter “enable default trace” is set to “YES“, the job attempts to enable it via the sp_configure stored procedure.
Regular checks and updates are made to ensure there’s a record of when changes occur, maintaining an audit trail.
Historical data is pruned according to the “history threshold” parameter, which determines how long to keep data (default 365 days).

Three main reports are generated:
- Current configuration: Displays active configurations joined with their status.
- Last day configuration history: Visualizes changes over the last day.
- Aggregated statistics: Shows summarized activity, such as how frequently the configuration check is performed, over a specified history threshold.

The job runs scheduled checks and enforces changes as specified in an automated manner.
It provides automated alerts and data visualizations that assist in quick assessments of the database’s compliance status.
Upgrades from previous versions are handled, maintaining continuity and ensuring that current settings and historical data are preserved according to the new job specifications.