Domain: Transparent Data Encryption · Risk: Changes state · Runs on: Windows, PowerShell 5.1 or 7

Audience Lens

Manager Practitioner Learner

Over time you replace the certificate that protects your encrypted databases. This command does that without taking the databases offline. It creates the new certificate, makes a safe copy of it, and then switches each encrypted database over to it.

The safety feature is the order it insists on: it will not switch any database to the new certificate until it has confirmed the new certificate was backed up first. So a rotation can never leave a database protected by a certificate that has no recoverable copy. It keeps the old certificate in place afterward — dropping it is a separate decision you make once you have confirmed the new one is working. The result reports each database’s outcome and does not claim success while any database has not moved.

Runs the rotation as a sequence: preflight (Test-SqlTdeConfiguration), create the new certificate (New-SqlTdeCertificate), escrow it (Backup-SqlTdeCertificate), and only if the escrow succeeded, re-encrypt each TDE database’s key with ALTER DATABASE ENCRYPTION KEY ENCRYPTION BY SERVER CERTIFICATE and verify each against the new certificate’s thumbprint. If the escrow does not succeed it stops before re-encrypting anything — no key has moved at that point, so the stop is safe. The previous certificate is retained (dropping it is a separate act). Per-database outcomes are aggregated into one result; if any database did not move, the result is Failed. An instance with no encrypted database yields Skipped. -Password protects the new escrow; optional -MasterKeyPassword is threaded to New-SqlTdeCertificate to create the master key if one is absent. Both are SecureString and are never logged, echoed, or returned. Runs under ShouldProcess (-WhatIf previews). Never throws; returns a SqlCert.Result. Data keys: NewCertificateName, NewThumbprint, Databases (Database / Reencrypted / Detail).

Recipe 01 — Rotate every encrypted database to a new certificate

$pw = Read-Host -AsSecureString 'Backup password'
Invoke-SqlTdeCertificateRotation -SqlInstance sql01 -Name 'TDE_Cert_2027' -Subject 'TDE 2027' `
    -CertificatePath 'E:\escrow\tde2027.cer' -PrivateKeyPath 'E:\escrow\tde2027.pvk' -Password $pw

Creates and escrows the new certificate, re-encrypts every TDE database’s key to it, verifies each, and keeps the old certificate in place.

Recipe 02 — Preview the rotation without changing anything

$pw = Read-Host -AsSecureString 'Backup password'
Invoke-SqlTdeCertificateRotation -SqlInstance sql01 -Name 'TDE_Cert_2027' -Subject 'TDE 2027' `
    -CertificatePath 'E:\escrow\tde2027.cer' -PrivateKeyPath 'E:\escrow\tde2027.pvk' -Password $pw -WhatIf

Reports which databases would be re-encrypted to the new certificate, without creating, escrowing, or changing anything.

Watchpoint — a failed escrow stops the rotation before any database moves. If the new certificate cannot be escrowed, the command returns Failed and re-points no database key, so nothing has changed. The old certificate is always retained; drop it separately once you have confirmed the new one. If some databases move and others do not, the result is Failed and names the databases that did not move — re-run to finish.

Rotating a certificate means bringing in a new one and moving every encrypted database over to it, while the databases stay online. The risk is obvious: if the new certificate is lost before it has been backed up, every database now depending on it becomes unrecoverable.

This command removes that risk by fixing the order. It creates the new certificate, backs it up, and checks that the backup succeeded before it moves a single database. If the backup fails, it stops there, having changed nothing. It also leaves the old certificate in place, so the databases still have a certificate they can fall back to until you have confirmed the new one and chosen to drop the old.

Software Approval Dependencies Compliance

Question	Answer
Outbound calls?	It connects only to the SQL instance you name, through the optional dbatools or SqlServer provider, and writes the new escrow files at the paths you supply on the SQL Server’s file system. No other outbound call.
What it changes	Creates and backs up a new certificate, then re-points each encrypted database’s key to it. The old certificate is kept. Only if `-MasterKeyPassword` is supplied and no master key exists, it also creates the database master key.
Privileges	A login able to create and back up certificates in `master` and to alter the encryption key on each encrypted database; the SQL service account must be able to write the escrow paths.
Reversibility	The old certificate is retained, so databases can be re-encrypted back to it. The new certificate can be dropped once nothing depends on it.

Dependency	Why	Required?
Windows + PowerShell 5.1 or 7	Runs the command.	Yes
dbatools or SqlServer module	Reaches the SQL instance to run the T-SQL.	Yes (either one)
At least one TDE-encrypted database	Nothing to rotate otherwise; the result is `Skipped`.	Yes (to do work)
A database master key in `master`	The new certificate is created under it.	Yes, unless `-MasterKeyPassword` creates it
A writable path for the new escrow files	The new certificate and private key are written there.	Yes

The rotation refuses to re-encrypt any database key unless the new certificate’s escrow succeeded, so it cannot leave a database protected by a certificate that has no recoverable backup.
Each re-encryption is verified against the new certificate’s thumbprint, and the result never reports success while any database has not moved — a partial rotation returns Failed and names the databases still to move.
The old certificate is retained by default; dropping it is a separate, deliberate act.
Passwords are SecureString and never logged, echoed, or returned; the run supports -WhatIf for a no-change preview.