Detections
Analytics

4.2 Ensure 'CHECK_EXPIRATION' Option is set to 'ON' for All SQL Authenticated Logins Within the Sysadmin Role

Information

Applies the same password expiration policy used in Windows to passwords used inside SQL Server.

Rationale:

Ensuring SQL logins comply with the secure password policy applied by the Windows Server Benchmark will ensure the passwords for SQL logins with sysadmin privileges are changed on a frequent basis to help prevent compromise via a brute force attack. CONTROL SERVER is an equivalent permission to sysadmin and logins with that permission should also be required to have expiring passwords.

Solution

For each <login_name> found by the Audit Procedure, execute the following T-SQL statement:

ALTER LOGIN [<login_name>] WITH CHECK_EXPIRATION = ON;


Impact:

This is a mitigating recommendation for systems which cannot follow the recommendation to use only Windows Authenticated logins.

Regarding limiting this rule to only logins with sysadmin and CONTROL SERVER privileges, there are too many cases of applications that run with less than sysadmin level privileges that have hard-coded passwords or effectively hard-coded passwords (whatever is set the first time is nearly impossible to change). There are several line-of-business applications that are considered best of breed which have this failing.

Also, keep in mind that the password policy is taken from the computer's local policy, which is taken from the Default Domain Policy setting. Many organizations have a different password policy regarding the service accounts. These are handled in AD by setting the account's password to not expire and having some other process track when the password needs to be changed. With this second control in place, this is perfectly acceptable from an audit perspective. If you treat a SQL Server login as a service account, then you have to do the same. This ensures that the password change happens during a communicated downtime window and not arbitrarily.

Default Value:

CHECK_EXPIRATION is ON by default when using SSMS to create a SQL authenticated login.

CHECK_EXPIRATION is OFF by default when using T-SQL CREATE LOGIN syntax without specifying the CHECK_EXPIRATION option.

References:

https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms161959(v=sql.105)

https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008/ms161959(v=sql.100)

See Also

https://workbench.cisecurity.org/files/2834

Item Details

Category: ACCESS CONTROL

References: 800-53|AC-2(3), CSCv6|16.2, CSCv7|16.10

Plugin: MS_SQLDB

Control ID: 8e29d851815771adfe8b1d3963c1c5c243e44ea3d881563c5c3d78a6ebf30ed4