Sql-server – Turn TDE off when restoring SQL databases

sql servertransparent-data-encryption

We are just getting started with TDE. Eventually it will be in our Prod SQL Server environment and two major non-prod environments, but currently it is only in one non-prod system.

My question is about restoring these databases. We have several other environments (Enterprise and Standard Edition) and an offsite vendor that we sometimes send database backups to. For those, we will either have to use an intermediary server to turn TDE off before restoring, or back up with different keys.

But specifically, I am wondering how to restore from a TDE enabled prod environment to a TDE enabled non-prod environment. Once a TDE database is restored over without TDE being turned off, is it using the source server's keys going forward? I am wondering if turning TDE off and on (which would take hours) would be a requirement in this situation.
Also note the strange behavior when I restored below. It appeared that it is carrying a historical key through a previous incarnation of the database, and when copying to a server with the same database name, is allowing the second server's key. It this correct?

I did the following in our non-prod environment consisting of two database instances on different VMs:

On instance 1:

  1. Enabled TDE on the database DBA,
  2. Backed up the cert, key, and database,
  3. Restored the backup of DBA to a new database DBA2,
  4. Backed up the database DBA2,
    On instance 2:
  5. Copied the DBA2 backup from instance 1,
  6. Restored DBA2.

Note that I did not create any certs or keys for DBA2, not did I copy any DBA certs and keys from instance 1 to 2.

Instance 2 has its own unrelated DBA database with TDE certs and keys. TDE is not enabled on DBA2 after restore.

So it seems like:

  1. the instance 1 DBA key got carried over into DBA2 in step 3, and
  2. the restore may have used the DBA key from instance 2 in step 6, even though instance 1's DBA and instance2's DBA are separate entities.

Here is the encryption status on these databases:

    SELECT db_name(database_id), encryption_state,   percent_complete, key_algorithm, key_length
    FROM sys.dm_database_encryption_keys
where db_name(database_id) like 'DBA%'

Instance 1:

dbname  encryption_state    percent_complete    key_algorithm   key_length
DBA     3                   0                   AES             256
DBA2    3                   0                   AES             256

Instance 2:

dbname  encryption_state    percent_complete    key_algorithm   key_length
DBA     3                   0                   AES             256

Best Answer

But specifically, I am wondering how to restore from a TDE enabled prod environment to a TDE enabled non-prod environment.

There is an entire Docs article on this. Let us know if you have specific questions.

Once a TDE database is restored over without TDE being turned off, is it using the source server's keys going forward?

You'll see, in the article above, that yes you won't be able to read the database unless the server certificate which is protecting the database encryption key (DKE) which resides in that database has been restored and available on the destination server.

I am wondering if turning TDE off and on (which would take hours) would be a requirement in this situation.

If the customer can't use TDE, then it's a requirement (Non-Enterprise Editions). If you don't want to send them your internal certificate, then once restored to the destination server, rotate the server certificate to a new one. It won't require any extra operations (apart from metadata changes) on the database. you could then send the customer the cert + the database, in two separate communications channels or a secure channel, of course.

Also note the strange behavior when I restored below. It appeared that it is carrying a historical key through a previous incarnation of the database, and when copying to a server with the same database name, is allowing the second server's key.

Huh? Your output doesn't show anything but the encryption state of the database. You quite literally can't restore the database without the server certificate. So if it let you restore the database without any issue and you didn't restore a server certificate for it, then the logical conclusion is that the current server certificate is the same on the source and destination server, which is a security no-no.

Related Question