# Sqlencryption Review > 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… - **Type:** Skill - **Install:** `agentstack add skill-vanterx-mssql-performance-skills-sqlencryption-review` - **Verified:** Yes — security-reviewed for prompt injection and unsafe behavior - **Seller:** [vanterx](https://agentstack.voostack.com/s/vanterx) - **Installs:** 0 - **Category:** [Databases](https://agentstack.voostack.com/c/databases) - **Latest version:** 0.1.0 - **License:** MIT - **Upstream author:** [vanterx](https://github.com/vanterx) - **Source:** https://github.com/vanterx/mssql-performance-skills/tree/main/skills/sqlencryption-review ## Install ```sh agentstack add skill-vanterx-mssql-performance-skills-sqlencryption-review ``` Requires the [AgentStack CLI](https://agentstack.voostack.com/docs/cli). Works with Claude Code, Cursor, and any MCP-compatible agent. ## About # 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 ```sql -- 1. TDE status across all databases SELECT d.name AS database_name, d.is_encrypted, dek.encryption_state, dek.encryption_state_desc, dek.percent_complete, dek.encryptor_type, dek.key_algorithm, dek.key_length, c.name AS certificate_name, c.expiry_date AS cert_expiry, c.pvt_key_encryption_type_desc FROM sys.databases d LEFT JOIN sys.dm_database_encryption_keys dek ON d.database_id = dek.database_id LEFT JOIN master.sys.certificates c ON dek.encryptor_thumbprint = c.thumbprint ORDER BY d.name; -- 2. Always Encrypted column inventory SELECT SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name, c.name AS column_name, c.encryption_type, c.encryption_type_desc, c.encryption_algorithm_name, cek.name AS cek_name, cmk.name AS cmk_name, cmk.key_store_provider_name, cmk.key_path FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id JOIN sys.column_encryption_keys cek ON c.column_encryption_key_id = cek.column_encryption_key_id JOIN sys.column_master_keys cmk ON cek.column_master_key_id = cmk.column_master_key_id WHERE c.column_encryption_key_id IS NOT NULL; -- 3. CEK version history (rotation check) SELECT cek.name AS cek_name, cek.create_date, cekv.column_master_key_id, cmk.name AS cmk_name, cekv.create_date AS version_created FROM sys.column_encryption_keys cek JOIN sys.column_encryption_key_values cekv ON cek.column_encryption_key_id = cekv.column_encryption_key_id JOIN sys.column_master_keys cmk ON cekv.column_master_key_id = cmk.column_master_key_id; -- 4. Symmetric and asymmetric keys SELECT sk.name, sk.symmetric_key_id AS key_id, 'SYMMETRIC' AS key_type, sk.algorithm_desc, CAST(sk.key_length AS VARCHAR(10)) AS key_length, sk.create_date, sk.modify_date, ke.crypt_type_desc AS key_protection_type FROM sys.symmetric_keys sk OUTER APPLY ( SELECT TOP 1 crypt_type_desc FROM sys.key_encryptions WHERE key_id = sk.symmetric_key_id ORDER BY crypt_type ) ke WHERE sk.name NOT LIKE '##%' UNION ALL SELECT name, asymmetric_key_id, 'ASYMMETRIC', algorithm_desc, CAST(key_length AS VARCHAR(10)), create_date, modify_date, pvt_key_encryption_type_desc FROM sys.asymmetric_keys WHERE name NOT LIKE '##%'; -- 5. Certificates (all purposes) SELECT name, certificate_id, pvt_key_encryption_type_desc, issuer_name, subject, start_date, expiry_date, DATEDIFF(DAY, GETDATE(), expiry_date) AS days_until_expiry, NULL AS sig_algorithm -- CERTPROPERTY does not expose 'Algorithm'; verify via certutil or Get-ChildItem Cert:\ | Select SignatureAlgorithm FROM sys.certificates WHERE name NOT LIKE '##%' ORDER BY expiry_date; -- 6. Backup encryption history (last 30 days) SELECT TOP 30 database_name, backup_start_date, backup_finish_date, type AS backup_type, key_algorithm, encryptor_type, encryptor_thumbprint FROM msdb.dbo.backupset WHERE backup_start_date > DATEADD(DAY, -30, GETDATE()) ORDER BY backup_start_date DESC; -- 7. Connection encryption status SELECT encrypt_option, auth_scheme, COUNT(*) AS connection_count, SUM(CASE WHEN client_net_address NOT IN ('', '127.0.0.1', '::1') THEN 1 ELSE 0 END) AS remote_connections FROM sys.dm_exec_connections GROUP BY encrypt_option, auth_scheme; -- 8. Key hierarchy: DMK protection status SELECT d.name AS database_name, d.is_master_key_encrypted_by_server, sk.name AS dmk_name, sk.create_date, sk.modify_date FROM sys.databases d LEFT JOIN sys.symmetric_keys sk ON sk.name = N'##MS_DatabaseMasterKey##' WHERE d.database_id = DB_ID(); -- 9. EKM providers SELECT provider_id, name, dll_path, is_enabled, provider_version, sqlcrypt_version FROM sys.cryptographic_providers; -- 10. Sensitivity classifications SELECT SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name, c.name AS column_name, sc.information_type, sc.label, sc.rank_desc, c.column_encryption_key_id AS ae_key_id FROM sys.sensitivity_classifications sc JOIN sys.objects t ON sc.major_id = t.object_id JOIN sys.columns c ON sc.major_id = c.object_id AND sc.minor_id = c.column_id; -- 11. Endpoints using certificate authentication SELECT e.name AS endpoint_name, e.type_desc, e.connection_auth_desc, c.name AS certificate_name, c.expiry_date, DATEDIFF(DAY, GETDATE(), c.expiry_date) AS days_until_expiry FROM sys.endpoints e LEFT JOIN sys.certificates c ON e.certificate_id = c.certificate_id WHERE e.connection_auth_desc LIKE '%CERTIFICATE%'; ``` --- ## How to Run Walk A1–A80 in category order. Report every triggered finding — do not stop at the first match per category. For checks where the relevant DMV data is absent from the input, mark them as `NOT ASSESSED` rather than skipping silently. For checks where the data is partial (e.g., a description rather than DMV output), state your assumption explicitly before applying the check. When multiple databases or instances are present in the input, run all applicable checks per database/instance and aggregate findings. Prioritize production databases for Critical and Warning findings. For T-SQL source-level checks (A18, A19, A43), scan provided SQL modules in `sys.sql_modules` or pasted code; note any checks that could not be verified due to missing source code. --- ## Thresholds Reference | Metric | Info | Warning | Critical | |--------|------|---------|----------| | Certificate / key days until expiry | — | 365 days since last rotation | > 730 days | | CMK rotation age | — | > 730 days | — | | RSA asymmetric key length | — | 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 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 authenticat … ## Source & license This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code. - **Author:** [vanterx](https://github.com/vanterx) - **Source:** [vanterx/mssql-performance-skills](https://github.com/vanterx/mssql-performance-skills) - **License:** MIT Install and usage instructions live in the source repository linked above. ## Pricing - **Free** — Free ## Security capabilities Automated source analysis of v0.1.0 — what this tool can access: - **Network access:** no - **Filesystem access:** no - **Shell / process execution:** no - **Environment & secrets:** no - **Dynamic code execution:** no *"Yes" means the capability is present in the source — more access means more to trust, not that it is unsafe.* ## Versions - **0.1.0** — security scan: passed — Imported from the upstream source. ## Links - Listing page: https://agentstack.voostack.com/l/skill-vanterx-mssql-performance-skills-sqlencryption-review - Seller: https://agentstack.voostack.com/s/vanterx - Browse the marketplace: https://agentstack.voostack.com/browse --- Listed on AgentStack — the marketplace for AI agent skills and MCP servers. Every listing is security-reviewed. Creators keep 70%.