I am trying to get my head around MSSQL TDE encryption.
I have followed these steps to encrypt a database:
1) Create a master key on the master database:
USE master GO CREATE MASTER KEY ENCRYPTION BY PASSWORD='thepbpass'; GO
2) Create certificate:
CREATE CERTIFICATE mycert WITH SUBJECT='mydb DEK'; GO
3) Associate the certificate to the database:
USE mydb GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE mycert; GO
4) Encrypt the database:
ALTER DATABASE mydb SET ENCRYPTION ON; GO
5) Export the certificate.
BACKUP CERTIFICATE mycert TO FILE = 'path_to_file' GO
My question is:
If my server died and I needed to restore a backup, do I have everything required for the restore?
I have the following:
- A certificate stored in a secure location.
- Knowledge of the secure master key password.
Do I need to worry about private keys or public keys? I have seen discussion about private and public keys online and I am not sure how they are relevant to my configuration or how they are different from the master key which I created in step 1.