[SOLVED] Check if my database instance on SQL server is encrypted by TDE?

Check if my database instance on SQL server is encrypted by TDE?

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?

Solution

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: https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-database-encryption-keys-transact-sql?view=sql-server-ver15