Secure Microsoft SQL Server with cnspec

Scan your Microsoft SQL Server instances to find security risks before they become incidents. cnspec evaluates server logins and roles, permissions, surface-area configuration, database ownership and trustworthiness, encryption, credentials, linked servers, SQL Agent proxies, and audit configuration.

cnspec connects over TDS and inventories the instance through read-only catalog queries against sys.* and msdb.*. It never touches your data.

If you're new to cnspec, start with the Quickstart. For an overview of every database cnspec can scan, see the database scanning overview.

Prerequisites

To scan SQL Server with cnspec, you must have:

• cnspec installed on your workstation
• Network access to the instance on its TDS port (1433 by default)
• A login that can read the instance catalog

Authenticate

cnspec authenticates with a SQL login, Windows (NTLM) integrated authentication, or a Microsoft Entra ID access token.

cnspec shell mssql sql.contoso.com --user sa --ask-pass

Windows or Entra authentication, and named instances:

cnspec shell mssql sql.contoso.com --instance SQL2022 --auth windows --user 'CONTOSO\auditor' --ask-pass
cnspec shell mssql sql.contoso.com --auth azure --user auditor@contoso.com --token ACCESS_TOKEN

Prefer a least-privileged login for auditing. VIEW ANY DEFINITION, or the ##MS_DefinitionReader## role on SQL Server 2022 and later, lets cnspec see all server principals and permissions. Without it some principals are simply invisible rather than causing a failure, so grant it before you trust a passing result.

Connection options

Verify with a quick SQL Server check

cnspec shell mssql sql.contoso.com --user sa --ask-pass

cnspec> mssql.server { version edition isMixedModeAuthEnabled }
mssql.server: {
  version: "Microsoft SQL Server 2022 (RTM-CU26) ... Developer Edition (64-bit) on Linux ..."
  edition: "Developer Edition (64-bit)"
  isMixedModeAuthEnabled: true
}

If cnspec connects and shows the version, you're ready to scan.

Scan SQL Server

By default cnspec discovers each online database as its own mssql-database asset alongside the instance asset:

cnspec scan mssql sql.contoso.com --user auditor --ask-pass

To scan the instance only:

cnspec scan mssql sql.contoso.com --user auditor --ask-pass --discover none

When a scan completes, cnspec prints a summary of all the checks it ran, grouped by policy, along with a risk score from 0 (no risk) to 100 (highest risk). Failed checks include remediation guidance to help you fix issues. To learn more about reading scan results, read Understand cnspec Results.

Mondoo doesn't yet ship an out-of-the-box SQL Server policy. The resources are shaped around the CIS Microsoft SQL Server Benchmark, so the checks below map closely to its recommendations. Use them as a starting point and create your own policies to meet your requirements.

Explore and test checks interactively

cnspec shell mssql sql.contoso.com --user auditor --ask-pass

List server logins

cnspec> mssql.server.logins { name type isDisabled }

Read a surface-area configuration option

cnspec> mssql.server.configurations.where(name == "clr enabled") { name valueInUse }
mssql.server.configurations.where: [
  0: {
    name: "clr enabled"
    valueInUse: 0
  }
]

List databases and their encryption state

cnspec> mssql.server.databases { name isTrustworthy isEncrypted ownerName stateDesc }

List linked servers

cnspec> mssql.server.linkedServers { name product provider dataSource isDataAccessEnabled }

Inspect a database's backup history

cnspec> mssql.server.databases.where(name == "TESTDB").first.backups { type isEncrypted keyAlgorithm }

List server audits

cnspec> mssql.server.audits { name isEnabled destination onFailure }

Example security checks

Ensure the sa login is disabled

cnspec> mssql.server.logins.where(name == "sa").all(isDisabled == true)
[ok] value: true

Ensure Windows authentication mode is enforced

Mixed mode leaves SQL logins usable, which puts password policy outside of Active Directory:

cnspec> mssql.server.isMixedModeAuthEnabled == false
[ok] value: true

Ensure the CLR is disabled

cnspec> mssql.server.configurations.where(name == "clr enabled").all(valueInUse == 0)
[ok] value: true

Ensure xp_cmdshell is disabled

xp_cmdshell runs operating system commands from inside the database engine:

cnspec> mssql.server.configurations.where(name == "xp_cmdshell").all(valueInUse == 0)
[ok] value: true

Ensure Ad Hoc Distributed Queries are disabled

cnspec> mssql.server.configurations.where(name == "Ad Hoc Distributed Queries").all(valueInUse == 0)
[ok] value: true

Ensure no database is marked Trustworthy

A Trustworthy database lets code inside it reach outside its own boundary, so a compromise of the database escalates to the instance:

cnspec> mssql.server.databases.none(isTrustworthy == true)
[ok] value: true

Ensure user databases are encrypted at rest

Database IDs 1 through 4 are the system databases (master, tempdb, model, msdb), so filtering above 4 leaves the databases you created:

cnspec> mssql.server.databases.where(databaseId > 4).all(isEncrypted == true)
[ok] value: true

Ensure backups are encrypted

cnspec> mssql.server.databases.all(backups.all(isEncrypted == true))
[ok] value: true

Ensure SQL logins enforce Windows password policy

A SQL login created without CHECK_POLICY sits outside password complexity and lockout rules entirely:

cnspec> mssql.server.logins.where(isActiveDirectoryPrincipal == false && isFixedRole == false).all(isPolicyChecked == true)
[ok] value: true

Ensure the instance forces encrypted client connections

cnspec> mssql.server.forceEncryption == true
[ok] value: true

Ensure at least one audit is running

An audit that is defined but not enabled records nothing:

cnspec> mssql.server.audits.any(isEnabled == true)
[ok] value: true

Ensure no CLR assembly is registered as UNSAFE

An UNSAFE_ACCESS assembly runs arbitrary code with no sandbox:

cnspec> mssql.server.databases.all(clrAssemblies.where(isUserDefined).none(permissionSet == "UNSAFE_ACCESS"))
[ok] value: true

Ensure no SQL Agent proxy is available to everyone

A proxy accessible to the msdb public role lets any job author run steps as the proxy's credential:

cnspec> mssql.server.proxyAccounts.none(isAccessibleToPublic == true)
[ok] value: true

Review linked servers and how they authenticate

Each linked server is a trust relationship to another instance. usesSelfCredential means the caller's own identity is forwarded, so the link inherits whatever the caller holds:

cnspec> mssql.server.linkedServers { name dataSource provider linkedLogins { localLogin remoteName usesSelfCredential } }

Review who holds CONTROL SERVER

cnspec> mssql.server.permissions.where(permissionName == "CONTROL SERVER") { granteeName state }

Learn more

• Microsoft SQL Server Resource Pack Reference: every SQL Server resource and field cnspec can query
• Write Effective MQL: guide to authoring checks and queries