Documentation

1. Purpose

This guide explains how to configure the CBOM Database TDE sensor (Discover_DB) to discover cryptographic assets managed by Microsoft SQL Server's Transparent Data Encryption (TDE) feature.

The sensor connects to SQL Server using a read-only login and queries system catalog views to inventory the following cryptographic material:

Database encryption keys (DEKs) — symmetric keys used to encrypt each TDE-protected database, discovered via sys.dm_database_encryption_keys
Certificates — server-level certificates that protect DEKs, discovered via sys.certificates
Asymmetric keys — RSA or ECC keys used in place of certificates for DEK protection, discovered via sys.asymmetric_keys
Symmetric keys — additional symmetric keys defined at the server or database scope, discovered via sys.symmetric_keys

For each asset the sensor records metadata including name, algorithm, thumbprint, key length, and creation/expiry dates. The sensor never decrypts data, never exports key material, and never performs any write operations.

2. Prerequisites

Before beginning this setup, ensure the following requirements are met:

SQL Server version — SQL Server 2008 or later (any edition that supports TDE). SQL Server 2019 or later is recommended for full catalog view coverage.
Network access — The host running the CBOM sensor must be able to reach the SQL Server instance on TCP port 1433 (or the configured custom port). Firewalls and security groups must permit this traffic.
Authentication mode — The SQL Server instance must be configured for SQL Server Authentication (Mixed Mode) or the CBOM service account must be mapped to a Windows login if using Windows Authentication.
Administrative access — You must have sysadmin or securityadmin rights on the target SQL Server to create the login and grant the required permissions.
CBOM platform access — You must have CBOM administrator access to create and deploy a new Discover_DB sensor configuration.
TDE enabled — At least one database should have TDE configured (ALTER DATABASE ... SET ENCRYPTION ON) for the sensor to return meaningful results. The sensor will still enumerate certificates and keys regardless.

3. Create a SQL Server Login for CBOM

The CBOM sensor requires a dedicated SQL Server login with read-only access to system catalog views. Do not reuse existing application or administrative accounts.

3.1 Connect to the SQL Server Instance

Open SQL Server Management Studio (SSMS) or any compatible SQL client (e.g., sqlcmd, Azure Data Studio).
Connect to the target SQL Server instance using an account with sysadmin or securityadmin privileges.
Select the master database context — login creation must occur in the master database.

USE master;
GO

3.2 Create the CBOM Reader Login

Run the following statement to create a SQL Server login. Replace the placeholder password with a strong, randomly generated value meeting your organization's password policy.

CREATE LOGIN cbom_reader
    WITH PASSWORD = 'R3pl@ceW1thStr0ngP@ssword!',
         DEFAULT_DATABASE = master,
         CHECK_EXPIRATION = ON,
         CHECK_POLICY = ON;
GO

Confirm the login was created successfully:

SELECT name, type_desc, is_disabled, create_date
FROM sys.server_principals
WHERE name = 'cbom_reader';
GO

Expected output: one row with type_desc = SQL_LOGIN and is_disabled = 0.

4. Grant Required Permissions

The CBOM sensor requires three server-level permissions to read cryptographic metadata across all databases. No database-level roles or data-read permissions are needed.

4.1 Grant Server-Level Permissions

Run the following statements while connected as sysadmin or securityadmin:

USE master;
GO

-- Allows reading sys.dm_database_encryption_keys and other DMVs
GRANT VIEW SERVER STATE TO cbom_reader;
GO

-- Allows reading sys.certificates, sys.asymmetric_keys, sys.symmetric_keys
GRANT VIEW ANY DEFINITION TO cbom_reader;
GO

-- Allows enumerating all databases and their encryption state
GRANT VIEW ANY DATABASE TO cbom_reader;
GO

4.2 Verify Permission Assignment

Confirm all three permissions are recorded in the server permission catalog:

SELECT
    pr.name        AS principal_name,
    pe.permission_name,
    pe.state_desc
FROM sys.server_permissions AS pe
JOIN sys.server_principals   AS pr
    ON pe.grantee_principal_id = pr.principal_id
WHERE pr.name = 'cbom_reader';
GO

Expected output: three rows — VIEW SERVER STATE, VIEW ANY DEFINITION, and VIEW ANY DATABASE, each with state_desc = GRANT.

5. Verify Catalog View Access

Before configuring the sensor, confirm that the cbom_reader login can successfully query all required catalog views.

5.1 Test as the CBOM Login

Open a new query session authenticated as cbom_reader (use SSMS Connect As or sqlcmd -U cbom_reader -P ...).
Run the following verification queries:

-- List all server-level certificates
SELECT name, subject, thumbprint, algorithm_desc, expiry_date, pvt_key_encryption_type_desc
FROM sys.certificates;
GO

-- List all asymmetric keys
SELECT name, algorithm_desc, thumbprint, pvt_key_encryption_type_desc
FROM sys.asymmetric_keys;
GO

-- List all TDE-protected databases and their encryption state
SELECT
    DB_NAME(database_id)   AS database_name,
    encryption_state,
    encryptor_type,
    key_algorithm,
    key_length,
    percent_complete
FROM sys.dm_database_encryption_keys;
GO

-- List symmetric keys
SELECT name, algorithm_desc, key_length, create_date, modify_date
FROM sys.symmetric_keys;
GO

All queries should return results (or empty result sets) without permission errors. Any The server principal ... is not able to access the database error indicates a missing permission — revisit Section 4.

6. Configure CBOM Sensor

Once the login is verified, add the connection details to your CBOM sensor configuration. Locate your CBOM deployment's sensor configuration file or the sensor management UI and create a new entry for the Discover_DB sensor type.

The following YAML block shows all required and optional fields:

sensors:
  - name: mssql-tde-prod
    type: Discover_DB
    enabled: true
    schedule: "0 2 * * *"          # Run daily at 02:00 UTC

    connection:
      driver: mssql
      host: "sql-prod-01.corp.example.com"
      port: 1433
      database: master              # Always use master for server-scope catalog queries
      username: "cbom_reader"
      password: "${CBOM_MSSQL_PASSWORD}"   # Injected from vault / environment secret
      encrypt: true                 # Enforce TLS for the client connection
      trust_server_certificate: false
      connection_timeout_seconds: 30

    discovery:
      catalogs:
        - sys.certificates
        - sys.asymmetric_keys
        - sys.dm_database_encryption_keys
        - sys.symmetric_keys
      include_disabled_databases: false
      include_system_databases: false    # Exclude master, model, msdb, tempdb from DB-scope scans

    output:
      asset_types:
        - certificate
        - asymmetric_key
        - symmetric_key
        - tde_dek
      tags:
        environment: production
        owner: "[email protected]"
        data_classification: confidential

    notifications:
      on_error: true
      on_new_asset: true
      on_expiring_cert_days: 30

Store the credential referenced by ${CBOM_MSSQL_PASSWORD} in your secrets manager (HashiCorp Vault, AWS Secrets Manager, Azure Key Vault, etc.). Never hardcode the password in the configuration file.

7. Validation

After saving the sensor configuration, perform the following steps to confirm end-to-end operation.

Trigger a manual scan from the CBOM console or CLI:

cbom sensor run --name mssql-tde-prod

Check the sensor run log for a successful completion message:

[INFO] Sensor mssql-tde-prod started
[INFO] Connected to sql-prod-01.corp.example.com:1433
[INFO] Discovered 3 certificate(s)
[INFO] Discovered 1 asymmetric_key(s)
[INFO] Discovered 4 tde_dek(s)
[INFO] Discovered 2 symmetric_key(s)
[INFO] Sensor run completed successfully in 4.2s

Open the CBOM asset inventory and filter by source = mssql-tde-prod. Verify that discovered assets include the expected certificates and TDE database encryption keys.
Confirm asset metadata for at least one certificate — check that thumbprint, algorithm, expiry_date, and subject are populated correctly.
Confirm no data access — review the SQL Server audit log (if enabled) to verify that cbom_reader executed only SELECT statements against catalog views and no data tables.

8. Common Errors and Resolution

Error 1: Login failed for user 'cbom_reader'

Microsoft.Data.SqlClient.SqlException: Login failed for user 'cbom_reader'.

Cause: The login does not exist on the target instance, the password is incorrect, the account is disabled, or SQL Server Authentication is not enabled (the instance may be in Windows Authentication-only mode).

Resolution:

Verify Mixed Mode authentication: in SSMS, right-click the server > Properties > Security > confirm SQL Server and Windows Authentication mode is selected. Restart the SQL Server service if you change this setting.
Confirm the login exists and is not disabled: SELECT name, is_disabled FROM sys.server_principals WHERE name = 'cbom_reader';
Reset the password if needed: ALTER LOGIN cbom_reader WITH PASSWORD = 'NewP@ssword!';

Error 2: The user does not have permission to perform this action

SqlException: The user does not have permission to perform this action.
SqlException: Cannot find the object "sys.dm_database_encryption_keys" because it does not exist or you do not have permissions.

Cause: One or more of the three required server permissions (VIEW SERVER STATE, VIEW ANY DEFINITION, VIEW ANY DATABASE) was not granted, or was subsequently revoked.

Resolution:

Re-run the permission verification query from Section 4.2.
Re-grant any missing permission:

GRANT VIEW SERVER STATE   TO cbom_reader;
GRANT VIEW ANY DEFINITION TO cbom_reader;
GRANT VIEW ANY DATABASE   TO cbom_reader;
GO

If the instance uses contained databases, also verify that the login is mapped to a contained database user if scanning contained databases.

Error 3: SSL Provider error — certificate chain not trusted

Microsoft.Data.SqlClient.SqlException (0x80131904): A connection was successfully
established with the server, but then an error occurred during the login process.
(provider: SSL Provider, error: 0 - The certificate chain was issued by an authority
that is not trusted.)

Cause: The CBOM sensor is configured with trust_server_certificate: false but the SQL Server's TLS certificate is self-signed or issued by a CA not trusted by the sensor host's certificate store.

Resolution:

Preferred: Install the SQL Server's CA certificate into the sensor host's trusted root CA store.
Alternative: Replace the SQL Server's self-signed certificate with one issued by your internal PKI or a public CA.
Temporary workaround only — not for production: Set trust_server_certificate: true in the sensor configuration to skip chain validation. This removes TLS protection for the credential in transit and is not recommended for any environment that handles production data.

9. Security Recommendations

Use a dedicated, least-privilege login. The cbom_reader login must have only the three permissions listed in Section 4. Do not grant sysadmin, db_owner, or any data-access role. Audit the login's effective permissions periodically with fn_my_permissions.
Rotate the login password on a scheduled basis. Enforce password rotation (90 days or per your organizational policy) using CHECK_EXPIRATION = ON. Store the credential in a vault and update the CBOM sensor secret reference — never hardcode it in configuration files or CI/CD pipelines.
Enforce encrypted connections. Set encrypt: true and trust_server_certificate: false in the sensor configuration. On the SQL Server side, configure a valid TLS certificate and set Force Encryption = Yes in SQL Server Configuration Manager to prevent plaintext connections from any client.
Enable SQL Server Auditing for the CBOM login. Create a server audit specification that logs SELECT statements by cbom_reader against system catalog objects. This provides an immutable record confirming the sensor performed only read operations and no key material was accessed.
Restrict network access to the SQL Server port. Use host-based firewall rules or network security groups to permit inbound TCP/1433 only from the CBOM sensor host IP address. This limits the attack surface if the cbom_reader credential is ever compromised.

10. Conclusion

This guide has walked through the complete setup of the CBOM Discover_DB sensor for Microsoft SQL Server Transparent Data Encryption, covering login creation, permission grants, catalog view verification, sensor YAML configuration, and post-deployment validation. The resulting configuration gives the CBOM platform continuous, read-only visibility into TDE certificates, asymmetric keys, symmetric keys, and database encryption keys across the target SQL Server instance without exposing or decrypting any protected data. With scheduled daily scans and expiry notifications enabled, security teams will have timely awareness of cryptographic asset inventory and certificate lifecycle events.

CBOM Secure

Microsoft SQL Server Setup Guide for CBOM Database TDE Sensor

1. Purpose

2. Prerequisites

3. Create a SQL Server Login for CBOM

3.1 Connect to the SQL Server Instance

3.2 Create the CBOM Reader Login

4. Grant Required Permissions

4.1 Grant Server-Level Permissions

4.2 Verify Permission Assignment

5. Verify Catalog View Access

5.1 Test as the CBOM Login

6. Configure CBOM Sensor

7. Validation

8. Common Errors and Resolution

Error 1: Login failed for user 'cbom_reader'

Error 2: The user does not have permission to perform this action

Error 3: SSL Provider error — certificate chain not trusted

9. Security Recommendations

10. Conclusion

On this Page: