New-SqlTdeCertificate

Encrypting a database’s files on disk needs a certificate on the server to act as the key. This command creates that certificate. SQL Server keeps this kind of certificate in a system database called master, and it can only be created once a prior secret — the database master key — exists there.

If that master key is missing, this command stops and tells you, rather than creating a recovery-critical secret on its own. You can hand it a password to create the master key as part of the same call when that is what you want. Creating the certificate is only half the job: it has to be backed up with Backup-SqlTdeCertificate before it protects data you cannot afford to lose.

Creates a CERTIFICATE in master for TDE. A TDE certificate depends on a database master key (DMK) in master. If the DMK is present the certificate is created; if it is absent and no -MasterKeyPassword is supplied, the command returns Pending with guidance rather than creating the DMK for you. Supplying -MasterKeyPassword (a SecureString) opts into creating the DMK first. Idempotent — an existing certificate of that name yields Skipped. Every state change runs under ShouldProcess, so -WhatIf previews and changes nothing. Never throws; it returns a SqlCert.Result on every path. -MasterKeyPassword is decrypted only to build the CREATE MASTER KEY statement and is never logged, echoed, or placed in the result.

Recipe 01 — Create the certificate when the master key already exists

New-SqlTdeCertificate -SqlInstance sql01 -Name 'TDE_Cert_2026' -Subject 'TDE cert for sql01'

Recipe 02 — Create the database master key and the certificate in one call

$pw = Read-Host -AsSecureString 'DMK password'
New-SqlTdeCertificate -SqlInstance sql01 -Name 'TDE_Cert_2026' -Subject 'TDE' -MasterKeyPassword $pw

Use this on an instance that has neither a master key nor the certificate yet. Data.MasterKeyCreated reports whether the master key was created.

Recipe 03 — Preview without changing anything

New-SqlTdeCertificate -SqlInstance sql01 -Name 'TDE_Cert_2026' -Subject 'TDE cert for sql01' -WhatIf

Watchpoint — a missing master key returns Pending, not Failed. Without a database master key and without -MasterKeyPassword, the result is Pending with instructions to create the master key (or re-run with -MasterKeyPassword). That is a prompt to act, not an error. After the certificate is created, back it up with Backup-SqlTdeCertificate before it protects any database.

Transparent Data Encryption scrambles a database’s files on disk so a stolen data file or backup cannot be read without the certificate that unlocks it. That certificate lives in the master system database, and SQL Server will only let you create it once a database master key exists there — the master key is what protects the certificate.

This command handles that ordering for you. If the master key is already there, it creates the certificate. If the master key is missing, it will not quietly create one, because a master key is itself a secret you have to be able to recover — it tells you to create the master key first, or you can pass a password so it creates the master key and then the certificate together.

• The database master key is a precondition the command makes explicit — a missing master key returns Pending with guidance, so a recovery-critical secret is never created behind the operator’s back.
• -MasterKeyPassword is a SecureString, decrypted only to build the statement and never logged, echoed, or returned.
• The result reminds the operator to escrow the certificate with Backup-SqlTdeCertificate before it protects data, closing the gap that would otherwise leave an encrypted database unrecoverable.
• Idempotent and -WhatIf-previewable, so a re-run or a dry run changes nothing.