This got me thinking because it has been a while I touched this subject, but was worth a revisit to my fundamentals of security related topics. After a couple of minutes, I got the drift and had suggested the following steps. When using SQL Server Database Mirroring, there are a few steps that need be implemented before enabling mirroring all the way.
The first step involves creating a Master Key on the Mirror server.
USE master; GO; CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'some strong password #1'; GO
The password doesn’t have to be the same as the one of the master key for the principal instance. This key is used to encrypt the certificate that will be created in the third step.
Second step is to backup the certificate on the principle. To backup the certificate used for encryption. The command would be:
USE master; GO; BACKUP CERTIFICATE MyPrinServerCert TO FILE = 'path_and_file_name.cer' WITH PRIVATE KEY ( FILE = 'path_and_file_name.pvk' , ENCRYPTION BY PASSWORD = 'your strong password #2' ); GO
The third step is to copy the certificate file and the key file to the mirror server and then create a certificate using these two files. The command would be:
USE master; GO; CREATE CERTIFICATE MyPrinServerCert FROM FILE = 'path_and_file_name.cer' WITH PRIVATE KEY (FILE = 'path_and_file_name.pvk', DECRYPTION BY PASSWORD = 'your strong password #2'); GO;
The password provided in this step has to match the password provided when backing up the certificate on the principle server.
Note: There is no direct Restore Certificate command. We have to use the Create Certificate command.
Similar steps apply in case of log-shipping / AlwaysOn as well. One important thing to keep in mind is that as and when the certificate used to encrypt the DEK of the Log-Shipped database changes, that certificate needs to be restored to the secondary server as well in order for the restore to be successful.
Since these steps are generic and can be used in other places too, I took the script and used it as part of the blog post. Do let me know if you ever used similar scripts in your environments while working with TDE inside SQL Server?
Reference: Pinal Dave (https://blog.sqlauthority.com)