Password expiration (sysadmin)
Job details
| Name: | Password expiration (sysadmin) | |
| Platform: | Sqlserver | |
| Category: | Security | |
| Premium package: | Security and compliance package | |
| Description: | Checks that CHECK_EXPIRATION option is set to ‘ON’ for all SQL authenticated logins within the sysadmin role. | |
| Long description: | Checks that CHECK_EXPIRATION option is set to ‘ON’ for all SQL authenticated logins within the sysadmin role. | |
| Version: | 1.2 | |
| Default schedule: | 20 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_EXPIRATION option is set to “OFF“. |
| enable check expiration | NO | If set to “YES“ the alert will alter the login by running “ALTER LOGIN [login_name] ] WITH CHECK_EXPIRATION = ON”. |
| 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 CHECK_EXPIRATION option is set to “ON” for all SQL authenticated logins within the sysadmin role.
- Why: This job is critical to maintain security standards by ensuring password policies are enforced for sysadmin role users. Not enforcing password expiration could lead to increased risk of unauthorized access through old or compromised credentials.
- Manual Checking: To check this manually in the database, you can issue the following SQL commands:
SELECT name, is_expiration_checked FROM sys.sql_logins WHERE IS_SRVROLEMEMBER('sysadmin', name) = 1;Job Details
- “Artifact ID”: pwd_expiration_sysadmin
- “Group”: job.dbwatch.com
- “Name”: Password expiration (sysadmin)
- “Object”: dbw_pwd_expiration_sysadmin_proc
- “Description”: Monitors and adjusts password expiration enforcement for sysadmin role.
Execution & Dependencies
- “Main procedure”: dbw_pwd_expiration_sysadmin_proc
- “Info Table”: dbw_pwd_expiration_sysadmin_info – Stores current status of login checks.
- “History Table”: dbw_pwd_expiration_sysadmin_histr – Maintains a log of historical data on checks performed.
Implementation
The job involves creating tables for capturing real-time login status and history, and a stored procedure to check, log, and potentially alter the expiration status of each login found without proper expiration settings:
CREATE PROCEDURE dbw_pwd_expiration_sysadmin_procCREATE TABLE dbw_pwd_expiration_sysadmin_infoCREATE TABLE dbw_pwd_expiration_sysadmin_histrTables are indexed based on the history date to optimize data retrieval and management operations. The procedure checks each SQL login within the sysadmin role, verifies if the check expiration is on, and logs the information. If directed by the parameters, it alters the login to enforce password expiration.
Parameters
- “return status”: Determines the alert level in cases where the CHECK_EXPIRATION option is set to “OFF“.
- “enable check expiration”: Controls whether the procedure should automatically set CHECK_EXPIRATION to “ON” for unchecked logins.
- “history threshold”: Defines how many days the statistics are kept in the historical table.
Reporting & Compliance Alignment
The process is aligned with compliance requirements:
- “Compliance Type”: Access Control
The job reports generate listing of logins along with their password expiration status to help in compliance audits and operational checks.
Upgrades and Compatibility
The monitoring job supports upgrades from version 1.1 and is designed to work with Microsoft SQL Server instances version 2005 and above, including Azure SQL Managed Instances. It ensures that only valid instances are selected for execution, enhancing performance and reducing unnecessary workload.