Password policy

Job details

Parameters

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

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.