Turn TDE off when restoring SQL databases

Question

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

Answer 1

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.

Answer 2

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

Just restore the backed up TDE certificate (using the key and password) to the non-prod SQL server, then you can restore encrypted databases back and forth between the two environments.

In our environment, we create a certificate for each application, not for each server. This also means that multiple databases on the same server might be using different certs, if they are part of different applications.

It also means that our QA and DEV environments for these applications have to be locked down as hard as our prod environment, from a security perspective. If your security policies don't allow that, then yes, you'll have to restore a copy of the database to an intermediate server (or restore a copy on prod) to remove TDE.

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

When you restore the certificate using the exported key and password, you are using the same certificate, but you are attaching it to the new server's masterkey, so it is using the original cert, but not the source server's "key", per se.

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?

No, that's not possible. Either someone removed TDE before restoring it to the second server, or the second instance has a previously restored copy of the same TDE certificate (by thumbprint, not by name).

Some useful queries:

SELECT name, issuer_name, subject, thumbprint
FROM master.sys.certificates
WHERE pvt_key_encryption_type = 'MK'

Lists all TDE certs on the instance (or, at least all the ones encrypted by the master key, which is the recommended technique). The "thumbprint" is the column that matters, if you want to compare your certs between servers.

SELECT d.name as dbname, d.is_encrypted,
       CASE k.encryption_state
           WHEN 0 THEN 'NoKey'
           WHEN 1 THEN 'Unencrypted'
           WHEN 2 THEN 'Encryping...'
           WHEN 3 THEN 'Encrypted'
           WHEN 4 THEN 'KeyChange...'
           WHEN 5 THEN 'Decrypting...'
           WHEN 6 THEN 'ProtectionChange...'
       END as state_desc,
       k.percent_complete, CONCAT(k.key_algorithm, '-', k.key_length) as key_type,
       c.name as certname
FROM sys.databases d
LEFT OUTER JOIN sys.dm_database_encryption_keys k ON d.database_id = k.database_id
LEFT OUTER JOIN sys.certificates c ON k.encryptor_thumbprint = c.thumbprint
ORDER BY k.encryption_state DESC, d.name;

Show all databases on the server, their encryption state, and the encryption key they are associated with (based on thumbprint, not name).