What would it mean to you and your business if a hacker or a competitor had access to your database?
What would happen if data like personal customer information, Human Resources data, sales details, product lists and prices got in the wrong hands?
What about banking information like the data that would be stored in your Temenos T24 databases?
For most companies this would be critcal if not fatal blow to their continued existence.
Transparent Data Encryption (TDE) was introduced in SQL Server 2008 as a way to protect all the information in a database without any modifications to the applcations that access the data.
Below is an image (from MSDN article Understanding Transparent Data Encryption (TDE) ) that shows the architecture of TDE Encryption:
In Part-1 of this mini-lab we're going to:
- Create a Database
- Create a table with sample data
- Backup a Database (not encrypted)
- Create a MASTER KEY
- Create a CERTIFICATE
- Backup a CERTIFICATE
- Create a DATABASE ENCRYPTION KEY
- Set ENCRYPTION on for a database
- Backup a Database (encrypted)
CREATE A DATABASE
USE [master]
GO
CREATE DATABASE [TDE_TEST] ON PRIMARY
( NAME = N'TDE_TEST', FILENAME = N'
<<DATA PATH>>
\TDE_TEST.mdf' , SIZE = 100 MB , MAXSIZE = 1 GB, FILEGROWTH = 100 MB )
LOG ON
( NAME = N'TDE_TEST_log', FILENAME = N'
<<LOG PATH>>
\TDE_TEST_log.ldf' , SIZE = 25 MB , MAXSIZE = 1 GB , FILEGROWTH = 10%)
GO
CREATE A TABLE WITH SAMPLE DATA
USE TDE_TEST
GO
CREATE TABLE dbo.[Customer](
[CustomerID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](50) NOT NULL,
[LastName] [varchar](50) NOT NULL,
[EmailAddress] [varchar](50) NULL,
[Phone] [varchar](25) NULL
CONSTRAINT [PK_Customer_CustomerID] PRIMARY KEY CLUSTERED
(
[CustomerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO dbo.[Customer](FirstName, LastName, EmailAddress, Phone)
VALUES
('Clark', 'Kent', 'clarkkent@dailyplanet.com', '219-555-1111'),
('Lois', 'Lane', 'loislane@dailyplanet.com', '219-555-2222' ),
('Jimmy', 'Olsen', 'jimmyolsen@dailyplanet.com', '219-555-3333')
BACKUP A DATABASE (unencrypted)
BACKUP DATABASE TDE_TEST TO DISK = '
<<BACKUP PATH>>
\TDE_TEST_NotEncrypted.BAK' WITH INIT
GO
We're going to stop here for a second as this is probably where most of you are at (if you are not currently using Transparent Data Encryption). If you haven't tried to open up a BACKUP file before double-click on the TDE_TEST_NotEncrypted.BAK file and open it in WordPad.
Now it probably looks similar to this:
At first glance this file probably looks encrypted and unreadable but follow these steps:
- Press CTRL-F
- Search for the word: Clark
Now if someone was able to get a hold of your backup file it would be just as easy to restore the BAK file to their own server and have access the data but I just wanted to show that data (and backup itself) is not encrypted by default.
CREATE A MASTER KEY
The database master key is a symmetric key used to protect the private keys of certificates and asymmetric keys that are present in the database. When it is created, the master key is encrypted by using the Triple DES algorithm and a user-supplied password. ( MSDN Link )
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Sup3rm@n'
CREATE A CERTIFICATE
A certificate is a database-level securable that follows the X.509 standard and supports X.509 V1 fields. CREATE CERTIFICATE can load a certificate from a file or assembly. This statement can also generate a key pair and create a self-signed certificate. ( MSDN Link )
USE master
GO
CREATE CERTIFICATE TDECertificate WITH SUBJECT = 'TDE Certificate'
GO
BACKUP A CERTIFICATE
Backing up a certificate allows you to export a certificate to a file ( MSDN Link )
USE master
GO
BACKUP CERTIFICATE TDECertificate TO FILE = '
<<BACKUP PATH>>
\TDECertificate'
WITH PRIVATE KEY ( FILE = '
<<BACKUP PATH>>
\TDECertificatePrivateKey' ,
ENCRYPTION BY PASSWORD = 'Sup3rm@n' )
GO
CREATE A DATABASE ENCRYPTION KEY
A database encryption key is required before a database can be encrypted by using Transparent Database Encryption (TDE). When a database is transparently encrypted, the whole database is encrypted at the file level, without any special code modifications. The certificate or asymmetric key that is used to encrypt the database encryption key must be located in the master system database. ( MSDN Link )
USE TDE_TEST
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE TDECertificate
GO
SET ENCRYPTION ON FOR A DATABASE
ALTER DATABASE TDE_TEST SET ENCRYPTION ON
BACKUP A DATABASE (encrypted)
We now have our TDE_TEST database encrypted. Let's perform the same steps that we did in the BACKUP a DATABASE step above and open the BAK file to see if we can still see our data in plain text.
BACKUP DATABASE TDE_TEST TO DISK = '
<<BACKUP PATH>>
\TDE_TEST_Encrypted.BAK' WITH INIT
GO
Now open up the TDE_TEST_Encrypted.BAK in WordPad and search for "Clark" and you should see the same results as below.
Security and protecting data is an important part of the role of today's DBA. Thanks to Transparent Data Encryption (introduced in SQL Server 2008 Enterprise Edition) encrypting your database(s) is a much easier process to implement and no longer requires application level changes or changes to your database schema.
Please read the Temenos T24 Core Banking Optimized on Microsoft SQL Server Database Platform whitepaper for more information.
In Part 2 of this 2 part mini-lab series on Transparent Data Encryption we will look at restoring a database that has TDE enabled as well as the process for disabling and removing TDE if desired.