Login audit setting

Job details

Name:	Login audit setting
Platform:	Sqlserver
Category:	Security
Premium package:	Security and compliance package
Description:	Checks if the SQL Server login audit is set to value ‘2′ (none = 0, successful logins only = 1, failed logins only = 2, both failed and successful logins = 3).
Long description:	Checks if the SQL Server login audit is set to value ‘2′ (none = 0, successful logins only = 1, failed logins only = 2, both failed and successful logins = 3). After changing this option the SQL Server Instance must be restarted.
Version:	1.2
Default schedule:	15 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) if the SQL Server login audit is not set to value “2″.
history threshold	365	The maximum number of days to keep statistics for in the historic tables.

Job Summary

Purpose: The purpose of this job is to monitor and ensure that the SQL Server login audit setting is set to monitor “failed logins only” (value 2), which is crucial for security and compliance purposes.
Why: Monitoring this setting is important because ensuring the right audit configuration helps in detecting unauthorized or failed login attempts, thereby increasing the database’s security posture. If the setting is incorrect, the system may either log unnecessary information or fail to log crucial security events.
Manual Checking: You can check the current audit settings manually in the database by executing the following SQL query:

EXEC xp_instance_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', @value_name='AuditLevel'

Job Implementation Details

The job involves creating procedures and tables that handle the storage and update of audit setting values, and the updates entail ensuring that the tracking data remains current and relevant.
The core procedure “dbw_login_audit_setting_proc” reads the current audit settings directly from the SQL Server registry and compares the retrieved value.
Depending on the current setting, it will update the tables that maintain these settings’ history and insert/delete necessary data for tracking changes over time.
The job setup also includes creating clustered indexes on the tables used to store the audit settings which optimize query performance.

Dependencies and Cleanup

SQL objects used:
- Main procedure “dbw_login_audit_setting_proc” which determines and records the current audit settings.
- Table “dbw_login_audit_setting_info” which stores the current status of the audit settings.
- Table “dbw_login_audit_setting_histr” which accumulates historical changes.
Each dependency has a cleanup-on-fail property ensuring that in case of a failure, any intermediate changes are not left in the database.

Reporting and Visualization

Login audit settings report:
- Presents current settings and historical data.
- Current configuration table retrieves and shows the setting as “none”, “successful logins only”, “failed logins only”, or “both failed and successful logins” with the date it was last checked.
- Aggregated statistics include a bar chart visualizing the number of checks carried out per day over a set period as specified by “history threshold”.

Upgrade Notes

The upgrade from previous versions involves altering the procedure to handle new checks and ensure compatibility with newer SQL Server versions.
Express installation is not supported, and a forced installation is required, ensuring that the system applies updates even in strict environments.

Metadata and Compliance

Compliance type tagged as: “none:Access Control” indicating the role of this job within SQL Server access control compliance.

This thorough description reflects how dbWatch Control Center is utilized to monitor and enforce crucial security settings within SQL Server environments, helping maintain strict access and auditing controls.