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: sp_control_dbmasterkey_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 ('<local machine>', '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	—	< 90 days	Expired (≤ 0 days)
Key rotation age (symmetric / CEK)	—	> 365 days since last rotation	> 730 days
CMK rotation age	—	> 730 days	—
RSA asymmetric key length	—	RSA_1024	RSA_512
Unencrypted remote connections	0	> 0	—
TDE DEK algorithm	AES_128 / AES_192	—	TRIPLE_DES_3KEY (the only non-AES DEK algorithm; RC4 is not valid for a DEK)
Symmetric key algorithm (CLE)	AES_128 / AES_192	DES / DESX / TRIPLE_DES	RC4 / RC2
Backup encryption algorithm	AES_128	TRIPLE_DES_3KEY	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 < 100
• Severity: Info — the scan consumes additional I/O and CPU proportional to database size; on SQL 2019+ the scan can be suspended. State values: 2 = encryption in progress, 4 = key change in progress (DEK algorithm or key being regenerated via ALTER DATABASE ENCRYPTION KEY REGENERATE), 5 = decryption in progress, 6 = protection change in progress (the certificate or asymmetric key encrypting the DEK is being replaced)
• Fix: Monitor percent_complete; avoid heavy index rebuild or backup jobs during scan; on SQL 2019+, use ALTER DATABASE [db] SET ENCRYPTION SUSPEND | RESUME if I/O pressure is high

A3 — TDE certificate not backed up

• Trigger: TDE-protector certificate present in master.sys.certificates but no evidence of a BACKUP CERTIFICATE operation (no corresponding file path documented, no SQL Agent job containing BACKUP CERTIFICATE)
• Severity: Critical — the TDE certificate is the only key capable of decrypting the Database Encryption Key; if it is lost with the instance, the database is permanently unrestorable even with an intact backup
• Fix: BACKUP CERTIFICATE [tde_cert] TO FILE = 'path\tde_cert.cer' WITH PRIVATE KEY (FILE = 'path\tde_cert.pvk', ENCRYPTION BY PASSWORD = 'StrongPassword') — store the .cer, .pvk, and password in separate secure, off-server locations

A4 — TDE certificate expired or expiring within 90 days

• Trigger: master.sys.certificates WHERE cert thumbprint matches a sys.dm_database_encryption_keys.encryptor_thumbprint AND DATEDIFF(DAY, GETDATE(), expiry_date) < 90
• Severity: Critical if expiry_date < GETDATE(); Warning if within 90 days
• Fix: Create new certificate: CREATE CERTIFICATE [tde_cert_new] WITH EXPIRY_DATE = '20270101'; re-key DEK: ALTER DATABASE [db] ENCRYPTION KEY ENCRYPTION BY SERVER CERTIFICATE [tde_cert_new]; keep old cert until all backups protected by it have been superseded

A5 — TDE DEK using non-AES_256 algorithm

• Trigger: sys.dm_database_encryption_keys WHERE key_algorithm != 'AES_256' — specifically TRIPLE_DES_3KEY or AES_128/AES_192
• Severity: Critical for TRIPLE_DES_3KEY (NIST SP 800-131A deprecated 3DES after 2023); Warning for AES_128 / AES_192 (acceptable but AES_256 is preferred for PCI-DSS and FIPS compliance)
• Fix: ALTER DATABASE [db] ENCRYPTION KEY REGENERATE WITH ALGORITHM = AES_256 — triggers a re-encryption scan; plan for I/O impact

A6 — Multiple databases sharing the same TDE certificate

• Trigger: sys.dm_database_encryption_keys GROUP BY encryptor_thumbprint HAVING COUNT(*) > 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 AEAD_AES_256_CBC_HMAC_SHA_256

• 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 authentication or use weaker primitives
• Fix: Re-encrypt the column specifying ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256' in the column definition; coordinate with application teams for driver updates if needed

A12 — Secure enclave not configured for range or pattern queries (SQL 2019+)

• Trigger: SQL Server 2019 or later AND sys.configurations WHERE name = 'column encryption enclave type' = 0 AND randomized-encrypted columns exist in the database
• Severity: Info — the server supports secure enclaves but they are not enabled; range comparisons (BETWEEN, <, >) and LIKE on AE columns are currently impossible
• Fix: EXEC sp_configure 'column encryption enclave type', 1; RECONFIGURE — requires Windows Server 2019+ with Virtualization Based Security or Intel SGX hardware; configure attestation service URL in client connection string

A13 — Column Master Key stored in Windows Certificate Store (not HSM or AKV)

• Trigger: sys.column_master_keys WHERE key_store_provider_name = 'MSSQL_CERTIFICATE_STORE'
• Severity: Warning — Windows certificate store certificates are machine-exportable; without additional protections (CNG key storage providers, TPM-backed certificates) the private key can be extracted from the machine; no hardware tamper resistance
• Fix: Migrate CMK to Azure Key Vault (key_store_provider_name = 'AZURE_KEY_VAULT') or a FIPS 140-2 Level 3 HSM; generate new CMK in the new store, re-encrypt CEKs under the new CMK, then drop the old CMK

A14 — Sensitive-pattern column names without Always Encrypted protection

• Trigger: sys.columns WHERE name matches any of %ssn%, %social_sec%, %credit_card%, %card_num%, %cvv%, %cvc%, %password%, %passwd%, %\bpin\b%, %dob%, %date_of_birth%, %salary%, %tax_id%, %passport%, %national_id%, %medical_record%, %diagnosis%, %account_number% — AND column_encryption_key_id IS NULL
• Severity: Warning — strong indicator of PII/PCI/PHI columns without encryption; likely non-compliant with PCI-DSS Requirement 3.4, HIPAA 45 CFR §164.312, or GDPR Article 32
• Fix: Confirm column contents with data owner; apply Always Encrypted for columns requiring client-side key control, or CLE for simpler needs; add sys.sensitivity_classifications label after encrypting

A15 — No CEK rotation ever performed

• Trigger: sys.column_encryption_key_values WHERE for a given column_encryption_key_id there is only one record (single CMK version, never rotated) AND sys.column_encryption_keys.create_date < DATEADD(YEAR, -1, GETDATE())
• Severity: Info — annual key rotation limits the exposure window if a CEK or its protecting CMK is ever compromised; a single-version CEK that is more than a year old has not been rotated
• Fix: ALTER COLUMN ENCRYPTION KEY [cek] ADD VALUE (COLUMN_MASTER_KEY = [new_cmk], ALGORITHM = 'RSA_OAEP', ENCRYPTED_VALUE = 0x...) — use SSMS wizard to automate CEK re-encryption; then DROP VALUE for the old CMK version

A16 — Column Master Key not rotated in over 2 years

• Trigger: sys.column_master_keys WHERE create_date < DATEADD(YEAR, -2, GETDATE()) AND no newer CMK with the same logical name pattern exists
• Severity: Warning — CMKs protect all CEKs for a database; a 2-year-old CMK is overdue for rotation by most security policy standards
• Fix: Use SSMS Always Encrypted wizard → Rotate Column Master Key, or PowerShell Invoke-SqlColumnMasterKeyRotation -InputObject $db -SourceColumnMasterKeyName [old_cmk] -TargetColumnMasterKeyName [new_cmk]; distribute new CMK to all application servers before completing rotation

---

Cell-Level Encryption — A17–A21

A17 — Symmetric key using deprecated or broken algorithm

• Trigger: sys.symmetric_keys WHERE algorithm_desc IN ('DES', 'Triple_DES', 'RC2', 'RC4', 'DESX', 'TRIPLE_DES_3KEY') AND name NOT LIKE '##%'
• Severity: Critical for RC4 and RC2 (cryptographically broken; trivially decryptable); Warning for DES (56-bit key, brute-forceable) and DESX (misnamed — actually Triple DES with a 192-bit key, deprecated); Warning for Triple_DES / TRIPLE_DES_3KEY (deprecated post-2023 per NIST SP 800-131A)
• Fix: CREATE SYMMETRIC KEY [key_new] WITH ALGORITHM = AES_256, KEY_SOURCE = '...', IDENTITY_VALUE = '...', ENCRYPTION BY CERTIFICATE [cert]; re-encrypt all data with ENCRYPTBYKEY(KEY_GUID('[key_new]'), plaintext); close and DROP SYMMETRIC KEY [key_old]

A18 — OPEN SYMMETRIC KEY without matching CLOSE in the same scope

• Trigger: T-SQL source (stored procedures, functions) containing OPEN SYMMETRIC KEY with no CLOSE SYMMETRIC KEY or CLOSE ALL SYMMETRIC KEYS before the end of the batch; OR sys.openkeys showing keys open across long-running or idle sessions
• Severity: Warning — an open symmetric key persists for the lifetime of the session; any code running in that session can call DECRYPTBYKEY() without re-opening; connection pool reuse means keys may be open in unexpected contexts
• Fix: Add CLOSE SYMMETRIC KEY [key_name] after every use; add a CATCH block that also closes the key on error; add CLOSE ALL SYMMETRIC KEYS as a session cleanup safeguard in session-level error handlers

A19 — Symmetric key protected by password only

• Trigger: sys.key_encryptions WHERE crypt_type_desc = 'ENCRYPTION BY PASSWORD' AND the same key_id does NOT also appear in a row with crypt_type_desc IN ('ENCRYPTION BY CERTIFICATE', 'ENCRYPTION BY ASYMMETRIC KEY')
• Severity: Warning — the password must be embedded in T-SQL scripts or agent jobs to open the key; passwords can appear in plan cache text, SQL Agent job step history, and memory dumps; certificate-based protection integrates cleanly with the key hierarchy and never requires a password at runtime
• Fix: ALTER SYMMETRIC KEY [key] ADD ENCRYPTION BY CERTIFICATE [cert] — then test the OPEN statement without the password parameter — then ALTER SYMMETRIC KEY [key] DROP ENCRYPTION BY PASSWORD = 'old_password'

A20 — Symmetric key never rotated (age over 365 days)

• Trigger: sys.symmetric_keys WHERE modify_date = create_date AND create_date < DATEADD(DAY, -365, GETDATE()) AND name NOT LIKE '##%'
• Severity: Info for 1–2 years; Warning for over 2 years — a static CLE key that has never been rotated maximizes the exposure window for any key material that might have leaked through logs, backups, or memory
• Fix: Create replacement key; re-encrypt data in batches; schedule rotation as a SQL Agent job; document the rotation interval in the key's naming convention (e.g., CLE_CustomerSSN_2025)

A21 — Both CLE and Always Encrypted applied to the same table

• Trigger: Table contains columns with column_encryption_key_id IS NOT NULL (Always Encrypted) AND sys.sql_modules.definition contains ENCRYPTBYKEY or DECRYPTBYKEY calls referencing the same table
• Severity: Warning — double-encryption adds latency on every read and write; the application must handle two decryption code paths; CLE decryption happens server-side (defeating AE's client-side key control guarantee)
• Fix: Standardize on Always Encrypted for column-level encryption across the table; remove CLE functions from stored procedures; test the application layer after removing CLE

---

Backup Encryption — A22–A25

A22 — Recent backups not encrypted

• Trigger: msdb.dbo.backupset WHERE backup_start_date > DATEADD(DAY, -30, GETDATE()) AND key_algorithm IS NULL (no encryption) — for database_name matching production databases
• Severity: Critical — an unencrypted full backup is a complete copy of the database accessible to anyone with access to the backup media or storage location; encryption at rest on storage is not a substitute for backup-level encryption
• Fix: Add WITH ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = [backup_cert]) to all BACKUP DATABASE and BACKUP LOG statements; create the backup certificate in master DB first; update Ola Hallengren or native maintenance plan scripts

A23 — Backup encryption certificate not separately backed up

• Trigger: Encrypted backups exist (msdb.dbo.backupset.key_algorithm IS NOT NULL) AND no BACKUP CERTIFICATE evidence for the referenced certificate
• Severity: Critical — if the SQL Server instance is lost (disk failure, OS corruption, ransomware) and the backup encryption certificate is not saved separately, all encrypted backup files become permanently irrecoverable regardless of how many copies exist
• Fix: BACKUP CERTIFICATE [backup_cert] TO FILE = 'D:\CertBackups\backup_cert.cer' WITH PRIVATE KEY (FILE = 'D:\CertBackups\backup_cert.pvk', ENCRYPTION BY PASSWORD = 'VaultStoredPassword') — keep the .cer, .pvk, and password in separate, geographically distributed secure storage

A24 — Backup encryption using TRIPLE_DES_3KEY or AES_128

• Trigger: msdb.dbo.backupset WHERE key_algorithm IN ('TRIPLE_DES_3KEY', 'AES_128') AND backup_start_date > DATEADD(DAY, -30, GETDATE())
• Severity: Warning — TRIPLE_DES_3KEY is deprecated (NIST 800-131A); AES_128 is acceptable but AES_256 is required for PCI-DSS v4 and recommended for HIPAA/GDPR workloads
• Fix: Update backup scripts to ALGORITHM = AES_256; existing backups retain their original algorithm (still restorable); change affects only new backups going forward

A25 — Backup encryption certificate expiring within 90 days

• Trigger: Certificate referenced by msdb.dbo.backupset.encryptor_thumbprint with DATEDIFF(DAY, GETDATE(), expiry_date) < 90
• Severity: Critical if already expired; Warning if < 90 days — note: an expired TDE certificate does NOT prevent restoring backups encrypted by that certificate; however the same certificate is often used for both TDE and backup encryption, so expired certs must be tracked
• Fix: Create new backup certificate; update backup scripts to use new cert; maintain old cert for restoring pre-rotation backups; document the cert-to-backup-date mapping

---

Transport and Connection Encryption — A26–A30

A26 — ForceEncryption not enabled at server level

• Trigger: sys.dm_server_registry WHERE registry_key LIKE N'%SuperSocketNetLib%' AND value_name = N'ForceEncryption' AND value_data = 0; or no TLS certificate configured in SQL Server Configuration Manager
• Severity: Warning — without ForceEncryption, any client that connects with Encrypt=False (which was the default in drivers before ODBC 18 / JDBC 12 / .NET 7) establishes a plaintext session; credentials and query results travel in clear text over the network
• Fix: SQL Server Configuration Manager → SQL Server Network Configuration → Protocols for [Instance] → Properties → Certificate tab (bind a CA-signed cert) → Flags tab → Force Encryption = Yes; restart the SQL Server service

A27 — Active remote connections using no TLS encryption

• Trigger: sys.dm_exec_connections WHERE encrypt_option = 'FALSE' AND client_net_address NOT IN ('<local machine>', '127.0.0.1', '::1', '<named pipe>')
• Severity: Warning — live remote sessions expose authentication credentials and all result-set data to network packet capture; this is an active security defect, not a configuration recommendation
• Fix: Enable ForceEncryption (A26); audit application connection strings for Encrypt=True; for interim mitigation, use IPsec between application servers and the SQL Server host

A28 — SQL Server TLS certificate is self-signed

• Trigger: SQL Server ERRORLOG at last startup contains "A self-generated certificate was successfully loaded for encryption"; or the certificate configured in SQL Server Configuration Manager has issuer_name = subject
• Severity: Info for development/test; Warning for production — self-signed certs cannot be chain-validated by clients; connecting clients must set TrustServerCertificate=True, which disables all certificate validation and opens a man-in-the-middle vector
• Fix: Obtain a certificate from an internal CA (ADCS) or public CA (DigiCert, Let's Encrypt enterprise); install in Local Machine → Personal store; bind in SQL Server Configuration Manager → Certificates; restart SQL Server

A29 — TrustServerCertificate=TRUE detected in connection attributes

• Trigger: Extended Events session on sql_batch_starting or rpc_starting captures session attribute trust_server_certificate = 1; or connection string audit reveals TrustServerCertificate=True in application configuration files
• Severity: Warning — TrustServerCertificate=True instructs the driver to skip the entire certificate chain validation; a network attacker can intercept the TLS handshake and present any certificate without detection
• Fix: Deploy a valid CA-signed TLS certificate on the SQL Server (A28 fix); then remove TrustServerCertificate=True from all production connection strings; test each application after the change

A30 — SQL Server TLS authentication certificate expiring within 90 days

• Trigger: The certificate bound to SQL Server for TLS (identified from ERRORLOG "The certificate [thumbprint] will expire" message or SQL Server Configuration Manager) expires within 90 days
• Severity: Critical if expired — SQL Server falls back to a self-generated certificate, which will cause connection failures for clients that do not have TrustServerCertificate=True; Warning if within 90 days
• Fix: Renew the certificate from the issuing CA; install renewed cert in Windows cert store; rebind in SQL Server Configuration Manager → Certificates tab; restart SQL Server; verify ERRORLOG confirms the new cert is loaded

---

Certificate Management — A31–A38

A31 — Certificate private key not protected by DMK

• Trigger: sys.certificates WHERE pvt_key_encryption_type_desc = 'ENCRYPTED_BY_PASSWORD' (password-only, DMK not used) OR pvt_key_encryption_type_desc = 'NO_PRIVATE_KEY' (key was stripped or never imported)
• Severity: Warning for password-only (password must appear in T-SQL code to use the cert at runtime); Critical for NO_PRIVATE_KEY (cert cannot sign or decrypt — effectively a public-key-only stub, useless for TDE/CLE/Service Broker)
• Fix: For password-only: ALTER CERTIFICATE [cert] WITH PRIVATE KEY (DECRYPTION BY PASSWORD = 'old_pwd', ENCRYPTION BY DATABASE MASTER KEY) — verify the DMK exists and is open; for missing private key, restore cert from BACKUP CERTIFICATE output

A32 — Service Broker endpoint certificate not rotated in over 2 years

• Trigger: sys.endpoints WHERE type_desc = 'SERVICE_BROKER' AND connection_auth_desc = 'CERTIFICATE' joined to sys.certificates WHERE create_date < DATEADD(YEAR, -2, GETDATE())
• Severity: Info — Service Broker authentication certificates are long-lived but should be rotated to limit exposure; rotation requires coordination on both sides of each service binding
• Fix: Create new certificate; export and import it to the remote database; update sys.remote_service_bindings at the remote end; update the local endpoint: ALTER ENDPOINT [sb_endpoint] FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE [new_cert]); test message flow before dropping old cert

A33 — Always On AG endpoint certificate expiring within 90 days

• Trigger: sys.endpoints WHERE type_desc = 'DATABASE_MIRRORING' (used for AG) AND connection_auth_desc LIKE '%CERTIFICATE%' joined to sys.certificates WHERE DATEDIFF(DAY, GETDATE(), expiry_date) < 90
• Severity: Critical — when the AG endpoint certificate expires, replicas disconnect from the primary; data movement halts; if the primary then fails, failover to a synchronized secondary may not be possible
• Fix: Create new cert on primary; export to all secondary replicas via CREATE CERTIFICATE … FROM FILE; update endpoint on each replica: ALTER ENDPOINT [hadr_endpoint] FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE [new_cert]); verify replica states with SELECT * FROM sys.dm_hadr_availability_replica_states

A34 — Certificate-based login mapped to sysadmin or elevated fixed role

• Trigger: sys.server_principals WHERE type = 'C' (certificate login) JOIN sys.server_role_members WHERE role_principal_id IN (SELECT principal_id FROM sys.server_principals WHERE name IN ('sysadmin', 'securityadmin', 'processadmin', 'dbcreator'))
• Severity: Critical — a certificate-mapped login with sysadmin means that any process that can sign a T-SQL batch with the certificate's private key has full server control; certificate-based logins are intended for code-signing, not elevated interactive access
• Fix: Remove the certificate login from elevated fixed server roles; grant only the specific permissions needed (e.g., GRANT EXECUTE ON [schema].[proc] TO [cert_login]); consider using EXECUTE AS within the signed procedure instead of elevating the cert login itself

A35 — Certificate signed with MD5 or SHA1 signature hash

• Trigger: Certificates in sys.certificates WHERE name NOT LIKE '##%' where the signature algorithm cannot be confirmed as SHA256 or stronger; verify via certutil -dump <certfile> (shows Signature Algorithm) or PowerShell Get-ChildItem Cert:\ | Select-Object Subject, SignatureAlgorithm — CERTPROPERTY() does not expose the signature algorithm and cannot be used for this check
• Severity: Critical for MD5 (collision attacks demonstrated in practice since 2004; do not use for any purpose); Warning for SHA1 (deprecated by NIST and Microsoft since 2016; CABrowser Forum banned SHA1 cert issuance)
• Fix: Re-issue all affected certificates using SHA256 or SHA384 signature algorithm; SQL Server's CREATE CERTIFICATE uses SHA1 by default on older instances — explicitly specify a stronger algorithm in newer versions or generate via OpenSSL/certreq with SignatureAlgorithm = sha256RSA

A36 — Production certificate issued by self-signed or untrusted CA

• Trigger: sys.certificates WHERE issuer_name = subject (self-signed) AND the certificate is referenced by an endpoint, linked server, or backup job in a production context
• Severity: Warning — self-signed certificates provide encryption but not authentication; the communicating party cannot verify the identity of the certificate holder; MitM attacks are undetectable with self-signed certs
• Fix: Request a certificate from a trusted internal CA (Microsoft AD CS) or public CA; distribute the CA root certificate to all client machines' Trusted Root Certification Authorities store; remove reliance on TrustServerCertificate bypass after rotation

A37 — No BACKUP CERTIFICATE evidence for certificates with private keys

• Trigger: sys.certificates WHERE pvt_key_encryption_type_desc NOT IN ('NO_PRIVATE_KEY') (certificate has a private key) AND no BACKUP CERTIFICATE operation documented in SQL Agent job history or maintenance scripts
• Severity: Critical — in the event of instance loss (OS corruption, disk failure, ransomware), all data encrypted or signed by these certificates becomes permanently inaccessible without the backed-up private key
• Fix: For each certificate: BACKUP CERTIFICATE [cert] TO FILE = '…' WITH PRIVATE KEY (FILE = '…', ENCRYPTION BY PASSWORD = '…'); store the .cer file, .pvk file, and password in separate physical locations; document the restore procedure and test it

A38 — Multiple certificates sharing the same Subject/CN

• Trigger: sys.certificates GROUP BY subject HAVING COUNT(*) > 1 WHERE name NOT LIKE '##%'
• Severity: Warning — when two certificates share the same subject, T-SQL code referencing the certificate by subject (rather than by name) may select the wrong one; rotation procedures become error-prone; CREATE CERTIFICATE … FROM FILE during disaster recovery may create additional duplicates
• Fix: Retire or rename duplicates; adopt a naming standard that incorporates purpose, environment, and year (e.g., TDE_ProductionDB_2025); include the subject in the cert name to keep them unique

---

Asymmetric and Symmetric Key Management — A39–A43

A39 — Asymmetric key using RSA_512 or RSA_1024

• Trigger: sys.asymmetric_keys WHERE key_length IN (512, 1024) AND name NOT LIKE '##%'
• Severity: Critical for RSA_512 (factorable with modest computational resources as demonstrated by researchers); Warning for RSA_1024 (NIST SP 800-131A prohibited RSA_1024 after 2013 for government use; CAs stopped issuing 1024-bit certs in 2013)
• Fix: CREATE ASYMMETRIC KEY [new_key] WITH ALGORITHM = RSA_2048; re-encrypt any symmetric keys protected by the old asymmetric key: ALTER SYMMETRIC KEY [sk] ADD ENCRYPTION BY ASYMMETRIC KEY [new_key]; ALTER SYMMETRIC KEY [sk] DROP ENCRYPTION BY ASYMMETRIC KEY [old_key]; then DROP ASYMMETRIC KEY [old_key]

A40 — CONTROL permission on an encryption key granted to a non-sysadmin principal

• Trigger: sys.database_permissions WHERE class_desc IN ('SYMMETRIC_KEY', 'ASYMMETRIC_KEY') AND permission_name = 'CONTROL' AND grantee_principal_id NOT IN (list of sysadmin-mapped users in the database)
• Severity: Warning — CONTROL on a symmetric key grants the ability to drop, alter, and (through key-open) access all data encrypted by that key; for asymmetric keys, CONTROL allows re-signing and potentially deriving key material
• Fix: Replace CONTROL with purpose-specific permissions: REFERENCES for Always Encrypted CMK metadata access; ENCRYPT + DECRYPT for CLE operations; EXECUTE for stored procedures that wrap the key operations; revoke CONTROL

A41 — Symmetric key not rotated in over 2 years

• Trigger: sys.symmetric_keys WHERE modify_date = create_date AND create_date < DATEADD(YEAR, -2, GETDATE()) AND name NOT LIKE '##%'
• Severity: Warning — a symmetric CLE key that has never been rotated since creation presents an extended exposure window; PCI-DSS Requirement 3.7.3 requires cryptographic key changes at least once per year for keys protecting cardholder data
• Fix: Create replacement key with updated name and AES_256; re-encrypt data in manageable batches during low-activity window; close and DROP SYMMETRIC KEY [old_key] after verifying all dependent data has been re-encrypted

A42 — Orphaned encryption keys not referenced in any module

• Trigger: Key name from sys.symmetric_keys or sys.asymmetric_keys (excluding ##% system keys) does not appear in any sys.sql_modules.definition or sys.server_sql_modules.definition — no ENCRYPTBYKEY, DECRYPTBYKEY, SIGNBYASYMKEY, or VERIFYBYASYMKEY calls referencing the key
• Severity: Info — orphaned keys are unused attack surface; they are common residue from retired applications, failed migrations, or test databases promoted to production
• Fix: Confirm with application owners that the key is truly unused across all environments and application versions; DROP SYMMETRIC KEY [key] or DROP ASYMMETRIC KEY [key] after verification; scan application source code in addition to SQL modules

A43 — Non-unique or weak KEY_SOURCE in symmetric key definition

• Trigger: T-SQL source containing KEY_SOURCE = 'password', KEY_SOURCE = 'test', or any KEY_SOURCE that is identical across multiple environments (detected via code review, git history, or duplicate key GUIDs visible across database clones)
• Severity: Warning — the KEY_SOURCE parameter is incorporated into the key derivation function; a non-unique KEY_SOURCE means identical symmetric keys are generated in different databases/environments, allowing dev-encrypted data to be decrypted with the prod key
• Fix: Generate a unique random string per database per environment as KEY_SOURCE: SELECT CONVERT(VARCHAR(36), NEWID()) AS unique_source; never reuse KEY_SOURCE across environments; treat KEY_SOURCE as a secret equal in sensitivity to the key itself

---

Key Hierarchy DMK and SMK — A44–A48

A44 — Database Master Key not backed up

• Trigger: sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##' exists (DMK is present) AND no BACKUP MASTER KEY evidence in SQL Agent job history or maintenance documentation
• Severity: Critical — the DMK is the root protector for all certificates, asymmetric keys, and symmetric keys in the database; losing the DMK without a backup means permanent loss of access to all dependent encrypted data across every table that uses CLE
• Fix: BACKUP MASTER KEY TO FILE = 'D:\Keys\[dbname]_master_key.mk' ENCRYPTION BY PASSWORD = 'VaultStoredPassword' — store the .mk file and password in separate secure locations; incorporate into disaster recovery runbook

A45 — DMK not encrypted by the Service Master Key

• Trigger: SELECT is_master_key_encrypted_by_server FROM sys.databases WHERE name = DB_NAME() returns 0
• Severity: Warning — the SMK provides automatic DMK decryption at SQL Server service startup; without SMK encryption, every restart that needs the DMK requires a manual OPEN MASTER KEY DECRYPTION BY PASSWORD = '...' call before any encrypted objects are accessible; this causes application errors until the DBA intervenes
• Fix: ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY — requires that the DMK is currently open or the password is provided; verify with SELECT is_master_key_encrypted_by_server FROM sys.databases WHERE name = DB_NAME() = 1

A46 — DMK protected by password only (no automatic decryption)

• Trigger: Same as A45 — is_master_key_encrypted_by_server = 0 AND no other protection method layered on the DMK
• Severity: Warning — a lost or forgotten DMK password with no SMK backup means the entire CLE key hierarchy for the database becomes permanently inaccessible; the password must be stored securely and documented in the recovery runbook
• Fix: Same as A45: ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY to add automatic decryption; separately back up the DMK (A44) so the password provides a recovery path even after instance migration

A47 — Service Master Key never explicitly backed up

• Trigger: No evidence of BACKUP SERVICE MASTER KEY in any SQL Agent job or maintenance script; the SMK is the root of the entire instance-level key hierarchy (protects all DMKs, linked-server passwords, and proxy account credentials)
• Severity: Critical — if the SQL Server instance is migrated to new hardware, rebuilt after OS failure, or the machine key is damaged (e.g., from Windows re-imaging without preserving DPAPI), all SMK-protected objects become inaccessible on the new instance without an SMK backup
• Fix: BACKUP SERVICE MASTER KEY TO FILE = 'D:\Keys\smk_[hostname]_[date].smk' ENCRYPTION BY PASSWORD = 'VaultStoredPassword' — run once after SQL Server installation and again after any SMK regeneration; store file and password in separate secure locations outside the SQL Server machine

A48 — Linked server connections not using TLS encryption

• Trigger: sys.servers WHERE is_linked = 1 AND server is on a different host (different from @@SERVERNAME) AND the provider string in sys.linked_logins or SSMS Linked Server properties does not include Encrypt=yes or Use Encryption for Data=True
• Severity: Warning — distributed queries, OPENQUERY, and four-part name queries over linked servers traverse the network as plaintext if the connection is not encrypted; linked server credentials are also visible during session setup
• Fix: Drop and re-create the linked server with encryption in the provider string: EXEC sp_addlinkedserver @server = N'RemoteSrv', @srvproduct = N'SQL Server', @provider = N'MSOLEDBSQL', @provstr = N'Encrypt=Mandatory;TrustServerCertificate=no' — ensure the remote SQL Server has a valid CA-signed TLS cert; use MSOLEDBSQL (OLE DB Driver 19+) rather than the deprecated SQLNCLI11 which is removed in SQL Server 2022

---

EKM and Azure Key Vault Integration — A49–A52

A49 — EKM provider installed but inactive or in error state

• Trigger: sys.cryptographic_providers WHERE is_enabled = 0; OR sys.dm_cryptographic_provider_properties WHERE the provider is in an error, not_connected, or degraded state
• Severity: Critical if TDE or any symmetric key uses this EKM provider — an inactive provider causes "Cannot find server certificate" or "The EKM provider key is not found" errors that prevent database startup or decryption
• Fix: Verify provider DLL path is accessible: sys.cryptographic_providers.dll_path; ensure EKM provider enabled = 1 in sys.configurations; restart the EKM provider service; consult vendor documentation for the specific error code; test with OPEN SYMMETRIC KEY using the EKM key

A50 — Azure Key Vault BYOK TDE without automatic key rotation configured

• Trigger: TDE configured with AKV asymmetric key (sys.dm_database_encryption_keys.encryptor_type = 'ASYMMETRIC_KEY') AND no Azure Automation runbook, AKV rotation policy, or SQL Agent job configured to detect key version changes and update the TDE protector
• Severity: Warning — BYOK TDE provides customer control over the encryption key but requires active management; a key that is not rotated for years undermines compliance posture and increases the impact of a key store compromise
• Fix: Set an expiry date and rotation policy on the AKV key via az keyvault key set-attributes --name [key] --vault-name [vault] --expires [date]; configure Azure Monitor alerts on key expiry; use Set-AzSqlServerTransparentDataEncryptionProtector to re-point TDE to the new key version

A51 — TDE using service-managed key in a compliance-regulated Azure SQL environment

• Trigger: Azure SQL Database WHERE the TDE protector type is service-managed (Azure default) AND the database resource tags or naming convention suggest PCI, HIPAA, FedRAMP, ISO 27001, or SOC 2 workloads
• Severity: Info — service-managed TDE is cryptographically sound (AES-256); however several compliance frameworks (PCI-DSS Requirement 3.6, HIPAA addressable safeguard for key management, FedRAMP HIGH) require customer-managed keys to demonstrate key lifecycle control and data sovereignty
• Fix: Switch to BYOK: create or import an RSA 2048/3072/4096 key into AKV; grant the SQL Server managed identity access to the vault; Set-AzSqlServerTransparentDataEncryptionProtector -ServerName [server] -ResourceGroupName [rg] -Type AzureKeyVault -KeyId [akv_key_uri]

A52 — EKM provider version is outdated

• Trigger: sys.cryptographic_providers.provider_version does not match the latest version published by the EKM vendor (compare against vendor release notes; common EKM providers: nCipher, Thales Luna, Azure Key Vault EKM connector)
• Severity: Warning — outdated EKM provider DLLs may have known vulnerabilities (e.g., key derivation weaknesses, authentication bypass); also risk of incompatibility with SQL Server cumulative updates
• Fix: Download the latest provider DLL from the vendor portal; test on a non-production SQL Server instance; update via ALTER CRYPTOGRAPHIC PROVIDER [provider] FROM FILE = 'path\new_provider.dll'; follow vendor's upgrade guide; verify with SELECT * FROM sys.cryptographic_providers after update

---

Compliance and Coverage — A53–A56

A53 — Sensitivity-classified columns without any encryption layer

• Trigger: sys.sensitivity_classifications WHERE information_type IN ('Financial', 'Health', 'Credentials', 'Banking', 'National ID', 'Government', 'Payment') AND the classified column has column_encryption_key_id IS NULL (no AE) AND the column name does not appear in any ENCRYPTBYKEY call in sys.sql_modules
• Severity: Warning — data classification is the first step in a data protection program, not the complete program; a column labeled "Financial" or "Credentials" with no encryption is still readable by any user with SELECT permission
• Fix: Apply Always Encrypted (preferred for Credentials and PII) or CLE (acceptable for less-sensitive Financial aggregates); confirm with the data owner whether masking (sys.masked_columns) is sufficient for some read-paths as a complementary control

A54 — Sensitive-pattern column names without any encryption or classification

• Trigger: sys.columns WHERE name matches any of: ssn, social_security, credit_card, card_number, cvv, cvc, pin, password, passwd, pw, dob, date_of_birth, birth_date, salary, wage, compensation, tax_id, ein, tin, passport, national_id, nhs, medical_record, mrn, diagnosis, prescription, account_number, routing_number, iban, swift — AND column_encryption_key_id IS NULL AND column_id not in sys.sensitivity_classifications
• Severity: Warning — column names are strong indicators of regulated data content; unencrypted and unclassified sensitive columns are a likely compliance violation under PCI-DSS, HIPAA, or GDPR
• Fix: Classify columns with ADD SENSITIVITY CLASSIFICATION TO [schema].[table].[column] WITH (LABEL = '…', INFORMATION_TYPE = '…', RANK = HIGH); apply encryption (A14 fix paths); run a data discovery scan (SQL Data Discovery & Classification in SSMS) to confirm column contents

A55 — Non-FIPS compliant algorithm detected anywhere in the encryption hierarchy

• Trigger: Any of: sys.dm_database_encryption_keys.key_algorithm = TRIPLE_DES_3KEY; sys.symmetric_keys.algorithm_desc IN ('DES', 'RC4', 'DESX', 'RC2', 'Triple_DES'); certificate signature algorithm is MD5 or SHA1 (verify via certutil or PowerShell — CERTPROPERTY() does not expose signature algorithm); msdb.dbo.backupset.key_algorithm = TRIPLE_DES_3KEY; sys.asymmetric_keys.key_length ≤ 1024
• Severity: Critical for RC4, RC2, and MD5 (cryptographically broken; not fixable with configuration; all data protected by these must be considered potentially exposed); Warning for SHA1, DES, DESX, Triple_DES, RSA_1024 (deprecated; not immediately broken but must be replaced for compliance)
• Fix: Each algorithm type requires its corresponding fix: TDE DEK (A5), CLE symmetric keys (A17), certificate hash (A35), backup encryption (A24), asymmetric keys (A39); treat this check as an umbrella finding that points to the individual fixes

A56 — No SQL Server Audit configured for cryptographic key access events

• Trigger: sys.server_audits and sys.database_audit_specifications — no DATABASE_OBJECT_ACCESS_GROUP, SCHEMA_OBJECT_ACCESS_GROUP, or DATABASE_PRINCIPAL_CHANGE_GROUP action group is configured to target symmetric key, asymmetric key, or certificate objects
• Severity: Info — without an audit trail for key access and modification events, it is impossible to detect unauthorized decryption attempts, key exfiltration, certificate deletion, or key rotation that should have occurred; PCI-DSS Requirement 10.2.2 requires auditing of all access to audit logs and cryptographic key management operations
• Fix: Create a database audit specification: ALTER DATABASE AUDIT SPECIFICATION [enc_audit] FOR SERVER AUDIT [instance_audit] ADD (SCHEMA_OBJECT_ACCESS_GROUP); enable the audit: ALTER SERVER AUDIT [instance_audit] WITH (STATE = ON); for finer granularity, add an audit action on sys.symmetric_keys, sys.asymmetric_keys, and sys.certificates object classes

---

TLS and Network Encryption Hardening — A57–A62

A57 — TLS 1.0 or 1.1 enabled at operating system level

• Trigger: SChannel registry key HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Server\Enabled = 1 OR TLS 1.1\Server\Enabled = 1; OR xp_regread output showing either protocol enabled; OR ERRORLOG does not contain "TLS 1.0 is disabled" (SQL 2016+)
• Severity: Warning — TLS 1.0 is prohibited by PCI-DSS v4.0 (effective 2018 for CDE); TLS 1.1 is deprecated with known weaknesses; both expose connections to downgrade attacks (POODLE, BEAST, CRIME). Even if ForceEncryption is enabled, legacy TLS versions weaken the protection.
• Fix: Set Enabled = 0 and DisabledByDefault = 1 for TLS 1.0 and TLS 1.1 under the SChannel registry path; restart SQL Server; verify with nmap --script ssl-enum-ciphers -p 1433 <host> or openssl s_client -tls1_0 -connect <host>:1433

A58 — Weak TLS cipher suites enabled

• Trigger: SChannel registry HKLM\...\SCHANNEL\Ciphers OR TLS cipher order in Group Policy contains RC4, 3DES, DES, NULL, EXPORT ciphers; OR nmap cipher scan reveals weak ciphers offered on port 1433
• Severity: Warning — RC4 and 3DES cipher suites are deprecated (NIST SP 800-131A); NULL ciphers offer zero encryption; EXPORT ciphers are trivially broken (FREAK attack); enabling them downgrades the effective TLS strength regardless of certificate or TLS version
• Fix: Use Group Policy → Computer Configuration → Administrative Templates → Network → SSL Configuration Settings → SSL Cipher Suite Order — specify a list containing only AES-GCM and AES-CBC ciphers with ECDHE key exchange; disable all RC4, 3DES, DES, NULL, MD5, and EXPORT suites; restart SQL Server

A59 — TLS 1.3 not enforced on SQL Server 2022+

• Trigger: SQL Server 2022 or later AND ERRORLOG shows "TLS 1.2" as the highest negotiated protocol (TLS 1.3 not listed) AND the OS is Windows Server 2022+ (capable of TLS 1.3) AND the SChannel registry does not show TLS 1.3 disabled
• Severity: Info — TLS 1.3 provides improved handshake performance (1-RTT vs 2-RTT), mandatory forward secrecy (all ciphers use ECDHE), and removes legacy algorithms entirely; SQL Server 2022 on Windows Server 2022+ supports TLS 1.3 but may not negotiate it if not enabled or if clients do not support it
• Fix: Verify TLS 1.3 is enabled in SChannel registry: HKLM\...\SCHANNEL\Protocols\TLS 1.3\Server\Enabled = 1; ensure Windows Server 2022 or later; update SQL Server drivers to versions supporting TLS 1.3 (ODBC 18+, JDBC 12.4+, .NET 8+); verify with openssl s_client -tls1_3 -connect <host>:1433

A60 — IPsec not configured as compensating control for unencrypted SQL traffic

• Trigger: sys.dm_exec_connections shows encrypt_option = 'FALSE' AND no evidence of IPsec policy securing TCP port 1433 between application servers and SQL Server (no Windows Firewall Connection Security Rule, no netsh ipsec static show policy output, no Group Policy IPsec rule)
• Severity: Info — IPsec provides network-layer encryption and authentication that protects SQL traffic even when TLS is not configured; it is an acceptable compensating control for PCI-DSS Requirement 4 where application-level TLS changes are not immediately feasible; IPsec on port 1433 with ESP/AES encryption can protect credentials and data in transit
• Fix: Create Windows Firewall Connection Security Rule: New-NetIPsecRule -DisplayName "SQL Encryption" -LocalPort 1433 -Protocol TCP -RequireEncryption -Authentication RequireAuthDomain OR configure via Group Policy → Windows Firewall with Advanced Security → Connection Security Rules; test with netsh ipsec dynamic show mmsas

A61 — Kerberos armoring (FAST) not enforced in Active Directory environment

• Trigger: Active Directory-joined SQL Server AND Kerberos armoring (Flexible Authentication Secure Tunneling) not enforced via Group Policy (Computer Configuration → Windows Settings → Security Settings → Local Policies → Security Options → Network security: Restrict NTLM — Kerberos armoring not configured) AND sys.dm_exec_connections.auth_scheme = 'KERBEROS' for domain accounts
• Severity: Info — Kerberos armoring (FAST / RFC 6113) protects Kerberos AS-REQ and TGS-REQ exchanges from offline brute-force attacks on the encrypted timestamp; without armoring, a captured Kerberos pre-authentication packet can be subjected to password cracking; this weakens the authentication layer that TLS depends on
• Fix: Configure Group Policy: Computer Configuration → Administrative Templates → System → Kerberos → Support for Kerberos armoring (FAST) = Enabled and require armoring; requires domain functional level Windows Server 2012+; verify with klist get krbtgt showing Armor: yes

A62 — Named Pipes protocol enabled on production SQL Server

• Trigger: SQL Server Configuration Manager shows Named Pipes protocol enabled for the instance; OR sys.dm_server_registry shows SuperSocketNetLib\Np present and Enabled; Named Pipes traffic is unencrypted by default (SMB encryption is separate)
• Severity: Warning — Named Pipes transport is not encrypted by TLS (TLS applies only to TCP/IP); data traverses SMB which may or may not be encrypted depending on SMB encryption configuration; Named Pipes exposes SQL traffic to any host on the same network segment with SMB access
• Fix: Disable Named Pipes in SQL Server Configuration Manager → SQL Server Network Configuration → Protocols for [Instance] → Named Pipes = Disabled; ensure all applications connect via TCP/IP; if Named Pipes is required for legacy local applications, restrict to local connections only and enable SMB Encryption via Set-SmbServerConfiguration -EncryptData $true

---

Always Encrypted Advanced — A63–A67

A63 — Enclave attestation service URL not configured for enclave-enabled queries

• Trigger: sys.configurations WHERE name = 'column encryption enclave type' AND value_in_use > 0 (enclave enabled) AND the client connection string or application configuration does not include Enclave Attestation Url parameter — OR the attestation URL is missing, unreachable, or using HGS/ICM in an unsupported configuration for the SQL Server version
• Severity: Warning — without a configured and reachable attestation URL, the client driver cannot verify the enclave's identity before sending sensitive plaintext data for in-enclave computation; this defeats the purpose of enclave attestation and allows a compromised host to impersonate the enclave
• Fix: Deploy Microsoft Azure Attestation (MAA) or Host Guardian Service (HGS); configure attestation URL in the client connection string: Enclave Attestation Url = https://<attest-server>/attest/SgxEnclave; verify attestation works with a simple query like SELECT GETDATE() with enclave-enabled connection; monitor attestation failures in SQL Server ERRORLOG

A64 — Always Encrypted driver version incompatible with secure enclave or modern features

• Trigger: Extended Events or sys.dm_exec_sessions.program_name reveals outdated driver versions: ODBC < 17.10, JDBC < 12.2, .NET < 7.0, or OLEDB < 19; AND Always Encrypted columns exist in the database — outdated drivers may lack enclave support, AEAD support, or CEK caching
• Severity: Warning — outdated drivers cannot leverage secure enclave capabilities, may use weaker key exchange algorithms, and may lack CEK caching (causing excessive AKV/HSM round-trips per query); this creates performance degradation and security gaps
• Fix: Update drivers: ODBC Driver 18+ for SQL Server, JDBC Driver 12.4+, Microsoft.Data.SqlClient 5.2+, OLE DB Driver 19+; verify Column Encryption Setting=Enabled in the updated connection string; test all AE queries after the driver update

A65 — Column Encryption Key caching disabled or misconfigured

• Trigger: Always Encrypted columns exist AND application performance logs show high latency on first query after connection (indicating CEK decryption per session, not cached) AND the driver supports CEK caching (ODBC 17.6+, JDBC 12.2+, .NET Core 3.1+) but the application does not configure a CEK cache TTL
• Severity: Info — without CEK caching, the driver must contact the AKV or HSM to decrypt the CEK on every new database connection; this adds 50–200 ms of latency per connection open and increases AKV API call costs; CEK caching with a 2-hour TTL dramatically reduces this overhead
• Fix: Set CEK cache TTL in the client driver: ODBC — Column Encryption Key Cache Time-To-Live = 7200 (seconds); JDBC — columnEncryptionKeyCacheTtl = 7200; .NET — SqlConnection.ColumnEncryptionKeyCacheTtl = TimeSpan.FromHours(2); monitor AKV API call volume after enabling to confirm reduction

A66 — Secure enclave configured but no columns using enclave-enabled computations

• Trigger: sys.configurations WHERE name = 'column encryption enclave type' AND value_in_use > 0 (enclave enabled, consuming VBS memory and attestation infrastructure) AND no columns use ENCRYPTION_TYPE = RANDOMIZED with enclave-enabled queries OR no LIKE, BETWEEN, or range queries on encrypted columns are observed
• Severity: Info — enabling a secure enclave consumes system resources (VBS reserves memory, attestation service runs); if no workloads leverage the enclave, this overhead is wasted and represents a misconfigured investment; the enclave was likely enabled for proof-of-concept but never operationalized
• Fix: Audit the Always Encrypted column inventory for columns that would benefit from enclave computations (randomized encrypted columns queried with LIKE, BETWEEN, range, or JOIN); if no such workload exists, consider disabling the enclave: EXEC sp_configure 'column encryption enclave type', 0; RECONFIGURE — then monitor for functional impact on AE queries

A67 — Enclave attestation protocol set to allow relaxed attestation

• Trigger: The SQL Server enclave configuration allows "relaxed" attestation (no HGS attestation URL configured, or HGS attestation mode set to None) in a production environment; the client can connect to the enclave without verifying its identity
• Severity: Warning — relaxed attestation means the client accepts any process claiming to be the enclave without cryptographic proof; in a compromised host scenario, a malicious process can intercept enclave computations and read plaintext; relaxed attestation is acceptable for development only
• Fix: Configure HGS or MAA attestation on the SQL Server host: Initialize-HgsServer -HgsServerName <hgs> -HgsDomainName <domain>; register the SQL Server with the HGS; configure the enclave to require attestation; update all client connection strings with the correct attestation URL; test attestation with SELECT GETDATE() before disabling relaxed attestation

---

Operational Key Lifecycle — A68–A72

A68 — DMK or SMK backup password does not meet complexity requirements

• Trigger: No evidence of DMK/SMK password complexity enforcement (length < 14 characters, no special characters, no mixed case, no digits) — inferred from documented backup procedures, SQL Agent job step text, or password vault entries
• Severity: Warning — a weak DMK/SMK backup password is the single point of failure for the entire database or instance encryption hierarchy; a brute-forced backup password gives an attacker full access to all encrypted data in the database or instance
• Fix: Regenerate the backup with a strong password: BACKUP MASTER KEY TO FILE = '...' ENCRYPTION BY PASSWORD = 'UseAVaultWith32+CharRandomString'; store the password in a secrets management vault (Azure Key Vault, HashiCorp Vault, CyberArk); enforce password complexity via organizational policy; use a password generator with ≥ 20 characters, mixed case, digits, and special characters

A69 — TLS certificate not configured for automatic enrollment via AD CS

• Trigger: SQL Server TLS certificate is manually managed (no auto-enrollment template configured in AD CS Group Policy) AND the certificate has expired or will expire within 90 days AND the SQL Server is domain-joined
• Severity: Info — Active Directory Certificate Services auto-enrollment ensures TLS certificates are renewed before expiry without manual intervention; manual certificate management is the primary cause of production outages from expired TLS certificates (A30)
• Fix: Configure AD CS certificate template for SQL Server TLS with auto-enrollment permission; apply via Group Policy: Computer Configuration → Windows Settings → Security Settings → Public Key Policies → Certificate Services Client - Auto-Enrollment → Enabled; set template validity period to 1–2 years; test renewal by running certutil -pulse and verifying the new certificate in the machine Personal store

A70 — No documented key archival or escrow procedure

• Trigger: No evidence of a documented key escrow procedure (physical or digital) — no key archival runbook, no escrow log, no documented split-knowledge procedure for key material recovery; AND certificates or master keys exist in the encryption hierarchy
• Severity: Warning — key escrow ensures that encryption keys can be recovered even if the primary custodians are unavailable (emergency, departure, disaster); without a documented escrow procedure, the organization has no guaranteed recovery path for encrypted data; PCI-DSS Requirement 3.7.6 mandates split-knowledge and dual-control procedures for manual clear-text key operations
• Fix: Document and test a key escrow procedure: define primary and secondary key custodians; establish a secure physical or digital escrow location (safe, vault, offline HSM); require dual authorization for escrow access; conduct annual escrow audits and recovery tests; integrate into the disaster recovery runbook

A71 — TDE encryption scan I/O impact never baselined or monitored

• Trigger: TDE is enabled or was recently enabled AND no performance baseline was captured before the encryption scan (no sys.dm_io_virtual_file_stats snapshot from before the scan, no PerfMon Physical Disk: Avg. Disk sec/Read and Avg. Disk sec/Write pre-scan baseline) AND the database is larger than 50 GB
• Severity: Info — a TDE encryption scan reads every page of every data file and writes it back encrypted; on large databases (hundreds of GB to TB), this can cause days of elevated I/O; without a pre-scan baseline, it is impossible to quantify the impact or distinguish scan overhead from normal workload I/O
• Fix: Before enabling TDE, capture baseline I/O metrics: SELECT * FROM sys.dm_io_virtual_file_stats(DB_ID('SalesDB'), NULL); start PerfMon with Physical Disk and SQL Server Buffer Manager counters; enable TDE during a maintenance window; monitor percent_complete in sys.dm_database_encryption_keys; on SQL 2019+, use ALTER DATABASE [db] SET ENCRYPTION SUSPEND during peak hours

A72 — TDE-protected database log backups lack secondary backup-level encryption

• Trigger: TDE is enabled on the database (sys.dm_database_encryption_keys shows encryption_state = 3) AND log backups exist (msdb.dbo.backupset WHERE type = 'L') AND key_algorithm IS NULL for those log backups (no WITH ENCRYPTION was specified)
• Severity: Info — When TDE is enabled, all backup files (.bak and .trn) already contain TDE-encrypted pages and require the TDE certificate to restore — they are NOT readable without it. However, adding explicit backup encryption (WITH ENCRYPTION) provides a separate defense-in-depth layer: the backup file is additionally protected by a distinct backup certificate, so that even possession of the TDE certificate alone does not grant access to the backup file. This is relevant when backup files are stored in locations with different access controls than the SQL Server instance.
• Fix: If defense-in-depth backup protection is required by your security policy: add WITH ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = [backup_cert]) to all BACKUP LOG statements and ensure the backup certificate is backed up separately. Verify with: SELECT key_algorithm FROM msdb.dbo.backupset WHERE type = 'L' AND backup_start_date > DATEADD(DAY, -7, GETDATE()). Note: this is a hardening recommendation — TDE backups are already protected and cannot be restored without the TDE certificate.

---

SQL Server Ledger — A73–A76

A73 — Ledger not enabled on database with immutability or audit requirements

• Trigger: Database naming convention or metadata suggests regulatory or audit requirements (names containing: audit, compliance, financial, legal, regulatory, tax) AND sys.databases WHERE is_ledger_on = 0 AND SQL Server version is 2022+
• Severity: Info — SQL Server Ledger provides cryptographic verification that data has not been tampered with; ledger tables maintain a SHA-256 hash chain and generate periodically published database digests that can be verified by external auditors; without Ledger, database integrity relies entirely on access controls and audit logs, which can be bypassed by a sysadmin
• Fix: Enable Ledger on the database: CREATE DATABASE [FinanceLedger] WITH LEDGER = ON; migrate existing tables to ledger tables: CREATE TABLE [dbo].[Transactions] (...) WITH (LEDGER = ON (APPEND_ONLY = ON)); verify with SELECT is_ledger_on FROM sys.databases WHERE name = DB_NAME()

A74 — Ledger database digest not configured for automatic storage

• Trigger: sys.databases WHERE is_ledger_on = 1 AND no Azure Storage account, Azure Confidential Ledger, or file share is configured for automatic digest upload — the digest is only generated locally (no external verification possible)
• Severity: Warning — database digests are the cryptographic proof that data has not been tampered with; storing them only on the same server defeats the purpose — a compromised sysadmin can delete or modify the digest history; external digest storage provides tamper-evident verification that auditors can use independently
• Fix: Configure automatic digest storage: EXEC sp_generate_database_ledger_digest @digest_storage_endpoint = 'https://<account>.blob.core.windows.net/<container>' to an Azure Storage account or an Azure Confidential Ledger instance; configure a SQL Agent job to run sp_generate_database_ledger_digest on a schedule (e.g., daily); test verification using EXEC sys.sp_verify_database_ledger_from_digest_storage after enabling ALLOW_SNAPSHOT_ISOLATION on the database

A75 — Ledger table hash algorithm not SHA-256

• Trigger: Ledger database or ledger tables exist — this is a documentation/audit-evidence check. The ledger hash algorithm is not configurable and is not exposed by any catalog view (there is no sys.database_ledger_configurations view); per MS Learn, ledger always uses SHA-256 (Merkle tree over rows, transactions, and blocks)
• Severity: Info — SHA-256 is the fixed hash algorithm for ledger integrity in SQL Server 2022; if a future SQL Server version introduces a configurable or weaker algorithm, re-evaluate this check
• Fix: Record SHA-256 as the ledger hash algorithm for audit evidence (confirmed by Ledger overview — "cryptographically SHA-256 hashed using a Merkle tree"); confirm blocks are forming via SELECT * FROM sys.database_ledger_blocks and digests are generated/stored; there is no algorithm setting to change

A76 — Ledger verification not scheduled on a recurring basis

• Trigger: Ledger database exists AND no SQL Agent job or Azure Automation runbook is configured to run sp_verify_database_ledger or sp_verify_database_ledger_from_digest_storage on a recurring schedule (at least monthly) — SQL 2022+ only
• Severity: Warning — ledger verification is the only way to detect tampering; if verification is never run, tampered data may go undetected for months or years, by which time the evidence trail may be cold; the ledger's security model depends on regular verification by an external party (auditor, compliance team)
• Fix: Prerequisite: ALTER DATABASE [db] SET ALLOW_SNAPSHOT_ISOLATION ON (required before running either verification procedure). For automatic digest storage (Azure Blob / ACL configured): EXEC sys.sp_verify_database_ledger_from_digest_storage @locations = (SELECT * FROM sys.database_ledger_digest_locations FOR JSON AUTO, INCLUDE_NULL_VALUES). For manual digest storage (JSON digest passed directly): EXEC sys.sp_verify_database_ledger @digests = N'[{"database_name":"db",...}]'. Schedule weekly or monthly via SQL Agent; configure alerts on verification failure; document the verification schedule in the audit runbook.

---

Azure-Specific Encryption — A77–A80

A77 — Azure SQL TDE protector key vault in different Azure region

• Trigger: Azure SQL Database or Managed Instance WHERE the TDE protector is customer-managed (BYOK) AND the Azure Key Vault is in a different Azure region from the SQL Server resource; OR the Key Vault is in a different subscription or tenant without proper cross-tenant authorization
• Severity: Warning — a cross-region Key Vault adds network latency to every DEK decryption operation (at database startup and after failover events), increases the risk of transient Key Vault unavailability due to region-specific Azure outages, and complicates the network security boundary (VNet service endpoints may not span regions)
• Fix: Provision an Azure Key Vault in the same region as the SQL Database or Managed Instance; migrate the TDE protector key to the regional vault using az keyvault key import or key rotation; update the TDE protector: Set-AzSqlServerTransparentDataEncryptionProtector -ServerName <srv> -ResourceGroupName <rg> -Type AzureKeyVault -KeyId <new_key_uri>; verify region affinity in Azure Portal

A78 — Double encryption (Infrastructure + TDE) not enabled on Azure SQL for high-compliance workloads

• Trigger: Azure SQL Database tagged with compliance designations (PCI, HIPAA, FedRAMP, ISO 27001) AND the database does not have infrastructure encryption enabled (check Azure Portal → SQL Database → Transparent Data Encryption → "Infrastructure encryption" toggle is off) AND the service tier supports it (Premium, Business Critical, Hyperscale)
• Severity: Info — Azure SQL's infrastructure encryption provides an additional layer of AES-256 encryption at the storage infrastructure level, beneath TDE; this protects against physical media theft at the Azure data center level; combined with customer-managed TDE, it provides defense-in-depth against cloud-provider-level threats
• Fix: In Azure Portal → SQL Database → Transparent Data Encryption → enable "Infrastructure encryption" toggle; this can only be enabled at database creation or during a service tier change — it cannot be enabled on an existing database; for existing databases, create a new database with infrastructure encryption enabled and migrate data using CREATE DATABASE ... AS COPY OF or Azure DMS

A79 — Always Encrypted enclave attestation shared across multiple tenants or applications

• Trigger: Azure SQL Database or Managed Instance with secure enclave enabled AND the attestation provider (MAA or HGS) is shared across multiple applications, tenants, or SQL Server instances without tenant isolation
• Severity: Warning — a shared attestation provider means that any client trusting that attestation provider can attest any enclave on any SQL Server registered with it; in a multi-tenant scenario, this allows a compromised application to impersonate the enclave of another tenant's database; dedicated attestation providers provide security isolation
• Fix: Deploy a dedicated MAA or HGS instance per security boundary (per tenant, per application tier, or per compliance domain); register only SQL Server instances within that boundary with the dedicated attestation provider; update client connection strings to use the dedicated attestation URL; verify isolation by testing cross-boundary attestation failure

A80 — Azure SQL Database audit logs not encrypted at rest

• Trigger: SQL Server Audit is configured on Azure SQL Database (sys.server_audits or sys.database_audit_specifications exist) AND the audit log destination is Azure Blob Storage AND the storage account does not have Storage Service Encryption (SSE) enabled OR uses a storage account without customer-managed keys for compliance workloads
• Severity: Info — audit logs contain sensitive operational data including query text, parameter values, and user identities; unencrypted audit logs expose this data to anyone with access to the storage account; PCI-DSS Requirement 10 requires protection of audit trail data from unauthorized modification
• Fix: Enable Azure Storage Service Encryption on the audit log storage account (enabled by default for new accounts — verify it is on); for PCI/HIPAA workloads, configure customer-managed keys for the storage account: az storage account update --name <account> --encryption-key-source Microsoft.KeyVault --encryption-key-vault <vault_uri> --encryption-key-name <key_name>; verify with az storage account show --name <account> --query encryption

---

DMK Password Auto-Open — A81–A86

The sp_control_dbmasterkey_password mechanism stores a database DMK password as a credential in master.sys.credentials (encrypted by the SMK), linked to the database's stable family_guid in master.sys.master_key_passwords. SQL Server uses it to auto-open password-only DMKs at startup or restore time. This applies to any database whose DMK is not encrypted by the SMK — including SSISDB by design, cross-server restores, deliberate isolation architectures, and AG secondaries.

A81 — Database has non-SMK DMK without registered password in sys.master_key_passwords

• Trigger: User database (database_id > 4) has is_master_key_encrypted_by_server = 0 AND no matching entry in master.sys.master_key_passwords for that database
• Severity: Warning — SQL Server cannot auto-open the DMK after restart or failover; any DECRYPTBYKEY, OPEN SYMMETRIC KEY, or cert-protected object access fails with "Please create a master key in the database or open the master key in the session before performing this operation"
• Fix: Register the password: EXEC sp_control_dbmasterkey_password @db_name = N'[db]', @password = N'[dmk_password]', @action = N'add'; or add SMK protection: USE [db]; ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

A82 — SSISDB DMK password not registered with sp_control_dbmasterkey_password

• Trigger: Database named SSISDB exists AND is_master_key_encrypted_by_server = 0 AND no entry for SSISDB in master.sys.master_key_passwords
• Severity: Critical — SSISDB deliberately creates its DMK without SMK protection (security isolation by design); SSIS catalog operations (package execution, environment variable decryption, sensitive parameter access) all fail with error 15581 after every SQL Server restart until the password is registered — SQL 2012+
• Fix: EXEC sp_control_dbmasterkey_password @db_name = N'SSISDB', @password = N'[catalog_creation_password]', @action = N'add'; the password is the one supplied during SSISDB.catalog.create_catalog; if lost, the SSISDB catalog must be dropped and recreated

A83 — Service Master Key restored from foreign instance: registered DMK passwords invalidated

• Trigger: Evidence of RESTORE SERVICE MASTER KEY FROM FILE in recent ERRORLOG or SQL Agent job history AND entries exist in master.sys.master_key_passwords — credentials were encrypted with the source instance's SMK and cannot be decrypted by the restored (foreign) SMK
• Severity: Warning — only cross-instance SMK restore invalidates registered passwords; ALTER SERVICE MASTER KEY REGENERATE on the same instance re-encrypts credentials automatically and does NOT invalidate registrations
• Fix: After restoring a foreign SMK backup: for each database in sys.master_key_passwords, run @action = 'drop' then immediately @action = 'add' with the current DMK password; maintain a vault-stored inventory of which databases have registered passwords

A84 — Non-SMK DMK database has no auto-open path configured

• Trigger: User database with is_master_key_encrypted_by_server = 0 AND no entry in master.sys.master_key_passwords AND no SQL Agent startup job containing OPEN MASTER KEY DECRYPTION BY PASSWORD for that database
• Severity: Warning — three auto-open paths exist: (1) SMK protection, (2) password registered via sp_control_dbmasterkey_password, (3) startup SQL Agent job; having none means every restart leaves encrypted objects inaccessible until a DBA manually intervenes
• Fix: Choose one path: ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY (simplest), or sp_control_dbmasterkey_password @action = 'add', or create a SQL Agent startup job executing OPEN MASTER KEY DECRYPTION BY PASSWORD = N'...'

A85 — Restored or attached database with non-SMK DMK: password not re-registered on target

• Trigger: Database has is_master_key_encrypted_by_server = 0 AND MSDB restore history (msdb.dbo.restorehistory) shows it was restored from backup AND no sys.master_key_passwords entry for this database exists on the current instance
• Severity: Warning — sys.master_key_passwords is instance-local; restoring a database to a new SQL Server does not carry DMK password registration; the family_guid is preserved from the source but the target instance has no corresponding entry
• Fix: On the target instance: EXEC sp_control_dbmasterkey_password @db_name = N'[db]', @password = N'[dmk_password]', @action = N'add'; add this step to all database restore runbooks; verify: SELECT * FROM master.sys.master_key_passwords

A86 — AG secondary with non-SMK DMK: password not registered on secondary replicas

• Trigger: Availability Group exists AND a member database has is_master_key_encrypted_by_server = 0 AND the password is absent from master.sys.master_key_passwords on one or more secondary replicas
• Severity: Warning — on AG failover, the new primary inherits the database but not the sys.master_key_passwords entry from the former primary; the DMK auto-open path breaks on the new primary until a DBA re-registers; AG seeding does NOT carry over password registrations
• Fix: Run EXEC sp_control_dbmasterkey_password @action = 'add' on every AG replica (primary and all secondaries); include in AG deployment checklist and failover runbook

---

Dynamic Data Masking and Permission Patterns — A87–A91

A87 — Sensitive column masked but not encrypted

• Trigger: sys.masked_columns WHERE column name matches sensitive patterns (ssn, card, password, salary, dob, tax_id, passport, diagnosis, account_number) AND column_encryption_key_id IS NULL AND not referenced by ENCRYPTBYKEY in any module — SQL 2016+
• Severity: Warning — Dynamic Data Masking is an access-control presentation layer, not encryption; any user with UNMASK permission, sysadmin role, SELECT INTO a temp table, or who exploits inference attacks receives unmasked data; DDM does not protect at-rest data against privileged access or storage-level exposure
• Fix: Apply Always Encrypted (client-side) or CLE for columns requiring genuine data confidentiality; retain DDM as a secondary display filter for application users; load references/howto-dynamic-data-masking.md for the decision tree

A88 — UNMASK permission granted to a broad role

• Trigger: sys.database_permissions WHERE permission_name = 'UNMASK' AND grantee_principal_id maps to a role with more than 1 member, or is granted to PUBLIC — SQL 2016+
• Severity: Warning — UNMASK on a role grants all current and future role members unrestricted read access to every masked column in the database; DDM becomes ineffective as a control
• Fix: REVOKE UNMASK FROM [role]; grant UNMASK only to specific named principals who genuinely need it; use SELECT * FROM sys.database_permissions WHERE permission_name = 'UNMASK' to audit

A89 — CONTROL permission on certificate granted to non-sysadmin

• Trigger: sys.database_permissions WHERE class_desc = 'CERTIFICATE_OBJECT' AND permission_name = 'CONTROL' AND grantee_principal_id is not a sysadmin-mapped database user
• Severity: Warning — CONTROL on a certificate allows DROP, ALTER, and use for encryption/decryption/signing without restriction; over-privileged access on TDE, Service Broker, or backup certs creates a privilege escalation path (extends A40 which covers keys only)
• Fix: Replace CONTROL with purpose-specific grants: REFERENCES for AE CMK metadata, EXECUTE for signing procedures; sysadmins have implicit access; run REVOKE CONTROL ON CERTIFICATE::[cert] FROM [principal]

A90 — Row-Level Security predicate referencing an Always Encrypted column

• Trigger: sys.security_policies exist AND the predicate function definition references a table column with column_encryption_key_id IS NOT NULL
• Severity: Warning — AE columns are encrypted before reaching the server-side SQL engine; RLS filter predicates receive encrypted byte strings, not plaintext; the predicate either always fails (zero rows) or always passes (all rows), depending on the comparison operator — it never filters correctly
• Fix: Move row-filtering logic to the application layer for AE-protected columns; use deterministic AE with application-side WHERE clauses; document this limitation for compliance reviewers

A91 — CLE-encrypted column without masked fallback exposes cipher text to reporting users

• Trigger: Table has columns encrypted with ENCRYPTBYKEY (CLE) AND the same columns have no sys.masked_columns entry AND the table has SELECT permission granted to a non-sysadmin role
• Severity: Info — reporting users who lack the open symmetric key receive raw varbinary cipher text, confirming the column is encrypted and potentially leaking data-length patterns
• Fix: Add a default DDM mask: ALTER TABLE [dbo].[t] ALTER COLUMN [col] ADD MASKED WITH (FUNCTION = 'default()'); grant UNMASK only to principals who also have the open key; cipher text is returned only when BOTH UNMASK and the key are available

---

Compliance Explicit Checks — A92–A98

A92 — PCI-DSS v4: PAN stored without column-level encryption

• Trigger: Columns matching card_number, pan, primary_account, credit_card, debit_card, cardholder_number exist AND column_encryption_key_id IS NULL AND no ENCRYPTBYKEY module reference — even if TDE is enabled on the database
• Severity: Critical — PCI-DSS v4.0 Requirement 3.5.1 requires PAN to be rendered unreadable at rest; TDE does not satisfy this because any SQL user with SELECT permission reads the plaintext PAN; TDE protects only against physical media theft
• Fix: Apply Always Encrypted with deterministic encryption (enabling indexed lookups) to all PAN columns; truncate or tokenise where the full PAN is not needed; document the chosen approach in PCI-DSS evidence

A93 — PCI-DSS v4: no evidence of annual key rotation for cardholder data keys

• Trigger: sys.symmetric_keys or sys.certificates used by CLE or backup encryption on PCI-scope databases WHERE modify_date = create_date AND age > 365 days
• Severity: Warning — PCI-DSS v4.0 Requirement 3.7.3 requires cryptographic key changes "at least annually" or on suspected compromise
• Fix: Create an annual rotation schedule in SQL Agent; use year-suffixed key naming (CLE_PAN_2026); document rotation history in the PCI key management policy

A94 — GDPR Art. 17: PII in append-only ledger table without crypto-shredding strategy

• Trigger: sys.tables with ledger_type = 3 (APPEND_ONLY_LEDGER_TABLE) AND columns matching PII patterns exist AND no per-subject encryption key column is present — SQL 2022+ (note: ledger_type = 2 is UPDATABLE_LEDGER_TABLE, which retains a history table but does permit logical DELETE)
• Severity: Warning — append-only ledger tables are cryptographically immutable; GDPR Article 17 right-to-erasure cannot be honoured without a crypto-shredding strategy (per-subject encryption key deleted on erasure request)
• Fix: Use updatable ledger tables for PII (support DELETE with history audit); OR implement per-subject CLE keys and document the shredding procedure in howto-crypto-shredding.md; OR store only pseudonymous identifiers in append-only ledger

A95 — FIPS: Windows FIPS mode not enabled on SQL Server host for regulated workloads

• Trigger: SQL Server ERRORLOG does not contain "FIPS compliance mode is enabled" at the most recent startup, AND the deployment is tagged or named as FedRAMP HIGH / CMMC / DoD IL4+
• Severity: Warning — FedRAMP HIGH, CMMC Level 3, and DoD IL4+ require FIPS 140-2 validated cryptographic modules; Windows FIPS mode is the enforcement mechanism for SQL Server; without it, non-FIPS operations may succeed silently
• Fix: Group Policy → Computer Configuration → Windows Settings → Security Settings → Local Policies → Security Options → "System cryptography: Use FIPS compliant algorithms" = Enabled; restart SQL Server; test all applications (some older ODBC/JDBC drivers fail in FIPS mode)

A96 — FIPS environment using software-only EKM provider (no HSM backing)

• Trigger: sys.cryptographic_providers shows an EKM provider AND the DLL name or path does not match known FIPS 140-2 Level 2+ validated HSM vendors (nCipher, Thales Luna/SafeNet, Entrust, IBM 4769, AWS CloudHSM, Azure Dedicated HSM)
• Severity: Warning — software EKM providers do not provide the physical tamper resistance required for FIPS 140-2 Level 2; key material can be extracted via memory dumps or disk forensics
• Fix: Replace with a NIST CMVP-validated HSM provider; verify the certification number at csrc.nist.gov/projects/cryptographic-module-validation-program

A97 — No documented key custodian or key management policy

• Trigger: No SQL Agent job with key/cert/dmk/smk naming pattern; no sys.server_audits active; no sys.sensitivity_classifications; no DATABASE_OBJECT_ACCESS_GROUP audit specification for encryption objects
• Severity: Info — PCI-DSS v4.0 Requirement 3.7.6 requires split-knowledge and dual-control for manual key operations; NY DFS 23 NYCRR 500 §500.15 and CMMC SC.3.191 require documented cryptographic controls
• Fix: Create a key inventory document; schedule SQL Agent maintenance jobs with named owners; configure SQL Server Audit for key access events (A56 fix); document custodians in job descriptions

A98 — HIPAA: PHI columns without column-level encryption and no SELECT audit trail

• Trigger: Columns matching PHI patterns (ssn, diagnosis, medical_record, mrn, prescription, dob, patient_id, condition, treatment) exist AND column_encryption_key_id IS NULL AND no SQL Audit action group targets SELECT on the table
• Severity: Warning — HIPAA 45 CFR §164.312 requires both encryption safeguards (§164.312(a)(2)(iv)) and audit controls (§164.312(b)); unencrypted PHI columns without audit fail both safeguards simultaneously
• Fix: Apply Always Encrypted to PHI columns; add SQL Server Audit: ADD (SELECT ON OBJECT::[schema].[table] BY [role]); retain audit logs for minimum 6 years per HIPAA §164.316(b)(2)

---

Operational Validation — A99–A104

A99 — SQL Agent job step containing hardcoded OPEN SYMMETRIC KEY password

• Trigger: msdb.dbo.sysjobsteps WHERE command LIKE '%OPEN SYMMETRIC KEY%DECRYPTION BY PASSWORD%'
• Severity: Critical — passwords in SQL Agent job step T-SQL are stored in MSDB in near-plaintext, appear verbatim in SSMS, are logged on step failure, and are visible to all principals with sysadmin or SQLAgentOperatorRole; the key material is fully exposed to anyone with SSMS or MSDB access
• Fix: Replace password-based key protection with certificate-based: OPEN SYMMETRIC KEY [key] DECRYPTION BY CERTIFICATE [cert]; the cert's private key is protected by the DMK which auto-opens via SMK — no password required in job code

A100 — Plan cache contains OPEN SYMMETRIC KEY with visible password

• Trigger: sys.dm_exec_sql_text cross-applied via sys.dm_exec_query_stats WHERE query text LIKE '%OPEN SYMMETRIC KEY%DECRYPTION BY PASSWORD%'
• Severity: Critical — plan cache entries are visible to all principals with VIEW SERVER STATE (operators, APM agents, monitoring tools, 3rd-party DBA tools); passwords appear in full text; parameters to sp_control_dbmasterkey_password are protected from traces, but ad-hoc T-SQL is not
• Fix: Migrate to certificate-based key protection (A99 fix); run DBCC FREEPROCCACHE to evict the exposed plan immediately; audit who holds VIEW SERVER STATE

A101 — Azure Key Vault soft-delete disabled or purge protection not enforced

• Trigger: Azure SQL using AKV BYOK TDE or AE CMK AND the vault has enableSoftDelete = false OR enablePurgeProtection = false
• Severity: Critical — without purge protection an AKV key can be permanently deleted within minutes; TDE-protected databases become permanently inaccessible; this misconfiguration is common in dev vaults promoted to production
• Fix: az keyvault update --name [vault] --enable-soft-delete true --enable-purge-protection true; set minimum retention ≥ 90 days; enforce via Azure Policy at subscription level

A102 — No annual encrypted backup restore test on record

• Trigger: Most recent RESTORE in msdb.dbo.restorehistory for databases with encrypted backups is older than 365 days OR no restore history exists
• Severity: Warning — certificate rotation, SMK changes, or infrastructure migration may silently break encrypted restore; the first evidence of a broken restore chain should not be an actual disaster
• Fix: Schedule annual restore test to an isolated test environment; restore the certificate first; verify RESTORE DATABASE [db] WITH RECOVERY; document the test result and certificate thumbprint in the DR runbook

A103 — sys.credentials age exceeds 1 year without rotation evidence

• Trigger: sys.credentials WHERE modify_date < DATEADD(YEAR, -1, GETDATE()) AND credential_identity is not a Windows or AAD account (i.e., key-string or secret-based EKM/proxy credential)
• Severity: Info — credentials for EKM providers and proxy accounts are encrypted by the SMK; long-lived secrets increase exposure window
• Fix: Rotate via ALTER CREDENTIAL [name] WITH IDENTITY = N'[id]', SECRET = N'new_secret'; coordinate with the HSM/AKV/proxy vendor; update any dependent applications

A104 — AG listener TLS certificate does not include listener DNS name in SAN

• Trigger: Availability Group exists AND the AG listener DNS name is not present in the Subject Alternative Name of the TLS certificate bound to SQL Server (listener name differs from the instance hostname in the certificate CN or SAN)
• Severity: Warning — clients connecting via the AG listener FQDN may receive a certificate name mismatch error or fall back to self-signed cert validation if the listener name is not in the SAN
• Fix: Include AG listener DNS names in the TLS certificate SAN when requesting from CA; rebind the updated cert in SQL Server Configuration Manager; test: openssl s_client -connect aglistener:1433 -starttls mssql

---

Advanced Cryptographic Patterns — A105–A112

A105 — TLS cipher suite ordering: ECDHE not prioritised over RSA key exchange

• Trigger: Windows registry HKLM\SOFTWARE\Policies\Microsoft\Cryptography\Configuration\SSL\00010002\Functions shows RSA key-exchange suites appearing before ECDHE suites, or ECDHE suites absent
• Severity: Info — ECDHE provides Perfect Forward Secrecy (PFS): a compromised server private key cannot decrypt past sessions; RSA key exchange does not; GCM is also preferable to CBC (authenticated encryption, no POODLE variant risk)
• Fix: Use IIS Crypto or Group Policy to reorder: prioritise TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 and TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 above all RSA-exchange suites; disable all NULL/EXPORT suites

A106 — Remote connections authenticating via NTLM

• Trigger: sys.dm_exec_connections WHERE auth_scheme = 'NTLM' AND client_net_address NOT IN ('<local machine>', '127.0.0.1', '::1')
• Severity: Info — NTLM is vulnerable to relay attacks (Pass-the-Hash, NTLM relay); no mutual authentication; Kerberos with armoring provides stronger session security; NTLM on encrypted TLS is lower risk but still a weaker auth plane
• Fix: Register SPNs correctly to enable Kerberos: setspn -S MSSQLSvc/hostname:1433 [service_account]; see /sqlspn-review for full SPN audit

A107 — Service Broker: remote endpoint certificate not imported into target database

• Trigger: sys.endpoints WHERE type_desc = 'SERVICE_BROKER' AND connection_auth_desc LIKE '%CERTIFICATE%' AND the endpoint certificate public key is not present as a no-private-key certificate in any other database's sys.certificates
• Severity: Warning — Service Broker certificate authentication requires the initiating database's public certificate to be imported into the target database; without it, messages are rejected with "The certificate used to sign the message has an unknown issuer" or "There is no remote service binding"
• Fix: Export cert (public key only): BACKUP CERTIFICATE [sb_cert] TO FILE = '...' (no WITH PRIVATE KEY); import into target: CREATE CERTIFICATE [remote_cert] AUTHORIZATION [user] FROM FILE = '...'

A108 — ENCRYPTBYPASSPHRASE used with weak or visible passphrase in T-SQL source

• Trigger: sys.sql_modules WHERE definition LIKE '%ENCRYPTBYPASSPHRASE%' AND the passphrase literal appears in source (not parameterised); flag if passphrase is < 16 characters
• Severity: Warning — ENCRYPTBYPASSPHRASE uses a weak key derivation function (few iterations, no salt stretching); on SQL Server 2016 and earlier it uses Triple DES 128-bit; from SQL Server 2017+ it uses AES-256 (a significant improvement, but the weak KDF remains). Short or dictionary passphrases are brute-forceable with GPU acceleration regardless of version; the passphrase is visible in T-SQL source, plan cache, and job step history. On SQL 2016 and earlier, treat all uses as Critical due to Triple DES.
• Fix: Migrate to AES_256 symmetric key: CREATE SYMMETRIC KEY [k] WITH ALGORITHM = AES_256, ENCRYPTION BY CERTIFICATE [cert]; use ENCRYPTBYKEY instead; if ENCRYPTBYPASSPHRASE must be retained, use a randomly generated 32+ char passphrase stored in a vault, never in T-SQL source

A109 — HASHBYTES using deprecated algorithm in security-sensitive context

• Trigger: sys.sql_modules WHERE definition LIKE '%HASHBYTES%' AND matches HASHBYTES('MD2' OR HASHBYTES('MD4' OR HASHBYTES('MD5' OR HASHBYTES('SHA' (SHA-1 shorthand) in modules with names suggesting password, token, signature, or fingerprint processing
• Severity: Warning — MD2/MD4/MD5 are cryptographically broken (collision attacks practical); SHA-1 is deprecated (NIST 2030 sunset, removed from TLS); using these for password hashing, HMAC, or data integrity provides no meaningful security guarantee
• Fix: Replace with HASHBYTES('SHA2_256', ...) or HASHBYTES('SHA2_512', ...); for password storage, use application-layer bcrypt/scrypt/Argon2 (SQL Server has no built-in password-hashing primitive)

A110 — Database Mail SMTP credentials without modern authentication

• Trigger: msdb.dbo.sysmail_account WHERE enable_ssl = 0 (plaintext SMTP) OR use_default_credentials = 0 AND password-based auth stored in sys.credentials (SMK-encrypted only)
• Severity: Info — Database Mail SMTP credentials are stored in MSDB encrypted only by the SMK; any sysadmin can extract them; plaintext SMTP transmits credentials over the network without TLS
• Fix: Use Windows Authentication for SMTP relay when available; migrate to OAuth2/modern auth for Office 365 SMTP (SQL Server 2022 CU14+ supports OAuth2 for Database Mail); enable enable_ssl = 1 on all accounts

A111 — ENCRYPTBYCERT or DECRYPTBYCERT used with certificates lacking expiry monitoring

• Trigger: sys.sql_modules WHERE definition LIKE '%ENCRYPTBYCERT%' OR '%DECRYPTBYCERT%' AND the referenced certificate has expiry_date < DATEADD(DAY, 90, GETDATE()) or is already expired
• Severity: Warning — ENCRYPTBYCERT and DECRYPTBYCERT succeed on expired certificates (SQL Server does not enforce expiry for T-SQL CLE operations); expired CLE certificates are silently missed by operators who assume TLS cert monitoring covers all certificates
• Fix: Add CLE cert expiry monitoring to the SQL Agent maintenance job: SELECT name, expiry_date FROM sys.certificates WHERE expiry_date < DATEADD(DAY, 90, GETDATE()) AND name NOT LIKE '##%'; rotate CLE certs before expiry per howto-key-rotation.md

A112 — Azure SQL Managed Instance: managed identity missing minimum AKV permissions for CMK

• Trigger: Azure SQL Managed Instance using BYOK TDE AND managed identity lacks wrapKey, unwrapKey, and get permissions on the target AKV key (inferred from error 33111 or "TDE Protector is not accessible" in ERRORLOG) — Azure SQL MI only
• Severity: Critical — missing wrapKey/unwrapKey/get permissions cause the TDE protector to become inaccessible; databases fail to start and failover groups cannot activate
• Fix: az keyvault set-policy --name [vault] --object-id [mi-object-id] --key-permissions wrapKey unwrapKey get list; for AKV with Azure RBAC: assign Key Vault Crypto Service Encryption User role to the managed identity

---

Output Format

Result Conventions

Apply one of four results to every check:

• PASS — attribute present, threshold not met (no issue)
• FIRE → Critical / Warning / Info — threshold met; use bold to distinguish from passes in the Check Evaluation Log
• NOT ASSESSED — required attribute absent from input (version-gated feature, missing DMV, or the construct is not applicable)
• SKIPPED — check triggered but insufficient data to evaluate (e.g., folder-based check when no directory provided)

Report Structure

Structure all output in this order:

1. Encryption Coverage Summary — one-paragraph narrative covering which encryption layers are active, which are absent, and the overall risk posture

2. Findings Table

   ID	Severity	Title	Evidence
   [A3]	CRITICAL	TDE certificate not backed up	No BACKUP CERTIFICATE in Agent history

3. Root-Cause Analysis — for each Critical or Warning finding, explain the risk in business terms (data exposure scenario, compliance implication, recovery impact)

4. Prioritised Fix Plan — ordered by: Critical → Warning → Info; within each tier, order by fix complexity (quick wins first); include T-SQL snippets for each fix

5. Compliance Gap Summary — separate paragraph mapping findings to PCI-DSS, HIPAA, GDPR requirements that are likely unmet (only if relevant context indicates regulated workloads)

6. Next Steps — 3–5 bullet points for the DBA team

Severity labels: CRITICAL, WARNING, INFO Output labels: [A1] through [A112]

File Output

Save two files to the current working directory:

output/sqlencryption-review/<YYYY-MM-DD-HHmmss>-<input-prefix>/analysis.md → full report output/sqlencryption-review/<YYYY-MM-DD-HHmmss>-<input-prefix>/trace.md → Check Evaluation Log

Derive <input-prefix> from the filename stem if a file path was provided, or from the first meaningful identifier in the artifact (database name, server name, etc.). Fallback: run. Sanitize: alphanumeric + hyphens/underscores only, max 32 chars.

File headers: analysis.md → # Analysis — sqlencryption-review / # Input: <first 80 chars> / # Generated: <UTC timestamp> trace.md → # Check Evaluation Log — sqlencryption-review / # Input: <first 80 chars> / # Generated: <UTC timestamp>

Create directories as needed. When --verbose is not present, write nothing to disk.

Analyzed by: sqlencryption-review (A1–A112)

---

Limitations

• When DMV data is absent (user provided natural language description only), mark version-gated and data-dependent checks as NOT ASSESSED and note what data would be needed for a complete assessment.
• Certificate backup history is not tracked by any DMV — A3, A23, A37, A44, A47 rely on SQL Agent job history or maintenance script evidence; if that evidence is not provided, state the assumption that no backup exists.
• Registry-based checks (A26 ForceEncryption via sys.dm_server_registry, TLS cipher suite checks A57–A58) require the SQL Server service account to have registry read permissions or the checks must be verified manually in SQL Server Configuration Manager.
• T-SQL source-level checks (A18, A19, A43) can only be evaluated when source code is provided or extractable from sys.sql_modules; source-only checks not applicable to DMV-only inputs.
• Azure-specific checks (A50, A51, A77–A80, A101, A112) are applicable only to Azure SQL Database, Azure SQL Managed Instance, or SQL Server on Azure VM; mark as NOT ASSESSED for on-premises instances.
• DMK password management checks (A81–A86) require access to master.sys.master_key_passwords; if the input does not include this view's output, assume non-SMK databases lack registration and flag accordingly.
• DDM checks (A87–A88) require SQL Server 2016+; sys.masked_columns does not exist on older versions.
• SQL Agent job step checks (A99) require access to msdb.dbo.sysjobsteps; plan cache checks (A100) require sys.dm_exec_sql_text via VIEW SERVER STATE.
• SQL Server 2022+ checks (A59 TLS 1.3, A73–A76 Ledger) self-skip on older versions where the features are absent.

---

Reference Files

Load references/check-explanations.md when:

• A check fires and the user asks "what does this mean?" or needs ranked fix options beyond the primary fix above
• You need DMV query examples or T-SQL code samples to verify a finding
• The user asks "explain check A5" or "how do I fix X"

The file is 2,100+ lines. Navigate with the Quick Reference Table at the top:

• A1–A8 — Transparent Data Encryption (TDE)
• A9–A16 — Always Encrypted
• A17–A21 — Cell-Level Encryption (CLE)
• A22–A25 — Backup Encryption
• A26–A30 — Transport and Connection Encryption
• A31–A38 — Certificate Management
• A39–A43 — Asymmetric and Symmetric Key Management
• A44–A48 — Key Hierarchy DMK and SMK
• A49–A52 — EKM and Azure Key Vault
• A53–A56 — Compliance and Coverage
• A57–A62 — TLS and Network Encryption Hardening
• A63–A67 — Always Encrypted Advanced
• A68–A72 — Operational Key Lifecycle
• A73–A76 — SQL Server Ledger
• A77–A80 — Azure-Specific Encryption
• A81–A86 — DMK Password Auto-Open
• A87–A91 — Dynamic Data Masking and Permission Patterns
• A92–A98 — Compliance Explicit Checks
• A99–A104 — Operational Validation
• A105–A112 — Advanced Cryptographic Patterns

Load references/concepts.md when:

• The user asks background questions: "what is TDE?", "explain symmetric vs asymmetric encryption", "what TLS version should I use?", "how does sp_control_dbmasterkey_password work?", "what is PBKDF1 vs PBKDF2?"
• A finding needs regulatory context: "what does PCI-DSS require for SQL Server encryption?", "does GDPR mandate key rotation?"
• The user needs encryption type comparison: "should I use TDE or Always Encrypted for this column?", "when should I use DDM vs encryption?"

Load references/howto-*.md guides when the user asks for step-by-step operational procedures:

• howto-tde-setup.md — end-to-end TDE deployment
• howto-always-encrypted.md — Always Encrypted setup and migration
• howto-tls-config.md — TLS 1.2/1.3 configuration and testing
• howto-key-rotation.md — certificate and key rotation procedures
• howto-crypto-shredding.md — cryptographic erasure for GDPR right-to-erasure
• howto-disaster-recovery.md — encrypted database restore across servers
• howto-dmk-password-management.md — sp_control_dbmasterkey_password, SSISDB DMK, cross-server restore, AG replica registration
• howto-dynamic-data-masking.md — DDM vs encryption decision tree, UNMASK permission, interaction with AE and CLE
• howto-agent-jobs.md — secure SQL Agent job patterns, credential protection, avoiding plaintext keys in job steps
• error-reference.md — common encryption error messages and resolution

---

Companion Skills

• /tsql-review — if T-SQL source code is provided, run alongside to catch OPEN SYMMETRIC KEY scope issues (A18), password-embedded key opens (A19), and CLE function usage patterns (A20, A21)
• /sqlplan-review — if execution plans are provided along with encryption artifacts, check for parameter sniffing on AE columns and implicit conversions caused by encrypted column type mismatches
• /sqlerrorlog-review — if ERRORLOG is available, check for TDE certificate errors, "Cannot find the server certificate" messages (A49), AG endpoint disconnection events related to cert expiry (A33), and TLS startup messages (A28, A59)
• /sqlhadr-review — if AG DMV data is provided, check for AG endpoint authentication failures that may be certificate-related (A33)
• /sqlspn-review — if Kerberos issues are suspected, check for SPN misconfiguration that may force NTLM fallback, weakening the encryption posture around transport authentication (complements A61)
• /sqlmigration-security-review — dispatched here for DMK/SMK key hierarchy migration detail (backup/restore sequencing, AG replica registration) and certificate-backed object portability during a SQL Server migration
• /mssql-performance-review — for mixed artifact bundles; routes encryption artifacts here automatically

---

Version Compatibility

Checks are self-skipping when the feature they test is absent from the artifact:

• A1–A8 (TDE): SQL Server 2008 and later; sys.dm_database_encryption_keys not present on SQL 2005 → checks not assessed
• A9–A16 (Always Encrypted): SQL Server 2016 and later; A12 (secure enclave) requires SQL 2019+
• A17–A21 (CLE): SQL Server 2005 and later
• A22–A25 (Backup encryption): SQL Server 2014 and later; backup set will have no key_algorithm column on older versions
• A26–A30 (Transport): SQL Server 2008 and later
• A31–A38 (Certificates): SQL Server 2005 and later
• A39–A43 (Key management): SQL Server 2005 and later
• A44–A48 (DMK/SMK): SQL Server 2005 and later; A48 requires linked server configuration
• A49–A52 (EKM/AKV): SQL Server 2008 and later for EKM; A50–A51 require Azure SQL or SQL Server on Azure VM
• A53–A56 (Compliance): A53 requires SQL Server 2019+ (sys.sensitivity_classifications); A54–A55 all versions; A56 requires SQL Server 2012+ (SQL Server Audit GA)
• A57–A62 (TLS/Network hardening): A57–A58 require registry access (all Windows versions); A59 requires SQL Server 2022+ (TLS 1.3); A60 all versions; A61 requires Kerberos (Active Directory environment); A62 all versions
• A63–A67 (Always Encrypted advanced): A63–A65 require SQL Server 2019+ with secure enclave; A66 requires SQL Server 2019+; A67 requires SQL Server 2019+
• A68–A72 (Operational key lifecycle): A68–A70 all versions; A71 requires SQL Server 2008+ (TDE); A72 requires SQL Server 2014+ (backup encryption)
• A73–A76 (SQL Ledger): All require SQL Server 2022+; Azure SQL Database ledger available in limited regions
• A77–A80 (Azure-specific): All require Azure SQL Database or SQL Server on Azure VM
• A81–A86 (DMK password): SQL Server 2005 and later; A82 (SSISDB) requires SQL 2012+
• A87–A91 (DDM/permissions): A87/A88 require SQL Server 2016+ (Dynamic Data Masking); A89–A91 all versions
• A92–A98 (Compliance explicit): A92–A93 all versions; A94 requires SQL 2022+ (Ledger); A95–A98 all versions
• A99–A104 (Operational validation): A99–A103 all versions; A101 Azure-only; A104 requires Always On AG
• A105–A112 (Advanced patterns): A105–A111 all versions; A112 Azure SQL Managed Instance only

Version	TDE	AE	CLE	Bkp	TLS	Certs	Keys	DMK	EKM	Compliance	TLS Harden	AE Adv	Key Ops	Ledger	Azure	DMK-PW	DDM	Compl+	Ops	Crypto+
SQL 2005	—	—	✓	—	✓	✓	✓	✓	—	A54–55	—	—	A68–70	—	—	A81,83–86	—	A92–93,95–98	A99–100,102–103	A105–111
SQL 2008	✓	—	✓	—	✓	✓	✓	✓	✓	A54–55	A57–58,60,62	—	A68–70	—	—	A81,83–86	—	A92–93,95–98	A99–100,102–103	A105–111
SQL 2012	✓	—	✓	—	✓	✓	✓	✓	✓	A54–56	A57–58,60,62	—	A68–70	—	—	A81–86	—	A92–93,95–98	A99–100,102–103	A105–111
SQL 2014	✓	—	✓	✓	✓	✓	✓	✓	✓	A54–56	A57–58,60,62	—	A68–72	—	—	A81–86	—	A92–93,95–98	A99–100,102–104	A105–111
SQL 2016	✓	✓	✓	✓	✓	✓	✓	✓	✓	A54–56	A57–58,60,62	—	A68–72	—	—	A81–86	A87–91	A92–93,95–98	A99–100,102–104	A105–111
SQL 2019	✓	✓+enc	✓	✓	✓	✓	✓	✓	✓	A53–56	A57–58,60,62	A63–67	A68–72	—	—	A81–86	A87–91	A92–93,95–98	A99–100,102–104	A105–111
SQL 2022	✓	✓+enc	✓	✓	✓+1.3	✓	✓	✓	✓	A53–56	A57–62	A63–67	A68–72	A73–76	—	A81–86	A87–91	A92–98	A99–100,102–104	A105–111
Azure SQL	✓	✓+enc	✓	✓	✓	✓	✓	✓	A50–51	A53–56	A57–58,60,62	A63–67	A68–72	A73–76	A77–80	A81–86	A87–91	A92–98,A101	A99–100,102–104	A105–111,A112