1

Below is my SQL Server details:

  • SQL Server 2014 Enterprise SP2 GDR with Availability Group configured.
  • List item

NOTE: The Databases are FileStream Enabled.

Below are the series of actions I did recently and need your help to understand and correct myself:

  1. Enabled TDE (Structured Data .mdf & .ldf)
  2. Enabled Encryption File System (EFS) for Unstructured data
  3. After both the configurations , now the Database is on TDE Enabled and EFS Enabled in AOAG .
  4. I took a backup of the Database (From what I have learnt post TDE & EFS the backup of the Database is encrypted as well using Database Encryption Key Algorith . Is this point correct ?)
    1. I removed the Database from AOAG. Later deleted the database from all AG Replicas and restored it using encrypted file taken above and joined them to AG again.

My Actual question is :

  1. Post taking a backup of the database by using Encrypted file above and restoring it on the same server , the database is now freed from both TDE & EFS , Can some explain me what happened here ?
  2. Why the Database is not encrypted when I used a encrypted Database Backup file !
  3. Does the backup file post restoration gives decrypted files automatically by default behavior ?

Current the Is_encrypted value for the database is now "1" earlier before retore it was "3".

How can I make sure (or) is there way at all when I restore a encrypted backup I can see the database is encrypted as well ? Is my thinking wrong , kindly share some insights on this point?

Improve this question

1 Answer 1

Reset to default
3

I believe there is more to the story than we're getting, here.

  1. I took a backup of the Database (From what I have learnt post TDE & EFS the backup of the Database is encrypted as well using Database Encryption Key Algorith . Is this point correct ?)

In this case, the database is still encrypted using TDE - however if you've changed the volume that the backup is on which isn't protected by EFS then EFS isn't in play. EFS is like TDE, but for the filesystem - so once you leave the protection boundary, it's no longer protected.

  1. Post taking a backup of the database by using Encrypted file above and restoring it on the same server , the database is now freed from both TDE & EFS , Can some explain me what happened here ?

I don't follow and there should be more to this that we're missing. Just restoring the database on the same server would require that the server certificate for TDE be available (which, since it's the same server it should be available) so the restore can take place. If you've chosen to MOVE the file location to a location not protected by EFS then EFS would no longer be in the picture. If you restored over the same database in the same location, then both TDE and EFS should bein effect. Regardless, though, TDE will still be in effect.

  1. Why the Database is not encrypted when I used a encrypted Database Backup file !

It is, so we're missing part of the story. If it's saying it isn't, then something else occurred after the RESTORE DATABASE command to disable TDE.

  1. Does the backup file post restoration gives decrypted files automatically by default behavior ?

No, that's why something else had to have happened. The database should still be TDE encrypted, whether or not EFS depends on the where the files were restored.

How can I make sure (or) is there way at all when I restore a encrypted backup I can see the database is encrypted as well ?

I'm not sure I follow, again, as a backup of a TDE database is encrypted. If you restore it, it's still encrypted. Period. If this happens each and every time just running RESTORE DATABASE and nothing else, then more data capture will be needed. If you're using a 3rd party tool, you'll want to ask what they do and their process or grab some tracing during the restore to look into what they are doing.

Improve this answer
3
  • Hi @Sean , Thank you for the responses. Please see I added point 5 above which can be the missing point you lookung for. Missed to mention it. The disk layout is same across all AG replicas. Commented Feb 28, 2019 at 15:19
  • @SQLBoy Adding and removing the database from an availability group doesn't change the TDE Encryption status, but thank you for adding more information. Commented Feb 28, 2019 at 15:47
  • Sincere Apology , I am new to TDE and after reading more , I got the understanding over the situation . I got confused between Is_Encrypted and Encryption_State . What I have now is Is_encrypted State 1 but in a confused state checked the Encryption_state to 1 which means Unencrypted. In actual encryption state is 3 and is_encrypted is 1 . Yes the DBs are enabled for encryption. Accepted all your points as answers . Thanks. Commented Feb 28, 2019 at 17:21

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.