Database Encryption: TDE and Connection Hardening

This document specifies how to encrypt the IdentityMesh database at rest (SQL Server Transparent Data Encryption) and in transit (TLS on the EF Core connection string). Both layers are configured on the SQL instance and in appsettings.json — IdentityMesh picks them up automatically without code changes.

TL;DR

• IdentityMesh stores identity data, audit, secret blobs, run history, and licence metadata in a single SQL Server database.
• SQL Server TDE encrypts data at rest — database files, the transaction log, and BACKUP DATABASE output — using a customer-controlled certificate. The IdentityMesh services never hold the key.
• TLS 1.2+ encrypts data in transit between the Admin API / Sync Engine / Relay Agent and SQL Server, controlled entirely through the connection string.
• Both layers are the customer’s responsibility to enable. The product uses Microsoft.Data.SqlClient, which honours the connection string verbatim — no driver overrides, no per-row encryption layered on top.

Threat model

Protects against:

• Backup tapes or .bak files copied off the server — the backup inherits TDE encryption; without the certificate it is ciphertext.
• Stolen physical disks, VM disk images, or detached mdf / ndf / ldf files.
• BACPACs and offline file-system copies on an attacker workstation.

Does NOT protect against:

• A logged-in SQL principal running SELECT * FROM IM_Secrets. TDE decrypts transparently for any authorised session; row-level access is governed by SQL grants and the IdentityMesh permission model, not TDE.
• In-flight data on the wire — that is TLS’s job (see Connection-string hardening below).
• Application-level secrets that need durability across hosts. IM_Secrets blobs are DPAPI-encrypted under the host’s LocalMachine key on top of TDE; a different host cannot decrypt them even with TDE in place. See secrets-and-dpapi.md and the key-vault runbooks for the application-secret story.
• A rogue DBA with CONTROL SERVER or sysadmin. They have legal access to read everything; TDE does not solve insider threat.

TDE is one layer in defence in depth. Pair it with least-privilege SQL grants, audited DBA access, and the IdentityMesh permission model.

Enabling SQL Server TDE

Performed on the SQL instance hosting the IdentityMesh database. Nothing changes on the IdentityMesh side.

1. Edition check

TDE is available on Enterprise / Developer / Evaluation editions of every supported SQL Server version, on Standard 2019 and later (TDE moved out of Enterprise-only at 2019), and on Azure SQL Database / Managed Instance (on by default — see Azure SQL note). Verify with:

SELECT SERVERPROPERTY('Edition'), SERVERPROPERTY('ProductVersion');

If the instance is Standard pre-2019 or Web/Express, plan an upgrade or move the database to a supported edition first.

2. Create the database master key

In master, create a master key. Pick a strong password and store it with the rest of your high-value secrets — it protects the certificate’s private key.

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong-password>';

3. Create the TDE certificate

USE master;
CREATE CERTIFICATE IdentityMeshTdeCert
    WITH SUBJECT = 'IdentityMesh TDE certificate',
         EXPIRY_DATE = '2031-12-31';

EXPIRY_DATE is metadata — SQL Server does not enforce it for TDE operations — but it gives your monitoring something concrete to alert on. Plan a rotation cadence shorter than the expiry.

4. Back up the certificate before enabling encryption

The single most important step in the procedure. Lose the certificate’s private key and the database is unrecoverable from any encrypted backup.

USE master;
BACKUP CERTIFICATE IdentityMeshTdeCert
    TO FILE = 'D:\Secure\IdentityMeshTdeCert.cer'
    WITH PRIVATE KEY (
        FILE = 'D:\Secure\IdentityMeshTdeCert.pvk',
        ENCRYPTION BY PASSWORD = '<different-strong-password>'
    );

Move both files to an offline secure location (HSM, sealed safe, key-vault export) and keep the password in a separate channel. There is no recovery story for a lost cert.

5. Create the database encryption key and turn on TDE

USE [IdentityMesh];
CREATE DATABASE ENCRYPTION KEY
    WITH ALGORITHM = AES_256
    ENCRYPTION BY SERVER CERTIFICATE IdentityMeshTdeCert;

ALTER DATABASE [IdentityMesh] SET ENCRYPTION ON;

Encryption proceeds online — background scanner threads encrypt pages while the database stays open. Expect elevated I/O for the duration; a 100 GB database typically completes in tens of minutes on reasonable storage.

6. Verify

SELECT db.name,
       dek.encryption_state,
       dek.percent_complete,
       dek.key_algorithm,
       dek.key_length
FROM   sys.dm_database_encryption_keys dek
JOIN   sys.databases db ON db.database_id = dek.database_id
WHERE  db.name = 'IdentityMesh';

Expected end state: encryption_state = 3 (encrypted), percent_complete = 0, key_algorithm = AES, key_length = 256. Anything else — 2 (in progress) lingering, 5 (key change), 6 (decryption in progress) — needs investigation before you call the rollout done.

Azure SQL note

For Azure SQL Database and Azure SQL Managed Instance, TDE is enabled by default with a service-managed key — no action is required to encrypt at rest. To take key custody, configure Customer-Managed Key (CMK) with an Azure Key Vault key: grant the logical server’s managed identity Get, WrapKey, and UnwrapKey on the vault, then enable CMK under Security → Transparent data encryption → Customer-managed key. Service-managed TDE meets many compliance regimes; CMK is the lever for “we hold the key, the cloud provider does not”.

Connection-string hardening (TLS in transit)

The Admin API, Sync Engine, and Relay Agent each connect to SQL through Microsoft.Data.SqlClient via EF Core. Every TLS knob is on the connection string — no IdentityMesh-side override.

Encrypt

Pick Strict if every SQL host is 2022+; otherwise Mandatory.

TrustServerCertificate

False (production) validates the server’s TLS certificate against the OS trusted-root store. True accepts any cert including self-signed — local dev only. Encrypt=Strict defaults this to False, but stating it explicitly survives later edits.

HostNameInCertificate

When the SQL server’s certificate Subject / SAN does not match the address the client connects to — common with Always-On AG listener names, custom DNS aliases, multi-tenant clusters — set this to the FQDN that does appear in the cert:

HostNameInCertificate=sql.example.local

Without it, the client rejects the cert with a name-mismatch error even though everything else is correct.

Sample hardened connection string

"ConnectionStrings": {
  "IdentityMesh": "Server=tcp:sql.example.local,1433;Database=IdentityMesh;Encrypt=Strict;TrustServerCertificate=False;HostNameInCertificate=sql.example.local;Authentication=Active Directory Integrated"
}

Authentication=Active Directory Integrated lets the service connect with Windows / managed-identity credentials — preferable to embedding a SQL login password in config. For SQL auth, use User ID=...;Password=... sourced from a secret manager. The same conventions apply to the Sync Engine’s appsettings.json and to anything else targeting the IdentityMesh database (BI tools, audit pollers).

Cipher suite expectations

The handshake is negotiated between SqlClient and SQL Server. The recommended floor:

Verify GCM availability on the SQL host with Get-TlsCipherSuite | Where-Object { $_.Name -match 'GCM' }. Force the server side: SQL Server Configuration Manager → SQL Server Network Configuration → Protocols → Properties → Flags → Force Encryption = Yes. Confirm legacy TLS is off by checking HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Server and the TLS 1.1 sibling for Enabled = 0.

Backup encryption layer

A BACKUP DATABASE of a TDE-encrypted database produces a .bak encrypted with the same DEK — unreadable without the certificate. This is the simple path: TDE transparently covers the backup output.

For belt-and-braces, BACKUP supports an explicit second layer:

BACKUP DATABASE [IdentityMesh]
    TO DISK = 'D:\Backups\IdentityMesh_full.bak'
    WITH FORMAT, INIT, COMPRESSION, CHECKSUM,
         ENCRYPTION (
             ALGORITHM = AES_256,
             SERVER CERTIFICATE = IdentityMeshBackupCert
         );

Trade-offs:

Most deployments use TDE alone. The dual-cert approach fits when backups are distributed to a separate trust domain (off-site DR vendor, compliance escrow). The backup-and-restore.md daily example is compatible with both — only the WITH ENCRYPTION clause differs.

Verification and monitoring

Weekly DEK health

SELECT db.name,
       dek.encryption_state,
       dek.encryptor_type,
       dek.create_date,
       dek.regenerate_date,
       dek.modify_date
FROM   sys.dm_database_encryption_keys dek
JOIN   sys.databases db ON db.database_id = dek.database_id
WHERE  db.name = 'IdentityMesh';

Alert on encryption_state <> 3 for the IdentityMesh database.

Certificate expiry monitor

SELECT name, subject, expiry_date,
       DATEDIFF(DAY, SYSUTCDATETIME(), expiry_date) AS DaysToExpiry
FROM   master.sys.certificates
WHERE  name = 'IdentityMeshTdeCert';

Alert when DaysToExpiry < 90. Default validity is one year when EXPIRY_DATE is omitted; a longer explicit date is fine as long as the rotation cadence is shorter.

Rotation procedure (high level)

1. Create a new certificate (IdentityMeshTdeCert_v2) and back up its private key offline before proceeding.

2. Rotate the DEK to encrypt under the new certificate:

   USE [IdentityMesh];
   ALTER DATABASE ENCRYPTION KEY
       ENCRYPTION BY SERVER CERTIFICATE IdentityMeshTdeCert_v2;

3. Wait for log backups taken under the old cert to age out of the retention window, then drop the old certificate.

The Microsoft SQL Server documentation is the authoritative reference for cert-rotation edge cases. Treat the steps above as the shape of the procedure, not the line-by-line script.

Post-DR restore checklist

When restoring an encrypted backup on a new SQL host, the cert must be present before RESTORE DATABASE runs — the .bak file only contains the cert thumbprint, not the cert itself.

1. Create a database master key in master on the new instance.

2. Restore the TDE certificate and its private key from the offline backup:

   USE master;
   CREATE CERTIFICATE IdentityMeshTdeCert
       FROM FILE = 'D:\Secure\IdentityMeshTdeCert.cer'
       WITH PRIVATE KEY (
           FILE = 'D:\Secure\IdentityMeshTdeCert.pvk',
           DECRYPTION BY PASSWORD = '<password-from-backup>'
       );

3. Restore the database and verify encryption_state = 3.

4. Continue with the application-side DR steps in backup-and-restore.md, including IM_Secrets re-provisioning (independent of TDE).

Customer responsibility split

Customer responsibilities:

• Provision SQL Server in a TDE-supporting edition; enable TDE on the IdentityMesh database.
• Create the database master key and TDE certificate; back up the certificate + private key offline before turning encryption on.
• Configure the connection string with Encrypt=Strict (or Mandatory) and TrustServerCertificate=False.
• Disable legacy TLS protocols on the SQL host.
• Monitor DEK state and certificate expiry; rotate on a defined cadence.
• Test cert + DB restore on a separate host as part of the annual DR drill.

Product behaviour:

• Connects via Microsoft.Data.SqlClient and honours every connection-string knob verbatim — no driver overrides.
• Does not impose its own per-row or per-column encryption on top of TDE; the backup story stays single-layer.
• Does not store the TDE certificate, the certificate password, or DEK material anywhere in application config or in SQL outside the standard system tables.
• Logs SQL connection / TLS handshake failures at Warning level via Serilog; persistent failures surface on /health/ready.

This split is the data-at-rest control boundary for SOC 2 and ISO 27001 audits.

Compliance mapping

Related

• deployment-architecture.md — where the SQL database fits in the overall topology.
• backup-and-restore.md — DR procedure; the cert restore step above slots in before the SQL restore.
• secrets-and-dpapi.md, secrets-keyvault.md, secrets-vault.md — application-level secret stores. TDE encrypts the database holding those blobs; the secret stores protect the values themselves.
• cluster-active-passive.md — the TDE certificate must be installed on every SQL host that may serve the database, including the passive node.