Performing transparent data encryption in SQL Server 2008

Problem

Security is a major consideration for any company. Database backups are easily stolen and restored to another instance of SQL Server. As we explore the new features of SQL Server 2008, we are interested in a feature called Transparent data encryption, which we can use to encrypt our database backups. Can you give us a detailed description of how this new feature should be used?

Expert answers

Transparent data encryption is a new feature of SQL Server 2008 that performs database-level encryption, complementing the record-level encryption currently used in SQL Server 2005. It protects database access either directly or by restoring a database backup to another instance of SQL Server.

In this article, we will look at how transparent data encryption can be performed to secure database backups. Let's start with a description of how database backups are protected from threats. By default, SQL Server backups are unencrypted. Let's start by creating a full backup of the Northwind database on my default instance.

BACKUP DATABASE Northwind
TO DISK = 'C:\Northwind_unencrypted.bak'
WITH INIT, STATS = 10

Let's query a specified record in the Northwind database. We will use this record later to view our database backup content.

SELECT * FROM dbo.Customers
WHERE ContactName = 'Aria Cruz'

Open the database backup in Notepad and search for "a R i a C r u z" (Note the space between the letters and the three spaces between "Aria" and "Cruz", because this is Unicode text). You can see that the text data is readable.