[SOLVED] SQL Server TDE - use T-SQL to show if there is a database key

SQL Server TDE - use T-SQL to show if there is a database key

I can tell if a SQL Server TDE Master Key exists with this T-SQL query:

if not exists (SELECT name FROM sys.symmetric_keys WHERE symmetric_key_id = 101) begin
    -- Master Key does not exist. Create one here.
end;

Is there a query to determine whether a database key exists for [mydb]? For example:

use [mydb];
if .... begin
    -- database encryption key does not exist. Create one here
end;

Solution

You're looking for sys.dm_database_encryption_keys. From the docs:

Returns information about the encryption state of a database and its associated database encryption keys.

Also, it's a server-level view, so no need to run it in the context of your user database (though it's fine if you do; you can run it anywhere on the server). Something like this should work

declare @db sysname = 'mydb';
if exists (
    select *
    from sys.dm_database_encryption_keys
    where database_id = db_id(@db)
       and encryption_state = 3 /* encrypted */
)
begin
   print concat(quotename(@db), ' is encrypted.')
end
else
begin
   print concat(quotename(@db), ' is not encrypted.')
end