Password policy


Job details

Name: Password policy
Platform: Sqlserver
Category: Security
Premium package: Security and compliance package
Description: Checks that CHECK_POLICY option is set to ‘ON’ for all SQL authenticated logins.
Long description: Checks that CHECK_POLICY option is set to ‘ON’ for all SQL authenticated logins.
Version: 1.21
Default schedule: 21 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 CHECK_POLICY option is set to “OFF“.
enable check policy NO If set to “YES“ the alert will alter the login by running “ALTER LOGIN [login_name] ] WITH CHECK_POLICY = ON”.
history threshold 365 The maximum number of days to keep statistics for in the historic tables.
max logins per graph 5 The maximum number of logins visualized in the report graphs.

Job Summary

SELECT name, is_policy_checked FROM sys.sql_logins WHERE is_policy_checked = 0 AND is_disabled = 0

Details of Implementation

The job involves several operations outlined in the XML script:

Reports and Monitoring

Two reports are generated from this job to aid in monitoring:

Dependencies Visualized as Table

Object Description Object Type Cleanup on fail
dbw_password_policy_proc Main procedure. 2 true
dbw_password_policy_info Current status table. 0 true
dbw_password_policy_histr History table. 0 true

Overall, this monitoring job in dbWatch Control Center plays a critical role in ensuring that security standards are adhered to with respect to password settings on SQL Server databases.