0

I would like to ask about encryption M.S SQL Server 2016. I have applied encryption on database production long time, but a few months later It show status not encrypted.

What happen to it?

Note

  1. Table sys.dm_database_encryption_keys" no database in this table
  2. Table sys.certificates" have certificate name
  3. Table sys.databases" have database name in this table

Thank you so much for your kindly help.

Srean
  • 9
  • 1
  • My first inclination is someone decrypted the database. It wouldn't be great if TDE turned itself off... – Ben Thul Aug 05 '20 at 16:12
  • Hi @BenThul How could we monitoring log, if someone decrypted db? May you show some advice for me. Note my db sql don't have audit specification. And as database security user have permission as (securityadmin role). Who else have any idea, please guild me. Thank you – Srean Aug 06 '20 at 01:35
  • I would take a look at extended events, specifically the `db_encryption_state_change` event. Depending on what you're keeping in the backuphistory table in msdb, you can also get a sense of when the encryption changed by looking at the encryptor_thumbprint column and correlating that with backup_start_time; the column will be null when the database is un-encrypted, not null when it is encrypted. – Ben Thul Aug 06 '20 at 02:25
  • Hi @BenThul I still cannot find backuphistory table or db_encryption_state_change event in database. Note I query all database default (msdb, master model, tempdb). Could you show me the this action. I try to test apply TDE and turn off and on back on my testing database, but it's still cannot find this event. Thank Regard, – Srean Aug 07 '20 at 02:54
  • Depending on how far back your backup history table goes, it would seem that the database was never encrypted. Perhaps the encryption scan aborted before it could complete. Either way, my suggestion at this point would be to move forward and (re-)enable TDE. Once that's done, verify that you can see evidence in backuphistory and then keep an eye on it. – Ben Thul Aug 11 '20 at 17:57
  • Thank you so much for your help, I will test to find the root cause. – Srean Oct 14 '20 at 00:59

0 Answers0