SQL Server Encryption – Part4(How to Restore an (TDE enabled Database) encrypted Backup?)

Until now we’ve seen what TDE is, how to enable it on a Database, what are the key considerations which we should never forget as a DBA while dealing with Encryption stuff..bla bla…!! In this post of the Encryption Series, let’s actually see how to Restore a Backup of a database which has TDE enabled on it.

As i mentioned earlier, we can’t restore a backup on a different instance without Certificates. We’ve to restore the Cert from Source Server , then we should be able to restore the Database from the Backup.

Note: Enabling TDE on a Database encrypts every single portion of the database. It includes mdf, ldf and any ndf’s as well. All your Database Backups are encrypted as well once you turn ON TDE!! Heyyy…that’s awesome!! One thing you should be aware of is, If you are using FileStreams, they are not going to be encrypted with this TDE.

My Scenario: I’ve a Encrypted Database “Adventureworks” in my PROD Instance and i took a Full backup of it(Adv_TDE.bak). Let’s try to restore on my DR Instance as a brand new Database. As you can see below it is throwing “certificate related error Messages” when i am trying to restore the database.(You’ll get the Same error message if you try to use detach/attach method or Copy DB Wizard)

To keep it real simple I’m taking all the defaults(which will fail in real case without MOVE) while restoring Database in my T-SQL Restore Syntax. Remember this blog post is not about normal DB Restores Syntax(es) 😀

Soo…..what should we do???

All We’ve to do is restore our Certificate from source(And you have to create a DMK on destination if you don’t have one), Once you are done with that piece of puzzle, the rest is just performing your normal DB restore process what you do on any other non encrypted user database(s)! So..Basically all we need to do is creating the certificates on the destination Instance from the backup of your certificates which we created in earlier post.

On DR Instance: 

Create a Master Key(You don’t have to do this if you already have DMK) and Create a certificate from the Cert Backup and providing Private Key file and Password to decrypt it as shown below

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'DIFFERENTSTRONGPASSWORD'
GO
CREATE CERTIFICATE TDEDEMOCERT 
    FROM FILE = 'D:\Backs\TDEDEMOCERT.BAK' 
    WITH PRIVATE KEY (FILE = 'D:\Backs\TDEkey.bkey',
    DECRYPTION BY PASSWORD = 'VeryStrongPassword');
GO 

/*the Certificate encryption piece on PROD was
BACKUP CERTIFICATE TDEDEMOCERT /*this was the certificate which we created earlier*/
TO FILE = 'D:\Backs\TDEDEMOCERT.BAK'
WITH PRIVATE KEY ( FILE = 'D:\Backs\TDEkey.bkey',
ENCRYPTION BY PASSWORD = 'VeryStrongPassword' )
GO
*/

Now let’s do the actual restore and see how it goes….

Perfect!!! Once we created certificates based on the Cert backup from PROD Instance, we were able to do Restore!! Soo….Key thing to remember is to Backup your certificate and store it in a safe as soon as you’ve Encrypted your database. I want to stress again – If you loose it, you lost everything related to your Database(You are safe as long as you can access the source database)!!!

Advertisements

SQL Server Encryption – Part3(GOTCHAS – A TDE Enabled Database)

In the Previous two Posts of this series, we’ve seen how to actually encrypt our Database(s). In this post, let’s focus on things to consider on Databases with TDE enabled. Let me tell you something…there are lot of GOTCHAS as a DBA for Encrypted databases you should be aware of. You should not be in a situation saying “Oops! I missed it, what should i do now?”.  Reminding again….If you loose your Master Key, You’ve lost your Database, No Ifs and buts! If you are creating a DB Encryption key with AES_128/AES_256 Algorithm and if you lost it, i believe it would be a very tough challenge even for an ethical hacker to decrypt it ;)(I might be wrong, but I would expect the same level of encryption what i’m saying!!) Okay!! Enough Scaring you all, let’s get into the actual content!

Very first thing, Encryption adds a little Overhead on your CPU. Thorough testing is mandatory!

Enabling TDE on a Database might require an Application outage, It takes some time depending on your database size(I think it acquires some locks on objects while enabling encryption for the first time, I’m not 100% sure). As a safe bet, do this after Business hours taking on a maintenance window. (Make sure that you’ve tested in your lower environments.)

If you enable TDE on any Database on your Instance, Your TEMPDB is automatically Encrypted as well. Soo…there’s a high possibility impacting other Databases which are actually not using TDE feature while you are in the process of enabling TDE!!!

Instant File Initialization won’t work on TDE Enabled databases and Log truncation won’t happen through the Encryption process(While Background Encryption scanner is in progress). So, keep a close eye on your LDF file when actual encryption process is in progress.

You’ve to backup your Service Master key, Certificates and Master key and secure it in a safest location(Far away from where you store your Database Backups. The idea is not to have access to your Keys for any intruder even though he/she got hold of your DB Backups!

How to Backup your Service Master Key??

BACKUP SERVICE MASTER KEY TO FILE = 'D:\Backs\SMK_08312011.bak'
ENCRYPTION BY PASSWORD = 'VERYVERYSTRONGPASSWORD'

How to Backup your Master Key?

BACKUP MASTER KEY TO FILE = 'D:\Backs\Master_Key.bak'
ENCRYPTION BY PASSWORD = 'DIFFERENTSTRONGPASSWORD'

How to Backup your Database Key?

BACKUP CERTIFICATE TDEDEMOCERT /*this was the certificate which we created earlier*/
TO FILE = 'D:\Backs\TDEDEMOCERT.BAK'
WITH PRIVATE KEY ( FILE = 'D:\Backs\TDEkey.bkey',
ENCRYPTION BY PASSWORD = 'VeryStrongPassword' )
GO

That’s all for the day! Let’s see how to actually restore a Database which is Encrypted on a completely different server in the upcoming post of this encryption series.