Sqlencryption Review

SQL Server Encryption Review Skill

Purpose

Audit the complete encryption posture of a SQL Server instance or database. Applies 112 checks (A1–A112) across 20 categories:

• A1–A8 — Transparent Data Encryption (TDE): scan state, algorithm strength, certificate lifecycle, cross-database cert sharing risks
• A9–A16 — Always Encrypted: encryption type selection, CEK algorithm, secure enclave availability, CMK store quality, sensitive-column coverage, key rotation
• A17–A21 — Cell-Level Encryption (CLE): deprecated algorithms, open-key scope leaks, password-only key protection, rotation age, strategy conflicts
• A22–A25 — Backup Encryption: unencrypted backups, certificate backup status, algorithm strength, certificate expiry
• A26–A30 — Transport / Connection Encryption: ForceEncryption enforcement, unencrypted active sessions, self-signed TLS certificates, TrustServerCertificate bypass, TLS cert expiry
• A31–A38 — Certificate Management: private key protection, Service Broker and AG endpoint certificates, certificate-based login permissions, signature hash algorithm, CA trust chain, backup strategy, duplicate subjects
• A39–A43 — Asymmetric and Symmetric Key Management: RSA key length, over-permissioned keys, rotation age, orphaned keys, non-unique KEY_SOURCE
• A44–A48 — Key Hierarchy (DMK / SMK): backup status, SMK protection layer, password-only risks, linked-server encryption
• A49–A52 — EKM / Azure Key Vault: provider health, BYOK rotation policy, service-managed vs. customer-managed TDE, provider version
• A53–A56 — Compliance and Coverage: sensitivity-classified columns without encryption, sensitive-pattern columns unprotected, non-FIPS algorithms, missing audit configuration
• A57–A62 — TLS and Network Encryption Hardening: legacy TLS version audit, weak cipher detection, TLS 1.3 enforcement, IPsec as compensating control, Kerberos armoring, Named Pipes risk
• A63–A67 — Always Encrypted Advanced: enclave attestation completeness, driver version compatibility, CEK caching configuration, enclave utilization, attestation protocol enforcement
• A68–A72 — Operational Key Lifecycle: DMK/SMK password strength, certificate auto-enrollment, key archival/escrow procedures, TDE scan I/O baselining, log backup encryption
• A73–A76 — SQL Server Ledger (SQL 2022+): database ledger enablement, digest storage configuration, hash algorithm strength, ledger verification scheduling
• A77–A80 — Azure-Specific Encryption: TDE protector region placement, double encryption (infrastructure + TDE), enclave attestation provider isolation, audit log encryption at rest
• A81–A86 — DMK Password Auto-Open: spcontroldbmasterkey_password coverage, SSISDB DMK registration, SMK restore invalidation, non-SMK DMK auto-open paths, cross-server restore, AG secondaries
• A87–A91 — Dynamic Data Masking and Permission Patterns: masking vs. encryption, UNMASK scope, certificate CONTROL permission, RLS on AE columns, CLE cipher text leakage
• A92–A98 — Compliance Explicit Checks: PCI-DSS v4 PAN column encryption, annual key rotation evidence, GDPR Art. 17 ledger conflict, FIPS mode enforcement, HSM provider for FIPS, key custodian documentation, HIPAA PHI audit trail
• A99–A104 — Operational Validation: SQL Agent job step passwords, plan cache password exposure, AKV soft-delete/purge protection, annual backup restore test, credential rotation, AG listener TLS
• A105–A112 — Advanced Cryptographic Patterns: cipher suite ordering, NTLM on remote connections, Service Broker cross-DB cert, ENCRYPTBYPASSPHRASE weakness, HASHBYTES deprecated algorithms, Database Mail SMTP credentials, CLE cert expiry monitoring, Azure MI managed identity AKV permissions

For background on encryption concepts, algorithm comparisons, TLS versions, the SQL Server key hierarchy, and PCI-DSS / HIPAA / GDPR requirements, read references/concepts.md.

---

Input

Accept any combination of the following. Apply all checks that are relevant to the data provided. When the user describes symptoms in natural language, apply checks based on the described state.

• Output from sys.dm_database_encryption_keys joined with sys.databases and master.sys.certificates — TDE checks
• Output from sys.columns joined with sys.column_encryption_keys and sys.column_master_keys — Always Encrypted checks
• Output from sys.symmetric_keys, sys.asymmetric_keys, sys.certificates, sys.key_encryptions — key management checks
• Output from msdb.dbo.backupset — backup encryption checks
• Output from sys.dm_exec_connections — transport encryption checks
• Output from sys.sensitivity_classifications — compliance coverage checks
• Output from sys.cryptographic_providers or sys.dm_cryptographic_provider_properties — EKM checks
• Output from sys.endpoints — Service Broker / AG endpoint checks
• Output from sys.server_audits and sys.database_audit_specifications — audit checks
• Output from sys.ledger_* DMVs — SQL Server 2022 Ledger checks (A73–A76)
• Output from sys.dm_exec_connections with connection attribute details — driver version checks (A64)
• Natural language description of encryption configuration or symptoms

Recommended Capture Queries

-- 1. TDE status across all databases
SELECT
    d.name                          AS database_name,
    d.is_encrypted,
    dek.encryption_state,
    dek.encryption_state_desc,
    dek.percent_complete,
    dek.encryptor_type,
    dek.key_algorithm,
    dek.key_length,
    c.name                          AS certificate_name,
    c.expiry_date                   AS cert_expiry,
    c.pvt_key_encryption_type_desc
FROM sys.databases d
LEFT JOIN sys.dm_database_encryption_keys dek ON d.database_id = dek.database_id
LEFT JOIN master.sys.certificates c ON dek.encryptor_thumbprint = c.thumbprint
ORDER BY d.name;

-- 2. Always Encrypted column inventory
SELECT
    SCHEMA_NAME(t.schema_id)        AS schema_name,
    t.name                          AS table_name,
    c.name                          AS column_name,
    c.encryption_type,
    c.encryption_type_desc,
    c.encryption_algorithm_name,
    cek.name                        AS cek_name,
    cmk.name                        AS cmk_name,
    cmk.key_store_provider_name,
    cmk.key_path
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
JOIN sys.column_encryption_keys cek ON c.column_encryption_key_id = cek.column_encryption_key_id
JOIN sys.column_master_keys cmk ON cek.column_master_key_id = cmk.column_master_key_id
WHERE c.column_encryption_key_id IS NOT NULL;

-- 3. CEK version history (rotation check)
SELECT
    cek.name                        AS cek_name,
    cek.create_date,
    cekv.column_master_key_id,
    cmk.name                        AS cmk_name,
    cekv.create_date                AS version_created
FROM sys.column_encryption_keys cek
JOIN sys.column_encryption_key_values cekv ON cek.column_encryption_key_id = cekv.column_encryption_key_id
JOIN sys.column_master_keys cmk ON cekv.column_master_key_id = cmk.column_master_key_id;

-- 4. Symmetric and asymmetric keys
SELECT
    sk.name,
    sk.symmetric_key_id                AS key_id,
    'SYMMETRIC'                        AS key_type,
    sk.algorithm_desc,
    CAST(sk.key_length AS VARCHAR(10)) AS key_length,
    sk.create_date,
    sk.modify_date,
    ke.crypt_type_desc                 AS key_protection_type
FROM sys.symmetric_keys sk
OUTER APPLY (
    SELECT TOP 1 crypt_type_desc
    FROM sys.key_encryptions
    WHERE key_id = sk.symmetric_key_id
    ORDER BY crypt_type
) ke
WHERE sk.name NOT LIKE '##%'
UNION ALL
SELECT
    name,
    asymmetric_key_id,
    'ASYMMETRIC',
    algorithm_desc,
    CAST(key_length AS VARCHAR(10)),
    create_date,
    modify_date,
    pvt_key_encryption_type_desc
FROM sys.asymmetric_keys
WHERE name NOT LIKE '##%';

-- 5. Certificates (all purposes)
SELECT
    name,
    certificate_id,
    pvt_key_encryption_type_desc,
    issuer_name,
    subject,
    start_date,
    expiry_date,
    DATEDIFF(DAY, GETDATE(), expiry_date) AS days_until_expiry,
    NULL                                   AS sig_algorithm  -- CERTPROPERTY does not expose 'Algorithm'; verify via certutil or Get-ChildItem Cert:\ | Select SignatureAlgorithm
FROM sys.certificates
WHERE name NOT LIKE '##%'
ORDER BY expiry_date;

-- 6. Backup encryption history (last 30 days)
SELECT TOP 30
    database_name,
    backup_start_date,
    backup_finish_date,
    type                            AS backup_type,
    key_algorithm,
    encryptor_type,
    encryptor_thumbprint
FROM msdb.dbo.backupset
WHERE backup_start_date > DATEADD(DAY, -30, GETDATE())
ORDER BY backup_start_date DESC;

-- 7. Connection encryption status
SELECT
    encrypt_option,
    auth_scheme,
    COUNT(*)                        AS connection_count,
    SUM(CASE WHEN client_net_address NOT IN ('', '127.0.0.1', '::1')
             THEN 1 ELSE 0 END)     AS remote_connections
FROM sys.dm_exec_connections
GROUP BY encrypt_option, auth_scheme;

-- 8. Key hierarchy: DMK protection status
SELECT
    d.name                          AS database_name,
    d.is_master_key_encrypted_by_server,
    sk.name                         AS dmk_name,
    sk.create_date,
    sk.modify_date
FROM sys.databases d
LEFT JOIN sys.symmetric_keys sk ON sk.name = N'##MS_DatabaseMasterKey##'
WHERE d.database_id = DB_ID();

-- 9. EKM providers
SELECT
    provider_id,
    name,
    dll_path,
    is_enabled,
    provider_version,
    sqlcrypt_version
FROM sys.cryptographic_providers;

-- 10. Sensitivity classifications
SELECT
    SCHEMA_NAME(t.schema_id)        AS schema_name,
    t.name                          AS table_name,
    c.name                          AS column_name,
    sc.information_type,
    sc.label,
    sc.rank_desc,
    c.column_encryption_key_id      AS ae_key_id
FROM sys.sensitivity_classifications sc
JOIN sys.objects t ON sc.major_id = t.object_id
JOIN sys.columns c ON sc.major_id = c.object_id AND sc.minor_id = c.column_id;

-- 11. Endpoints using certificate authentication
SELECT
    e.name                          AS endpoint_name,
    e.type_desc,
    e.connection_auth_desc,
    c.name                          AS certificate_name,
    c.expiry_date,
    DATEDIFF(DAY, GETDATE(), c.expiry_date) AS days_until_expiry
FROM sys.endpoints e
LEFT JOIN sys.certificates c ON e.certificate_id = c.certificate_id
WHERE e.connection_auth_desc LIKE '%CERTIFICATE%';

---

How to Run

Walk A1–A80 in category order. Report every triggered finding — do not stop at the first match per category. For checks where the relevant DMV data is absent from the input, mark them as NOT ASSESSED rather than skipping silently. For checks where the data is partial (e.g., a description rather than DMV output), state your assumption explicitly before applying the check.

When multiple databases or instances are present in the input, run all applicable checks per database/instance and aggregate findings. Prioritize production databases for Critical and Warning findings.

For T-SQL source-level checks (A18, A19, A43), scan provided SQL modules in sys.sql_modules or pasted code; note any checks that could not be verified due to missing source code.

---

Thresholds Reference

| Metric | Info | Warning | Critical | |--------|------|---------|----------| | Certificate / key days until expiry | — | 365 days since last rotation | > 730 days | | CMK rotation age | — | > 730 days | — | | RSA asymmetric key length | — | RSA1024 | RSA512 | | Unencrypted remote connections | 0 | > 0 | — | | TDE DEK algorithm | AES128 / AES192 | — | TRIPLEDES3KEY (the only non-AES DEK algorithm; RC4 is not valid for a DEK) | | Symmetric key algorithm (CLE) | AES128 / AES192 | DES / DESX / TRIPLEDES | RC4 / RC2 | | Backup encryption algorithm | AES128 | TRIPLEDES3KEY | None (unencrypted) | | Non-FIPS algorithm anywhere | — | SHA1 / DES / 3DES | RC4 / MD5 |

---

Checks

Transparent Data Encryption — A1–A8

A1 — TDE not enabled on user database

• Trigger: sys.databases WHERE is_encrypted = 0 AND database_id > 4 (non-system database)
• Severity: Warning by default; Critical if database name contains any of: prod, finance, hr, payroll, customer, patient, medical, pii, gdpr, pci, hipaa
• Fix: In master DB, create a certificate and Database Encryption Key, then ALTER DATABASE [db] SET ENCRYPTION ON. Note: tempdb will encrypt automatically.

A2 — TDE encryption scan in progress

• Trigger: sys.dm_database_encryption_keys WHERE encryption_state IN (2, 4, 5, 6) AND `percent_complete 1
• Severity: Warning — a single certificate compromise or rotation event affects all databases simultaneously; a failed rotation leaves all covered databases in a degraded state
• Fix: Issue a dedicated TDE certificate per database or per environment tier (prod, staging); use a consistent naming convention: TDE_[dbname]_[year]

A7 — TDE enabled on master, model, or msdb

• Trigger: sys.dm_database_encryption_keys WHERE database_id IN (1, 2, 3) (master, model, msdb — tempdb at database_id 2 is acceptable and expected)
• Severity: Info — explicitly encrypting master or model can complicate bare-metal restores, Dedicated Admin Connection (DAC) access, and AG seed operations; tempdb encryption is expected when any user DB is TDE-enabled
• Fix: Confirm intent; if inadvertent, ALTER DATABASE master SET ENCRYPTION OFF; verify that AG seeding, RESTORE DATABASE, and DAC connections still function after change

A8 — tempdb encrypted but no user database is encrypted

• Trigger: sys.databases WHERE name = 'tempdb' AND is_encrypted = 1 AND COUNT of user databases (database_id > 4) with is_encrypted = 1 = 0
• Severity: Warning — tempdb encryption is a residual artifact of a TDE-enabled database that was since dropped or decrypted; it adds I/O overhead with no data-protection benefit
• Fix: Disable TDE on the last remaining user database (if that is the intended state); tempdb will automatically drop its encryption; verify sys.databases shows is_encrypted = 0 for tempdb after restart

---

Always Encrypted — A9–A16

A9 — Deterministic encryption on non-searchable columns

• Trigger: sys.columns WHERE encryption_type = 1 (DETERMINISTIC) AND column name does not suggest a join key or lookup field (no _id, _key, _code, _number suffix pattern)
• Severity: Info — deterministic encryption preserves equality-comparison semantics but leaks frequency distribution patterns; randomized encryption (type 2) is preferable for columns that are never queried with WHERE or JOIN
• Fix: Re-encrypt privacy-only columns (e.g., middle name, notes) with RANDOMIZED type using SSMS Always Encrypted wizard or PowerShell Set-SqlColumnEncryption

A10 — Randomized encryption where equality queries are needed, no secure enclave configured

• Trigger: sys.columns WHERE encryption_type = 2 (RANDOMIZED) AND sys.configurations WHERE name = 'column encryption enclave type' returns 0 (no enclave)
• Severity: Warning — applications attempting WHERE or JOIN on randomized-encrypted columns will receive "Operand type clash" errors at runtime; this is a functional defect, not merely a security concern
• Fix: Either (a) switch the column to DETERMINISTIC if only equality comparisons are needed, or (b) enable a secure enclave on SQL 2019+ (sp_configure 'column encryption enclave type', 1) and configure VBS enclave attestation on the client

A11 — Column encryption algorithm is not AEADAES256CBCHMACSHA256

• Trigger: sys.columns WHERE column_encryption_key_id IS NOT NULL AND encryption_algorithm_name != 'AEAD_AES_256_CBC_HMAC_SHA_256'
• Severity: Warning — the standard AE algorithm provides authenticated encryption (prevents ciphertext manipulation); non-standard algorithms may lack authenticat

…

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: vanterx
• Source: vanterx/mssql-performance-skills
• License: MIT

Install and usage instructions live in the source repository linked above.