Mistake: treating the database master key as disposable setup. It is the encryption root inside a database, protecting certificates and asymmetric keys that protect lower-level symmetric keys and encrypted data. section 14.6.1
Losing it can make every encrypted object beneath it unusable. Back it up immediately, protect the backup password, and preserve the related certificate or recovery may be impossible. section 12.11 section 14.6.1
HumanResources salary encryption
Create the HumanResources database master key, back it up, then create SalaryCert and protect SalaryKey with that certificate. SalaryKey encrypts values stored in Employees.EncryptedSalary. section 14.6.1
Listing 14.8 Creating the encryption hierarchy
USE HumanResources ;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd' ; #1
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Pa$$w0rd';
BACKUP MASTER KEY TO FILE = 'c:\keys\HumanResourcesMasterKey.key' #2
ENCRYPTION BY PASSWORD = 'Pa$$w0rd' ;
GO
CREATE CERTIFICATE SalaryCert #3
WITH SUBJECT = 'Employee Saleries' ;
GO
CREATE SYMMETRIC KEY SalaryKey #4
WITH ALGORITHM = AES_128
ENCRYPTION BY CERTIFICATE SalaryCert ;
GO
Inspect the complete T-SQL sequence for creating, backing up, and using a database master key within an encryption hierarchy. section 14.6.1
Listing 12.12 Creating cryptographic objects required to encrypt backups
USE master ;
GO
CREATE MASTER KEY #1
ENCRYPTION BY PASSWORD = 'Pa$$w0rd' ;
GO
CREATE CERTIFICATE CertForBackupEncryption #2
WITH SUBJECT = 'Backup Encryption Certificate' ;
GO
Use the prerequisite pattern for backup encryption: create a master key and certificate in master before encrypting database backups. section 12.11
