Skip to content

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

Description

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.

Applies To

  • Databases

Tags

This rule is applied when the following tags are present:

Tag With Value
secureclouddb/provider aws
secureclouddb/service rds
secureclouddb/engine sqlserver

Default Rule

const { isEmptyArray } = module

/**
 * @param {Object} databaseSettings - database settings object
 * @returns {boolean} true if 'check expiration' is set to true
 */
function validate(databaseSettings) {
    const success = isEmptyArray(databaseSettings.users) ||
                    isEmptyArray(
                        databaseSettings.users.filter(user =>
                            user.sqlserver &&
                            user.sqlserver.source === 'sys.server_principals' &&
                            user.sqlserver.isExpirationChecked === false &&
    ((!isEmptyArray(user.sqlserver.roles) &&
        !isEmptyArray(user.sqlserver.roles.filter(role => role.roleName === 'sysadmin'))) ||
    (!isEmptyArray(user.sqlserver.permissions) &&
        !isEmptyArray(user.sqlserver.permissions.filter(permission => permission.type === 'CL' &&
        (permission.state === 'G' || permission.state === 'W')))))))

    return {
        success
    }
}

validate(databaseSettings)