Hi Folks,
One of my friends had a requirement where he was supposed to restore a database which has TDE enabled on both Source & Destination. This sounded something interesting to me hence thought of giving it a try.
I am not going to explain how to configure TDE as it has been explained in many blogs.
Here are the high level steps
1) creating a Master Key
USE MASTER;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Admin123$$'
After creation of master key if we fire sys.symmetric_keys we will find 2 rows one for SMK & the other for Master key of what we created above.
If you are not aware of what SMK is below is the description for the same.
USE MASTER;
CREATE CERTIFICATE TDECert WITH SUBJECT = 'TDE Certificate'
3)Creating a Database Encryption Key
use TDETEST_node1
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY
SERVER CERTIFICATE TDECert ;
As you can see in the above screen shot it is giving a warning message indicating that certificate has not been never backed up so we need to backup it up.
use master
BACKUP CERTIFICATE TDECert TO FILE = 'D:\Backup\certificate_TDE_Test_Certificate.cer'
WITH PRIVATE KEY(FILE = 'D:\Backup\certificate_TDE_Test_Key.pvk',
ENCRYPTION BY PASSWORD = 'Admin243$$')
4)Turning TDE ON
use TDETEST_node1
ALTER DATABASE TDETEST_node1 SET ENCRYPTION ON ;
I am doing the same steps on Server 2 as well but with database name as TDETEST_node2 & changed the passwords for master key & private key encryption.
When we try to perform restore operation with out copying the certificate & private key we will get the below error
So we need to copy the private key & the certificate from server 1 to server 2.
once it is done we need to restore the certificate unlike restore statement we should use create instead & ensure u provide the same password of what you made use of at the time of backing up the certificate on server 1
CREATE CERTIFICATE TDECert
FROM FILE = 'D:\restore\certificate_TDE_Test_Certificate.cer'
WITH PRIVATE KEY (FILE = 'D:\restore\certificate_TDE_Test_Key.pvk',
DECRYPTION BY PASSWORD = 'Admin243$$')
once the above statement gets completed you can run your restore of database of source on to destination with out any issues.
conclusion:: There are many blogs which shows how to restore TDE enabled database on to another instance however there we need to create master key. In our scenario as the database on destination has already involved in TDE we can exempt that step & proceed with just copying the certificate & private file which would suffice the requirements.
One of my friends had a requirement where he was supposed to restore a database which has TDE enabled on both Source & Destination. This sounded something interesting to me hence thought of giving it a try.
I am not going to explain how to configure TDE as it has been explained in many blogs.
Here are the high level steps
1) creating a Master Key
USE MASTER;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Admin123$$'
After creation of master key if we fire sys.symmetric_keys we will find 2 rows one for SMK & the other for Master key of what we created above.
If you are not aware of what SMK is below is the description for the same.
Service Master Key: At the top of the key hierarchy is the Service Master Key. There is one per SQL Server instance, it is a symmetric key, and it is stored in the master database. Used to encrypt Database Master Keys, Linked Server passwords and Credentials it is generated at first SQL Server startup.
There are no user configurable passwords associated with this key – it is encrypted by the SQL Server service account and the local machine key. On startup SQL Server can open the Service Master Key with either of these decryptions. If one of them fails – SQL Server will use the other one and ‘fix’ the failed decryption (if both fail – SQL Server will error). This is to account for situations like clusters where the local machine key will be different after a failover. This is also one reason why service accounts should be changed using SQL Server Configuration Manager – because then the Service Master Key encryption is regenerated correctly.
2)creating a certificate protected by Master keyUSE MASTER;
CREATE CERTIFICATE TDECert WITH SUBJECT = 'TDE Certificate'
3)Creating a Database Encryption Key
use TDETEST_node1
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY
SERVER CERTIFICATE TDECert ;
As you can see in the above screen shot it is giving a warning message indicating that certificate has not been never backed up so we need to backup it up.
use master
BACKUP CERTIFICATE TDECert TO FILE = 'D:\Backup\certificate_TDE_Test_Certificate.cer'
WITH PRIVATE KEY(FILE = 'D:\Backup\certificate_TDE_Test_Key.pvk',
ENCRYPTION BY PASSWORD = 'Admin243$$')
4)Turning TDE ON
use TDETEST_node1
ALTER DATABASE TDETEST_node1 SET ENCRYPTION ON ;
I am doing the same steps on Server 2 as well but with database name as TDETEST_node2 & changed the passwords for master key & private key encryption.
When we try to perform restore operation with out copying the certificate & private key we will get the below error
So we need to copy the private key & the certificate from server 1 to server 2.
once it is done we need to restore the certificate unlike restore statement we should use create instead & ensure u provide the same password of what you made use of at the time of backing up the certificate on server 1
CREATE CERTIFICATE TDECert
FROM FILE = 'D:\restore\certificate_TDE_Test_Certificate.cer'
WITH PRIVATE KEY (FILE = 'D:\restore\certificate_TDE_Test_Key.pvk',
DECRYPTION BY PASSWORD = 'Admin243$$')
once the above statement gets completed you can run your restore of database of source on to destination with out any issues.
conclusion:: There are many blogs which shows how to restore TDE enabled database on to another instance however there we need to create master key. In our scenario as the database on destination has already involved in TDE we can exempt that step & proceed with just copying the certificate & private file which would suffice the requirements.
Comments