First published on TECHNET on Jan 26, 2011
[Prior Post in Series]
In
Part 1
of this 2 part Mini-Lab series we looked at setting up TDE (Transparent Data Encryption) for a database and backing it up. In Part 2 we're going to discuss restoring a database (that has TDE enabled) to a different instance as well as disabling and removing TDE if desired.
RESTORING AN ENCRYPTED BACKUP TO A DIFFERENT INSTANCE
In Part 1 we backed up our TDE_TEST Database to a backup file called TDE_TEST_Encrypted.BAK. Now let's try and restore that database using a standard restore statement to a different instance:
USE MASTER
GO
RESTORE DATABASE TDE_TEST
FROM DISK = '
<<BACKUP PATH>>
\TDE_TEST_Encrypted.BAK' WITH
MOVE 'TDE_TEST' TO '
<<DATA PATH>>
\TDE_TEST.mdf',
MOVE 'TDE_TEST_log' TO '
<<LOG PATH>>
\TDE_TEST.ldf'
GO
We get the following error message:
Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint '0xF1BD42D532117FG5407P48C7Z4TT97D6C2FFBD82'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
The problem here is that we need to create the server certificate on the new instance that was used to secure our database encryption key. Remember in Part 1 of this series when we backed up our certificate? On the new instance (the instance we want to restore the backup to) we need to:
CREATE A DATABASE MASTER KEY
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Sup3rm@n'
CREATE A TRANSPARENT DATA ENCRYPTION CERTIFICATE FROM THE BACKUP
CREATE CERTIFICATE TDECertificate
FROM FILE = '
<<BACKUP PATH>>
\TDECertificate'
WITH PRIVATE KEY (FILE = '
<<BACKUP PATH>>
\TDECertificatePrivateKey',
DECRYPTION BY PASSWORD = 'Sup3rm@n' )
GO
Now that we have the server certificate that was used for TDE for the backup of the TDE_TEST database on the instance that we want to restore to we can restore from the backup file.
USE MASTER
GO
RESTORE DATABASE TDE_TEST
FROM DISK = '
<<BACKUP PATH>>
\TDE_TEST_Encrypted.BAK' WITH
MOVE 'TDE_TEST' TO '
<<DATA PATH>>
\TDE_TEST.mdf',
MOVE 'TDE_TEST_log' TO '
<<LOG PATH>>
\TDE_TEST.ldf'
GO
HOW TO DISABLE TDE
Disabling Transparent Data Encryption on a database is done by executing the following statement:
ALTER DATABASE TDE_TEST
SET ENCRYPTION OFF
In doing this we've disabled TDE for the user database TDE_TEST but once TDE is enabled on a database on an instance the tempdb system database for that instance will be encrypted as well. In order for tempdb to NOT be encrypted all user database must have TDE disabled and the SQL Server service will need to be restarted.
HOW TO REMOVE TDE
Removing Transparent Data Encryption on a database is done by executing the following statement:
USE TDE_TEST
GO
DROP DATABASE ENCRYPTION KEY
HOW TO CHECK IF TDE IS ENABLED
If you want to validate that TDE is either enabled or disabled for a particular database you can execute the following statement:
SELECT name, is_encrypted
FROM sys.databases
WHERE name = '
<<DATABASE NAME>>
'
There are many ways to try and protect your database but until now it wasn't as easy to protect the physical media (such as drives or backup tapes). With the use of SQL Server 2008 and Transparent Data Encryption this media is now useless to anyone without the keys.
What would it mean to you and your business if a hacker or a competitor had access to your database?
Please read the
Temenos T24 Core Banking Optimized on Microsoft SQL Server Database Platform
whitepaper for more information.
_____________________________________________________________________________________________
Follow Tier1OnSQL on Twitter
