SQL Server › Transparent Data Encryption
What is the hierarchy of encryption keys and certificates involved in setting up Transparent Data Encryption?
IntermediateAnswer
TDE relies on a hierarchy starting with a service master key at the server level, which protects a database master key, which in turn protects a server certificate, and finally that certificate is used to encrypt the database encryption key specific to each individual database that has TDE enabled, creating a chain of protection from the top level server security down to the actual data.
-- Setting up the key hierarchy from the top down
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPassword123!';
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'TDE Certificate';
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE MyServerCert;Real-world example A database administrator carefully follows the correct order for setting up the encryption key hierarchy, starting with the master key, then the certificate, before finally creating the database encryption key needed to enable TDE.