master Keys and Certificates

Overview

The TDE Status page answers which databases are encrypted. This one answers the question that matters on the day somebody restores one of them onto another server: does the key exist anywhere except inside this master.

The chain:

  1. A database encryption key is protected by a certificate that lives in master.
  2. That certificate is protected by master’s database master key.
  3. That is protected by the service master key.
  4. That is protected by the machine.

Restoring the database without the certificate produces a file nobody can open, and the column that says whether anybody ever exported it is pvt_key_last_backup_date, which is null far more often than it should be.

The master Keys and Certificates page: what master is protecting and what has never been exported
The whole page. Seven certificates and the service master key, with the header line answering in one sentence whether any database on this instance is encrypted at all.

Where to find it

Expand a server in the tree, expand the master database, then Master → Keys and Certificates.

Shown on SQL Server 2008 and newer.

Expiry is a warning and never an outage

An expired certificate still decrypts what it encrypted.

A page that painted it red would be teaching its reader to ignore the color, so expiry is a note rather than an alarm.

Version gates

sys.dm_database_encryption_keys and sys.cryptographic_providers arrived in SQL Server 2008, and pvt_key_last_backup_date arrived later than the rest of sys.certificates.

So the column list is built from the instance version rather than written out once. Naming a column that a build does not have kills the whole batch at compile time and no TRY around the query catches it.

Reading the chart

The bars: one per key or certificate, the ones protecting a database and never exported first
Nothing here is protecting a database, so the bars fall back to age and the label on the right is how old each one is. On an instance with TDE, the certificate behind it sorts to the top.

One bar per certificate or key. The order puts the finding first: a certificate protecting a database encryption key with no export date, then everything else.

Read the header line before the bars. It says how many certificates master holds and whether any database on the instance is encrypted, and on most instances that second half is the answer.

Reading the grid

The grid: the certificates and keys master holds
Every row on this instance is one of the engine’s own ##MS_ certificates plus the service master key. The columns that decide whether a restore elsewhere is possible, Last exported and Protects, are further to the right.
Column What it is
Name The certificate, symmetric key or asymmetric key.
Kind Which of the three, and whether it is the database master key or the service master key.
Algorithm or subject The key algorithm, or the certificate subject.
Valid The validity dates. Expiry is noted, not alarmed.
Last exported pvt_key_last_backup_date. Blank is the finding this page exists for.
Protects Which database encryption keys this certificate protects.
Protected by What protects it: the database master key, a password, or the service master key.
Thumbprint The thumbprint, which is how a certificate on another server is matched to this one.

The toolbar

Control What it does
TDE status Opens the TDE Status report.
Credentials Opens master Credentials.
Copy the backup statements Copies BACKUP CERTIFICATE and BACKUP MASTER KEY statements for the rows with no export date, without running them.

Where the data comes from

Source What it gives
sys.certificates The certificates, their subject, validity and pvt_key_last_backup_date.
sys.symmetric_keys The symmetric keys, including the database master key.
sys.asymmetric_keys The asymmetric keys.
sys.key_encryptions What protects what.
sys.dm_database_encryption_keys Which databases each certificate protects.
sys.cryptographic_providers EKM providers, where one is in use.
Report Why you would go there
TDE Status Which databases are encrypted, and their encryption state.
master Credentials The EKM credential behind a provider.
master Backup and Rebuild Readiness Whether these certificates are in a backup of master.
Restore History Where a database that arrived from another server came from.
Security Posture The wider picture.

Frequently asked questions

A TDE certificate has never been exported. What happens? If this server is lost, the encrypted databases are unrecoverable. Backing up the certificate and its private key, and storing them somewhere other than this server, is the fix. The toolbar copies the statements.

The certificate expired last year and everything still works. It will. An expired certificate still decrypts what it encrypted. It cannot be used to encrypt something new, which for a TDE certificate is rarely what anybody is doing.

I restored a TDE database and cannot open it. The certificate did not come with it. It has to be created on the destination instance from the exported certificate and private key, with the same thumbprint, before the restore.

What is the service master key? The root of the chain, protected by the machine and by the SQL Server service account. It is also worth backing up, and it is on this page for that reason.