Securing Database Backup with Transparent Database Encryption (TDE) in SQL Server 2008
By : Kasim Wirama, MCDBA
Data is vital asset for an organization. Imagine if data could be restore in other sql server without consent of the organization, all company data could be read by unauthorized person. SQL Server 2008 comes to provide database backup security by encrypting database files so that data could not be easily restored without consent of database administrator. TDE is the answer for this issue. Let me show how to implement secure database backup-restore with TDE.
First create instance master key encrypted by password and create certificate that would be encrypted by instance master key. These processes will be done under context of master database.
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘strongP@ssw0rd’;
GO
CREATE CERTIFICATE Tde_cert
WITH SUBJECT = ‘encryption by TDE’;
GO
Next change database context to target database that you want to encrypt, for example : AdventureWorks. Create database encryption key by server certificate (Tde_cert) and choose encryption algorithm provided by SQL Server 2008 (I use AES_128) and turn on database encryption by altering database property with ALTER DATABASE.
USE adventureworks
GO
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE Tde_cert;
GO
ALTER DATABASE Adventureworks
SET ENCRYPTION ON;
GO
Now you can create database backup of AdventureWorks and restore it in other SQL Server instance. An error will come up by giving you information that the restored database need server certificate from first SQL Server instance, so backup the server certificate along with private key for decryption purpose and restore the certificate on second SQL Server instance.
--in first SQL Server instance.
USE master
GO
BACKUP CERTIFICATE Tde_cert
TO FILE = ‘C:\Tde_cert.cer’
WITH PRIVATE KEY
(
FILE = ‘C:\Tde_cert.pvk’,
ENCRYPTION BY PASSWORD = ‘myp@ssw0rd’
);
GO
--in second SQL Server instance
USE master
GO
CREATE DATABASE MASTER KEY ENCRYPTION BY PASSWORD = ‘@ther1nst@nce’;
GO
CREATE CERTIFICATE Tde_cert
FROM FILE = ‘C:\Tde_cert.cer’
WITH PRIVATE KEY
(
FILE = ‘C:\Tde_cert.pvk’,
DECRYPTION BY PASSWORD = ‘myp@ssw0rd’
);
After that you can restore database on second SQL Server instance as usual.