Categories
Security

How to Copy Move a Database that is encrypted with TDE

In my previous articles on TDE Ive gone through whats TDE and how to configure it. Now here in this article Im going to test whether Im able to restore attach the database without encryption key since the main property of TDE is not to allow to use the physical files of the database without certificate. Ill also brief about how to copy move the database that is encrypted with TDE.

Testing TDE without Certificate in the Instance

In the previous article Ive encrypted the database TDE_Testing, lets try to use the physical files of this database with different methods to restore attach in another instance which doesnt have this certificate and lets check whether SQL Server 2008 detects this.

Backup and Restore Method

Lets take the backup of TDE_Testing database which is encrypted with TDE

tde1

Now we have the backup of encrypted database lets try to restore it in a instance which dont have the certificate. SQL Server 2008 has immediately thrown the error couldnt find the certificate.

Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint 010B56404F499B92B8A372B6B05D44D283A88F723?.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

tde2

Hence if you encrypt the database with TDE you should have the certificate backup with the database backup then only you will be able to restore it in different instance.

Detach Attach Method

Ok the first method gets failed lets try to detach the database, copy the physical files of the database to another instance and will attach it there. Ive already detached the database and moved to an another instance lets try to attach it.. Yes you are right SQL Server 2008 detects the TDE and returns the below error

Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint 010B56404F499B92B8A372B6B05D44D283A88F723?.

tde3

Hence if you encrypt the database with TDE you should have the certificate backup with the database physical files then only you will be able to attach it in different instance.

Compressed Backup and Restore Method

Hmmm, both the methods failed. Ok lets see whether SQL Server 2008 detects TDE on compressed backup. Now Im going to take a compressed backup of TDE_Testing database and going to restore it in another instance.

tde4

Lets restore it.

tde5

Yeap! SQL Server detects TDE in compressed backup too. So you to have the certificate backup.

Copy Database Wizard

Yeah we got another method to move the database, lets try to copy move the database using Copy database wizard into an instance which don’t have the certificate. Lets see whether SQL Server detects it.

tde6

tde7

Great SQL Server 2008 detects this too, its not allowing me to move the database without the certificate. Hence the certificate is too important to copy move the database which is encrypted with TDE.

Import Export Wizard

Ok Lets all the method above failed, lets try to use Import Export wizard to move the datas from one instance to another instance.

tde8

tde9

Ah Ah!.. This worked for me.. You know why this worked, because this is out of TDE architecture and TDE doesn’t encrypt the data’s which is residing at buffer pool or in memory hence when you use Import Export wizard data’s will be retrieved from disk to buffer pool hence those data’s are not encrypted and its transferred to another instance without encryption hence this gets succeeded.

Ok, now we have tested almost all the possibilities to move the physical files of the database from once sever to another server which is encrypted with TDE and all the possibilities got failed. From this you know how important is to backup the certificate, this is because if the instance is crashed then without this certificate you cant recover the database.

Lets discuss the proper method to move copy the database which is encrypted with TDE.

Move Copy a database which is encrypted with TDE

To copy or move a database which is encrypted with TDE you need to create the certificate in the destination instance first from the backup of the certificate created. In this case Ive created the certificate backup while writing my previous article, lets use that backup and restore it in the destination instance.

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'TDEtesting123'
GO
CREATE CERTIFICATE TDE_Testing 
    FROM FILE = 'C:\Users\Sagar\Documents\TDE_testing_cert_backup' 
    WITH PRIVATE KEY (FILE = 'C:\Users\Sagar\Documents\TDE_testing_key', 
    DECRYPTION BY PASSWORD = 'TDEtesting123');
GO 

tde10

 

Now we have restored the certificate in the destination instance, now lets try to attach & restore the database, this should now work.

Detach Attach Method

tde11

After the certificate is restored we are now able to attach the database.

Backup Restore Method

tde12

Great this too worked lets see whether the database is encrypted in the destination server.

tde13

Conclusion

Transparent Data Encryption (TDE) is one of the simple and great encryption feature available in SQL Server 2008. If you encrypt a database with TDE no one can use the physical files of the database until they have the certificate with them. The main thing is that you need to take a backup of the certificate as soon as possible when you encrypt the database since if the server is crashed you wont be able to retrieve the database without this certificate hence backup the certificate and place it in a safe location.

Leave a Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

*