I have a question about SQL server's transparent encryption (TDE). I need to dump a database instance, which will be restored by another DBA remotely by dumped data files. I was asked to make sure the dumped data files has no TDE so DBA can restore it. I checked online, and I found a query to list the encryption status as follows:
SELECT db_name(database_id), encryption_state
FROM sys.dm_database_encryption_keys;
my database instance is not in the result at all. I run another query as follows:
SELECT
db.name,
db.is_encrypted,
dm.encryption_state,
dm.percent_complete,
dm.key_algorithm,
dm.key_length
FROM
sys.databases db
LEFT OUTER JOIN sys.dm_database_encryption_keys dm
ON db.database_id = dm.database_id;
GO
My database instance has value 0 for is_encrypted, and all other values null.
Does it mean my database instance is not encrypted at all?
1 Answer
If your output looks like this...
name | is_encrypted | encryption_state | percent_complete | key_algorithm | ley_length
--------------------------------------------------------------------------------------------
MyDatabase | 0 | NULL | NULL | NULL | NULL
... your database, [MyDatabase], is NOT encrypted. Nor does it have a database encryption key configured.
If, however, any databases have non-NULLs in columns other than [is_encrypted] (e.g. [encryption_state] = 1), those databases are either encrypted, partially encrypted/decrypted or prepped for encryption.
Read up here for detail on encrpytion states:
