Skip to content

Disable SQL Authentication In Contained Database

Description

A contained database has no external dependencies, and does not depend on server level logons. User accounts should be mapped to domain or Azure Active Directory accounts, not local SQL authentication.

For more information, see Contained Database Authentication: Introduction, and also, contained database authentication Server Configuration Option

Rationale

Users that authenticate using SQL authentication may re-use passwords, and will require passwords in the connection strings, which is difficult to do securely.

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 Sql Auth is not used
 */
function validate(databaseSettings) {
    let namesToIgnore = ['dbo', 'Information_Schema', 'sys', 'guest']
    let typesToCheck = ['U','S','G']

    const success = isEmptyArray(databaseSettings.users) ||
                    isEmptyArray(
                        databaseSettings.users.filter(user =>
                            isDatabaseContainmentOn(databaseSettings.databases, user.sqlserver.parentDatabase) &&
                            user.sqlserver &&
                            user.sqlserver.source === 'sys.database_principals' &&
                            user.sqlserver.authenticationType === 2 &&
                            !namesToIgnore.includes(user.sqlserver.name) &&
                            typesToCheck.includes(user.sqlserver.type)))

    return {
        success,
    }
}

function isDatabaseContainmentOn(databases, parentDatabase) {
    const success = isEmptyArray(databases.filter(database =>
                        database.sqlserver.containment !== 0 &&
                        database.sqlserver.name === parentDatabase))

    return {
        success,
    }
}

validate(databaseSettings)