Skip to content

Revoke SQL Logon To Local Group

Description

Using Windows authentication for a Microsoft SQL Server is preferred to using native SQL auth because it helps keep passwords out of connection strings, where attackers might find them. Once your Microsoft SQL Server is using Windows authentication, a logon could be mapped to an individual user (not recommended), a group defined local to the host server, or a group defined at domain level, or in Azure Active Directory (AAD). When logons are mapped to groups local to the host, group membership is difficult to audit, and a local administrator could inject unauthorized accounts.

Rationale

Microsoft SQL Server logons should be mapped to groups defined at domain level. Groups defined at local level are difficult to audit, and are easier to tamper with.

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
const { getServerSetting } = module

/**
 * @param {Object} databaseSettings - database settings object
 * @returns {boolean} true if Local groups are not logins
 */
function validate(databaseSettings) {
    const WindowsGroup = 'G'
    const name = getServerSetting(databaseSettings, "MachineName")

    const success = isEmptyArray(databaseSettings.users) ||
                    isEmptyArray(
                        databaseSettings.users.filter(user =>
                            user.sqlserver &&
                            user.sqlserver.source === 'sys.server_principals' &&
                            user.sqlserver.type &&
                            user.sqlserver.type === WindowsGroup &&
                            user.sqlserver.name &&
                            user.sqlserver.name.startsWith(name.concat('\\'))))

    return {
        success,
    }
}

validate(databaseSettings)