TDE encryption

Question

TDE encryption

as_1234

SSCrazy

Points: 2881
More actions
October 12, 2017 at 10:11 am

#329157

All,
I'm looking at enabling this on a Standard AG database. If possible can I check that I'm correct with the following understanding?:
Each instance has a server master key. I don't need to back this up as any new instance creates it own and it's not used to encrypt the database?
The database master key is only used to create the server certificate so again I don't need to back this up? On a new server I would just create a new key?
The server certificate encrypts the data and I do need to back up the certificate, the private key file and the password so that a) I can restore it to the replica and b) I can restore the database backup to a completely new server if required.
So basically to ensure I can access the data in any situation is the certificate backup, The private key backup and the password?
I think I can test all of this on a test database on the same servers without affecting the live database?
I saw some comments about needing the SQL service account or machine name to be unchanged but I don't think that's necessary for TDE as it is all linked to the Certificate which can be backed up and restore to any instance and/or machine?
Thanks

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113563 More actions · Answer 1

What edition of SQL are you running Enterprise or Standard, just double checking as you said "Standard AG Database" and TDE is not available in Standard edition.

If you are running Enterprise, links to follow are

https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption
https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/move-a-tde-protected-database-to-another-sql-server

as_1234 SSCrazy Points: 2881 More actions · Answer 2

Hello,

Thank you for your help.

I completely missed the fact that TDE isn't in Standard.

Thanks

Steve Jones - SSC Editor SSC Guru Points: 738632 More actions · Answer 3

TDE is EE only.

However, the only thing you need to move is the server certificate used for TDE. Backup and create from file on all replicas.

SMK separate for each instance.
DMK in master can be different. This is used in the hierarchy to protect the server cert, but when you create the new certificate on the replica instance, it gets bound to whatever DMK you have. You may need to create this yourself.