TDE enabled but database backups don't *appear* to be encrypted
-
I have a database that is using TDE and this can be confirmed through:
SELECT
CASE database_id
WHEN 2 THEN 'tempdb'
ELSE 'encryptedDB'
END AS database_name,
encryption_state,
key_algorithm,
key_length,
encryptor_thumbprint,
encryptor_type
FROM
sys.dm_database_encryption_keys
WHERE
encryption_state = 3
This returns:
However after a backup, checking the values in msdb.dbo.backupset suggests that the backup hasn't been encrypted:
select
bs.database_name,
bs.encryptor_thumbprint,
bs.encryptor_type
from
msdb.dbo.backupset bs
where
bs.type = 'D'
and
bs.database_name = 'encryptedDB'
go
This returns:Is this indicating that the backup hasn't been encrypted? This appears to contradict the Microsoft article regarding TDE (https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-2016):
If the backup is being encrypted, how can I prove this?Thanks.
-
I'll make a educated guess. The data in the backup is encrypted through TDE. The backup isn't showing that as that probably occurs when you encrypt the backup during the backup.
-
Lynn is correct. Those columns in msdb.dbo.backupset indicate if the backup itself was run with the Encryption option, not that the backup is of a database encrypted with TDE. So your data is still encrypted in the backup because the database itself is encrypted and the backup is just a copy of the pages that make up the database. Native Backup Encryption is a newer feature in SQL Server.(released with 2014).
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question - Pete Bishop - Thursday, May 24, 2018 2:51 PMI have a database that is using TDE and this can be confirmed through:
SELECT
CASE database_id
WHEN 2 THEN 'tempdb'
ELSE 'encryptedDB'
END AS database_name,
encryption_state,
key_algorithm,
key_length,
encryptor_thumbprint,
encryptor_type
FROM
sys.dm_database_encryption_keys
WHERE
encryption_state = 3
This returns:However after a backup, checking the values in msdb.dbo.backupset suggests that the backup hasn't been encrypted:
select
bs.database_name,
bs.encryptor_thumbprint,
bs.encryptor_type
from
msdb.dbo.backupset bs
where
bs.type = 'D'
and
bs.database_name = 'encryptedDB'
go
This returns:Is this indicating that the backup hasn't been encrypted? This appears to contradict the Microsoft article regarding TDE (https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-2016):
If the backup is being encrypted, how can I prove this?Thanks.
Try restoring it on a diff server which does not have TDE setup. You should get an error "cannot find certificate with thumbprint ...etc"
Alex S -
Alex. I had that thought but wasn’t sure if that would differentiate between the database being encrypted and the backup of the database being encrypted. Peter
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply