/sqlencryption-review

name: sqlencryption-review
description: Analyze SQL Server encryption posture across all layers — TDE, Always Encrypted, cell-level encryption, backup encryption, transport/TLS, certificate lifecycle, asymmetric and symmetric key management, DMK/SMK key hierarchy including sp_control_dbmasterkey_password and SSISDB, EKM/AKV, sensitivity-classification gaps, TLS hardening, AE enclave/driver, operational key lifecycle, SQL Ledger, Azure encryption, dynamic data masking patterns, and PCI-DSS/HIPAA/GDPR/FedRAMP/CMMC/NY-DFS compliance. Applies 112 checks (A1–A112) across 20 categories. Use this skill when reviewing database security posture, preparing for a compliance audit, investigating a key exposure, troubleshooting SSISDB or DMK auto-open failures, or whenever output from sys.dm_database_encryption_keys, sys.certificates, sys.symmetric_keys, sys.master_key_passwords, msdb.dbo.backupset, sys.dm_exec_connections, sys.ledger_*, sys.masked_columns, or sys.sensitivity_classifications is pasted. Trigger for questions about TDE setup, Always Encrypted configuration, backup encryption, TLS enforcement, certificate rotation, key rotation, sp_control_dbmasterkey_password, SSISDB encryption, SQL Ledger, dynamic data masking, HASHBYTES algorithm selection, or crypto-shredding for GDPR.
triggers:
  - /sqlencryption-review
  - /encryption-review
  - /tde-review
  - /always-encrypted-review
  - /backup-encryption-review
  - /tls-review
  - /ledger-review
  - /ssisdb-review
  - /data-masking-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.