V-79191 - Check if a member server inherits the password policy

[potatoqualitee]

I dont know how to do this

Ensure SQL Server is configured to inherit password complexity rules from the operating system for SQL logins.

[tboggiano]

I believe this is just the CHECK_POLICY = ON.

[potatoqualitee]

That is indeed true for the SQL Server part. The thing I'm looking at is the Windows server portion.

[tboggiano]

trying to read between the lines in MSDocs which is fun, they have three properites in bold under CHECK_POLICY then below tell us where we can go in Windows to check those properties to see if what they are set to, I'm still thinking it might be as simple as setting the checkbox, I'll keep looking around for more verification https://docs.microsoft.com/en-us/sql/relational-databases/security/password-policy?view=sql-server-ver15

[tboggiano]

From https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-login-transact-sql?view=sql-server-ver15:

CHECK_POLICY = { ON | OFF } Applies only to SQL Server logins. Specifies that the Windows password policies of the computer on which SQL Server is running should be enforced on this login. The default value is ON.

[potatoqualitee]

ohh looks like they give us the info!

Check the server operating system for password complexity:

Navigate to Start >> All Programs >> Administrative Tools >> Local Security Policy and to review the local policies on the machine. Account Policy >> Password Policy:

Ensure the DISA Windows Password Policy is set on the SQL Server member server.

Fix Text: Configure the SQL Server operating system and SQL Server logins for compliance. 

1. Ensure the DISA Windows Server 2012/2012 R2 Domain Controller Security Technical Implementation Guide password complexity guidelines are met. 

2. Ensure SQL Server is configured to inherit password complexity rules from the operating system for SQL logins. Ensure check of policy and expiration are enforced when SQL logins are created. 

CREATE LOGIN [] WITH PASSWORD=N'', CHECK_EXPIRATION=ON, CHECK_POLICY=ON