I have a question regarding TDE on SQL Server 2017 for example.
I created the master key
Created the certificate
Created the database encryption key ( now the database is encrypted)
Backed up the certificate
set encryption ON.
Thats great, I could backup up the database and restore it on another server.
Let's say i don't want TDE anymore.
Why when i set ENCRYPTION OFF it still tells me, when i try to restore it on another server (After a new backup of course, with encryption off, only with the "database encryption key", that the server coudnt find the certificate thumbprint?
I can remove certificate and master key and it fix the "Problem".
I would like to know whats the difference between:
USE DATABASE CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM =aes_256 ENCRYPTION BY SERVER CERTIFICATE MyCert
ALTER DATABASE DATABASE SET ENCRYPTION ON
TDE only encrypt the DATA FILE right? and not data, so someone can't steal my file and restore it on another server.