CREATE DATABASE [TDE_TEST] ON PRIMARY
( NAME = N'TDE_TEST', FILENAME = N'
\TDE_TEST.mdf' , SIZE = 100 MB , MAXSIZE = 1 GB, FILEGROWTH = 100 MB )
( NAME = N'TDE_TEST_log', FILENAME = N'
\TDE_TEST_log.ldf' , SIZE = 25 MB , MAXSIZE = 1 GB , FILEGROWTH = 10%)
CREATE A TABLE WITH SAMPLE DATA
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
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO dbo.[Customer](FirstName, LastName, EmailAddress, Phone)
('Clark', 'Kent', 'firstname.lastname@example.org', '219-555-1111'),
('Lois', 'Lane', 'email@example.com', '219-555-2222' ),
('Jimmy', 'Olsen', 'firstname.lastname@example.org', '219-555-3333')
BACKUP A DATABASE (unencrypted)
BACKUP DATABASE TDE_TEST TO DISK = '
\TDE_TEST_NotEncrypted.BAK' WITH INIT
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:
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. (
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. (
CREATE CERTIFICATE TDECertificate WITH SUBJECT = 'TDE Certificate'
BACKUP A CERTIFICATE
Backing up a certificate allows you to export a certificate to a file (
BACKUP CERTIFICATE TDECertificate TO FILE = '
WITH PRIVATE KEY ( FILE = '
ENCRYPTION BY PASSWORD = 'Sup3rm@n' )
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. (
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE TDECertificate
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 = '
\TDE_TEST_Encrypted.BAK' WITH INIT
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.