Since TDE relies on a certificate stored in master (which is used to encrypt the database encryption key), then this would work only work if you could restore the master database to another server in such a way that the certificate could be decrypted.
This is the TDE encryption hierarchy:
- Service master key (protected by Windows; tied to the service account credentials, and a machine key)
- Database master key (in this case, the one for the master database)
- Certificate
- TDE encryption key
The first three items are stored in the master database, and can all be backed up. The fourth is stored (encrypted by the certificate from #3) in the header of the encrypted database.
So in a failure scenario, you would have to restore enough of the encryption hierarchy to allow you to read the TDE key. SQL Server creates the service master key at installation; thus while restoring the master database to a different instance will also restore items 2 and 3, the necessary key(s) to decrypt them will not be present. Result: unreadable data.
The two best options are to either restore the certificate (#3) from a backup (a good option if master cannot be restored for whatever reason), or restore your master database and its master key (#2) from a backup. Restoring the master key may be a better option if you have a lot of certificates/keys protected by this key, and need to make them all accessible at once. This comes with the same precautions normally associated with restoring the master database (collations, logins, database names and file paths, etc.)
Generally, I'd only recommend restoring master in a recovery scenario. For a migration/scale-out scenario (such as using Availability Groups/mirroring with a TDE-encrypted database), it's better to backup/restore the certificate (#3) so that it's encrypted using the master keys unique to each instance it's moving to. You will need to include the private key with the certificate backup.
In any case, you're going to have to make key/certificate backups, so guard them well, and store them in redundant, secure locations. Simply having a backup of master will not get you out of a TDE disaster; you're going to need a backup of at least one key or certificate.
Restoring the database to another server will not result in the data being unencrypted. However, you do need to:
...move the certificate or asymmetric key that is used to open the DEK.
The certificate or asymmetric key must be installed in the master database of the destination server, so that SQL Server can access the database files.
You must retain copies of both the certificate file and the private
key file in order to recover the certificate. The password for the
private key does not have to be the same as the database master key
password.
The following link provides you with the details:
https://msdn.microsoft.com/en-us/library/ff773063(v=sql.120).aspx
Once you have everything set up on the restored database, you can (of course) turn off TDE and let the server eventually de-encrypt the database, if that is your intention.
There is a Very Important Note on removing encryption from a database that is to be restored from SQL Server 2008 or 2008 R2 to another server at:
http://blogs.msdn.com/b/batuhanyildiz/archive/2012/10/16/how-to-enable-remove-transparent-data-encryption-tde.aspx
(This may not matter to you, depending on the source of the restored database.)
Best Answer
Transparent Data Encryption (TDE) can be used to perform real-time I/O encryption and decryption on instance data files. To increase data security, you can enable TDE to encrypt instance data. Data is encrypted before it is written to disk and decrypted when it is read from disk. TDE does not:
Increase the size of the data file. Require developers to make any code changes to use TDE. Alibaba Cloud ApsaraDB for RDS fully supports TDE for MySQL. In this article, we will look at setting up TDE for MySQL on Alibaba Cloud.
Note: TDE is currently only applicable to SQL Server 2008 R2 and MySQL 5.6. To view or modify TDE settings, you need to log in with an Alibaba Cloud account rather than a RAM account.
Setting Up TDE on ApsaraDB for RDS
To enable TDE on Alibaba Cloud:
Go to the RDS Management Console, select the appropriate RDS instance. Under Security Control, TDE tab you will be able to find the option to enable TDE.
This feature will allow encryption of data while at rest. The encryption of data while in transit is still the responsibility of the user and is outside the purview of this document.
Encrypting Tables
Log in to the database and execute the following command to encrypt the table to be encrypted.
Decrypting Tables
If you want to decrypt the TDE encrypted table, execute the following command.
You can find detailed TDE documentation here