Ensure 'MUST_CHANGE' Option is set to 'ON' for All SQL Authenticated Logins

MediumLevel 1Manual reviewmssql

Description

Whenever this option is set to ON, SQL Server will prompt for an updated password the first time the new or altered login is used. Enforcing a password change after a reset or new login creation will prevent the account administrators or anyone accessing the initial password from misuse of the SQL login created without being noticed.

How it is checked

Audit method: Manual review

This recommendation cannot be checked automatically. A scan lists it with these instructions when you pass --include-manual.

Using SQL Server Management Studio 1. Open SQL Server Management Studio. 2. Open Object Explorer and connect to the target instance. 3. Navigate to the Logins tab in Object Explorer and expand. Right click on the desired login and select Properties. 4. Verify the User must change password at next login checkbox is checked. Using T-SQL SELECT name ,CAST(LOGINPROPERTY(log.name, N'IsMustChange') AS bit) AS [MustChangePassword],* FROM sys.server_principals AS log WHERE type = 'S' and CAST(LOGINPROPERTY(log.name, N'IsMustChange') AS bit) = 1 Returns "1" row for each new Login that has not been logged in to yet, and is correctly configured Note: This audit procedure is only applicable immediately after the login has been created or altered to force the password change. Once the password is changed, there is no way to know specifically that this option was the forcing mechanism behind a password change.

How it is fixed

Fix method: Guidance or infrastructure patch

Set the MUST_CHANGE option for SQL Authenticated logins when creating a login initially: CREATE LOGIN <login_name> WITH PASSWORD = '<password_value>' MUST_CHANGE, CHECK_EXPIRATION = ON, CHECK_POLICY = ON; Set the MUST_CHANGE option for SQL Authenticated logins when resetting a password: ALTER LOGIN <login_name> WITH PASSWORD = '<new_password_value>' MUST_CHANGE;

Properties

Operating systems: Any
Tags: authloggingmssqlsqlserver-2022
Reference: CIS Microsoft SQL Server 2022 Benchmark v1.3.0 - 4.1 Ensure 'MUST_CHANGE' Option is set to 'ON' for All SQL Authenticated Logins