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.
OK, In case anyone wants to know, I got to the bottom of this.
The chain is pretty easy to work out:
Create Service Master Key (automatically done upon install)
Create Database Master Key (user creates their own key)
SymetricEncrypt(Service Key,Database Master Key) -> Store Encrypted Master Key into Master DB
Create Certificate
SymetricEncrypt(Database Master Key, Certificate Private Key) -> Store Encrypted private key in Master DB
Create Database Encryption Key (system creates a random key, but must be manually added to database)
ASymetricEncrypt(Certificate, Database Encryption Key) -> Store Encrypted DEK into database.
So, to decrypt the database data, you'll need to:
Use the SMK to decrypt the DMK
Use the DMK to decrypt the cert private key
Use the certificate to decrypt the DEK
Use the DEK to decrypt the database
The starting point is the Service Master Key... how is this stored (if you encrypt it, you can't retrieve data, and if left in plaintext your system is exposed).
The answer is, the SMK is encrypted with Data Protection API, which is a service built into windows.
Basically, data is encrypted with "secrets" from your user profile. So the same user profile must be used to decrypt the SMK.
Now that I know this, I can use DPAPI in my applications to encrypt my encryption keys :)
Best Answer
Correct, you've stated that the Column Master Key (CMK) will be created/stored there. Most likely this was done via the GUI to generate a new key and save it there. There are other options as well for the CMK.
Correct, that was the location used when executing the metadata update to SQL Server for where the CMK exists. Since SQL Server doesn't go out and touch that Certificate (which shouldn't be in the server) it only knows what to send back to the client driver as metadata. The certificate holds an asymmetric key pair that is used to encrypt and decrypt the Column Encryption Key (CEK) which is stored inside of SQL Server in the same database.
Assuming AE is setup correctly, the client only needs that certificate, yes. There are obviously database level permissions that are required to get said metadata but overall the process is moot if you don't have a copy of the CMK.
No, as I stated, there is the CMK which is the certificate you've created and then there is the CEK which does the actual encryption and decryption of the data.
You'll want to go back and read a bit more about Always Encrypted and how it works as this is all explained in the documentation.