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:
SELECTdb.name,db.is_encrypted,dm.encryption_state,dm.percent_complete,dm.key_algorithm,dm.key_lengthFROMsys.databases dbLEFT OUTER JOIN sys.dm_database_encryption_keys dmON db.database_id = dm.database_id;GOMy database instance has value 0 for is_encrypted, and all other values null.
Does it mean my database instance is not encrypted at all?
Best 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:https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-database-encryption-keys-transact-sql?view=sql-server-ver15