TDE Status Report

Overview

Transparent Data Encryption protects a database at rest. The data files, the log file and every backup taken of that database are encrypted, and none of them can be read on another server without the key.

That last clause is the whole report. A database encryption key is protected by a certificate in master, and if the private key of that certificate has never been exported, the encrypted database cannot be restored or attached anywhere else. Not on a rebuilt server, not on a DR box, not by you. It is a backup that does not restore, and nothing about the daily job says so.

So the subject of this report is the certificate rather than the database.

Nothing on this page changes anything. Every read is a catalog view or a DMV.

There is no auto refresh. Encryption state changes when somebody changes it, and a scan that is running is the one case where the Refresh button is worth pressing.

Where to find it

The page title reads TDE Status for <server name>.

Requirements

• VIEW SERVER STATE, for sys.dm_database_encryption_keys.
• VIEW DEFINITION on the certificates in master, or the certificate columns come back empty. When that happens the page says not visible to this login rather than NEVER. Those are opposite findings and the difference is a permission, so the report never guesses between them.
• SQL Server 2008 or newer. Before SQL Server 2019, TDE also needed Enterprise, Developer or Evaluation edition; on an older Standard instance the page says so and stops, because there the answer to every question on it is the same one.
• The query has a 60 second timeout and reads no user data.

The states a database can be in

Not encrypted is not a failure. It is a decision, and it is drawn in grey rather than red. The red on this page is reserved for the one thing that is unambiguously wrong: encrypted data behind a key nobody has a copy of.

Reading the three views

Protection

The enclosure map, and the reason this page has a chart at all.

A certificate is drawn as a circle. The databases it protects are packed inside it, sized by data file size. Lose the key at the middle of that circle and everything inside it is gone, so blast radius stops being a sentence in a column and becomes an area.

Two encodings sit on top of each other, deliberately:

• A database circle’s fill is that database’s own encryption state. Green encrypted, blue mid scan, amber key-present-but-off, grey not encrypted.
• The ring around a group and the wash inside it are the state of the key. Red means the private key has never been exported.

That is why the common bad instance draws as green circles on a red field. A grid cannot put those two facts in the same place; it has a column for each and eight rows between them.

Three things are drawn outside any certificate, each in its own dashed region:

• Not encrypted, the databases with no key at all.
• Not online, kept separate because the instance genuinely cannot say whether they are encrypted and folding them in would make the region disagree with the tile above it.
• tempdb, dashed, because SQL Server encrypts it on its own account the moment any user database is encrypted. It is protected by an internal key with an empty thumbprint, not by any certificate anybody owns, and there is nothing about it for anybody to back up.

master, model and msdb are left out of the map entirely. They cannot be given an encryption key, so drawing them would be reporting a gap that cannot be closed. They are in the grid, where the Status column says Cannot be encrypted.

A database more than about a hundred and fifty times smaller than the largest one in its group is drawn at a legibility floor rather than at a radius of under a pixel. The legend says the area is data size and the grid carries the exact figures.

Key clock

Two facts about a certificate are dates, and a date cannot be read off an area.

A certificate that has never been backed up is drawn as a hatched bar running off the left edge with a chevron, rather than as a blank cell, because never is not a small number, it is a different kind of answer.

The shape shows the trap: the certificate nobody can restore without usually has the longest runway, which is exactly why nobody has looked at it.

Three honesty notes ride under this view rather than in this page, because a chart that quietly drew a zero would be lying:

• A certificate’s private key does not change, so a four year old export is still a valid export. What matters is whether it was ever taken and whether the file still exists somewhere, and the second of those is not a question any DMV can answer. An old date is not coloured red.
• No DMV publishes a backup date for the service master key or a database master key, so neither is drawn.
• An expired certificate does not stop TDE working. The expiry matters when the key is rotated, and it is reported rather than alarmed about.

Coverage

The number you put in the email.

One bar for the whole user estate split by state, then every database ranked with the unprotected ones first. System databases sort last whatever their size, because a six gigabyte master is not the finding on a page about user data.

The four numbers across the top

Ordered by how bad they are, not by how the query returns them.

The grid

Sorted worst first: unprotected user data, then data behind a key nobody can restore, then scans, then everything settled, then the databases the instance cannot open, then system databases.

What changed in version 4

This report used to be a grid with no chart, and the query behind it had five faults worth naming, because the numbers on the old page were not merely plain, they were wrong:

• Every unencrypted database read Unknown, in red. A database with no TDE has no row in sys.dm_database_encryption_keys at all, so the outer join left encryption_state NULL, no WHEN in the CASE matched NULL, and every one of them fell through to ELSE. On an instance not using TDE, that was the entire page. The classification is done on the join now, so no key, key with encryption off, not online and cannot be encrypted are four different answers.
• Only sys.certificates was joined. An asymmetric key encryptor, which is what EKM, Azure Key Vault and tempdb’s own internal key use, produced blank certificate columns with nothing on the page to say why.
• NEVER rendered as an empty cell, indistinguishable from a permissions gap.
• percent_complete was cast to varchar, so the column sorted 100 before 2 and printed a number for databases where the value means nothing.
• No ORDER BY and no size. Rows arrived in whatever order the instance returned them, and “three databases are not encrypted” never said whether that was two gigabytes or four terabytes.

Common questions

A database is encrypted and the report is showing red. Why? Because the certificate protecting it has never had its private key exported. The database is encrypted correctly; it just cannot be restored anywhere but this instance.

How do I fix that?

USE master;
BACKUP CERTIFICATE YourTdeCertificate
    TO FILE = 'D:\SecureBackups\YourTdeCertificate.cer'
    WITH PRIVATE KEY (FILE = 'D:\SecureBackups\YourTdeCertificate.pvk',
                      ENCRYPTION BY PASSWORD = 'a password you keep somewhere else');

Then put both files, and the password, somewhere that is not this server. Refresh the page and the ring turns green.

The backup date is four years old. Is that a problem? Not on its own. A certificate’s private key does not change, so an old export is still a valid export. The question is whether the file still exists and whether anyone knows the password, and no DMV can answer either.

Why is tempdb listed when I never encrypted it? Because SQL Server encrypted it for you. As soon as any user database on the instance is encrypted, tempdb is too, since it holds spilled data from every database. It cannot be turned off independently and there is nothing about it to back up.

Why is my certificate expiry in the past, and why is nothing red? TDE does not check the expiry date of the certificate protecting a database encryption key. The database keeps working. The expiry matters when you rotate the key.

Why does the Private Key Backed Up column say not visible to this login? The login can read sys.dm_database_encryption_keys but not the certificate row in master. VIEW DEFINITION on the certificate fills in the backup date and the expiry.

Why are master, model and msdb missing from the chart? They cannot be encrypted. The engine refuses with Cannot encrypt a system database. They are in the grid with that as their status.

Can I tell from here whether my backups are encrypted? Indirectly. A backup of a TDE encrypted database is encrypted, because the pages are encrypted on disk. Backup level encryption, set with BACKUP ... WITH ENCRYPTION, is a separate feature and is not on this page.