6

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

migrated from stackoverflow.com Aug 20 at 18:18

This question came from our site for professional and enthusiast programmers.

  • I can get encryptor_thumbprint but because it is on SQL 2008 R2 (under ultra-extended paid support), encryptor_type isn't available. On the destination server, the DBA2 database is using the other cert created from the copied file as per another comment in one of the answers below: (No column name) encryption_state percent_complete key_algorithm key_length encryptor_thumbprint DBA 3 0 AES 256 0x52... DBA2 3 0 AES 256 0x87... – BrianC Aug 20 at 22:35
7

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.

  • Not sure having the same cert in multiple environments is a "security no-no" as much as it is a security consideration. In our environment that simply means our QA environment has to be treated with prod-level security guidelines. – BradC Aug 20 at 20:16
  • Although the article has valid points, it seems to reference moving the database by detach / attach. That isn't something we'd be doing in our Prod environment. I was asking more in terms of refreshing environments from Prod. And since in my test the two servers were from the same environment, the cert and key setup were exactly the same. That probably explains why the DBA / DBA2 database in instance 1 and 2 seemed to be able to use the same key and cert. – BrianC Aug 20 at 20:20
  • @BrianC Yeah, there is zero reason to do detach/attach, backup and restore will work fine. But the cert/key backup/restore code in that article looks accurate. That must be why your restore worked fine; the cert had already been restored to the other environment. – BradC Aug 20 at 20:30
  • @BradC No, it's a security no-no. Treating QA like prod and it being prod are two different things. – Sean Gallardy Aug 20 at 20:42
  • @BrianC It doesn't matter if it's a backup/restore, attach/detach, log shipping, mirroring, availaiblity group, the certificate needs to be there. Again, it's the same cert so nothing magically went with it, as I've stated. – Sean Gallardy Aug 20 at 20:44
3

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).

  • The DBA2 database is likely carrying forward the cert from DBA on instance 1, and when the restore happenned it probably succeeded since instance 1 and 2 are part of the same 'environment', and thus use identical cert configuration. That's my best theory anyway. Running the TDE cert query shows one single cert in instance 1 (as we will likely not be able to share certs by application all the way up the environment hierarchy. Instance 2 has its own cert from the initial setup plus a second cert I created for the purposes of the restore from instance 1. – BrianC Aug 20 at 20:30
  • @BrianC So, you did create a cert prior to restoring the encrypted db? From scratch? Or via restoring the cert/key pair exported from the other instance? – BradC Aug 20 at 20:32
  • Correct, but I don't know if I did it right. On the destination server I created a cert just for a restore: name issuer_name subject thumbprint TDECert TDE Certificate TDE Certificate 0x52.... TDECertRestore TDE Certificate TDE Certificate 0x87... – BrianC Aug 20 at 20:37
  • According to my documentation, this was the cert creation code: It was created to restore a specific TDETest database as part the initial test: CREATE CERTIFICATE TDECertRestore_[database] FROM FILE = '[some path]\certificate_TDETest_Certificate.cer' WITH PRIVATE KEY (FILE = '[some path]\certificate_TDETest_Key.pvk', DECRYPTION BY PASSWORD = '[source cert password]') – BrianC Aug 20 at 20:44
  • @BrianC Ther server cert is not contained in the user database but in the master database so there is literally no way a user database is carrying it around to each instance to make it automagically work. – Sean Gallardy Aug 20 at 20:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.