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
- Purpose: To monitor and ensure compliance with password policies specifically checking if the “CHECK_POLICY“ option is turned “ON” for all SQL authenticated logins on Microsoft SQL Server.
- Why: Proper password policies enhance database security by enforcing complexity requirements and regular updates. If the “CHECK_POLICY“ is set to “OFF“, it might lead to weaker passwords making the database vulnerable to unauthorized access.
- Manual checking: To manually check whether the password policy is being enforced, you can use the following SQL command:
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:
- Tables are created to store current statuses and historical data of password policy checks:
- “dbw_password_policy_info” for current login names and whether their policy is checked. – “dbw_password_policy_histr” for historical records indicating when policies were checked. - A stored procedure “dbw_password_policy_proc” is central to the implementation, handling the logic to: – Insert new records of logins not enforcing the password policy. – Optionally adjust the policy setting of user accounts to enforce the policy if enabled.
- Dependencies include procedures and tables that are crucial for maintaining and updating records: – Main procedure “dbw_password_policy_proc” – Tables “dbw_password_policy_info” and “dbw_password_policy_histr” for status and history tracking.
Reports and Monitoring
Two reports are generated from this job to aid in monitoring:
- “Password Policy” report providing a list of logins with the policy set to “OFF“.
- Displays login names and their current policy status. - “Aggregated Statistics” which plots historical data showing the changes in policy enforcement over time. – Visualizes data limited to the number of logins specified by “max logins per graph” parameter.
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.