Securing SQL Server 2008 Databases

Securing a database is always a top priority for a database professional,

whatever capacity he might be working in, a database developer, administrator,

designer or architect. Each new version coming from all database vendors adds

more security to a product, and Microsoft SQL Server is no different. MS SQL

Server 2005 provided you with out-of-the-box features to encrypt data using

certificates, symmetric key encryption and asymmetric key encryption. But that

was at the cell level, which means, you could encrypt one column in a row before

you store it in the database, and decrypt the information before you use it. SQL

Server 2008 introduces a new feature, Transparent Data Encryption (TDE), which

lets you encrypt the complete database. Securing a database is a vast and

complex topic, so let me restrict this discussion to showing how to use TDE in

databases.

What is TDE?



TDE encrypts your entire database using a symmetric key. This symmetric key is
called as the database encryption key (DEK). The encryption will be completely

transparent to the applications accessing the database. The older cell-level and

the newer TDE are compatible and therefore can be used together. TDE encrypts

the data stored in both the database's data file and log file. In addition, any

backups for the database are also encrypted. Database encryption is performed at

the page level. Data is encrypted on the disk and then decrypted as it is read

into memory. Please check the article, Getting the Most Out of SQL Server Pages'

at http://pcquest.ciol.com/content/search/showarticle.asp?arid=111301&way=search

for more information.



Enabling TDE


You first create a master key for the database. It should be in the master
database.

USE master;



GO

CREATE MASTER KEY ENCRYPTION



BY PASSWORD='MyStrongPassword';


GO

Then you create a certificate that is protected by the master key.

CREATE CERTIFICATE payrollCert



WITH SUBJECT='TDE for payroll';


GO

Next you create the DEK and secure it using the certificate.

USE payroll



GO

CREATE DATABASE ENCRYPTION KEY



WITH ALGORITHM = AES_128


ENCRYPTION BY SERVER CERTIFICATE payrollCert;


GO

Finally you enable the encryption.

ALTER DATABASE payroll



SET ENCRYPTION ON;


GO

You can still access the data as before, and TDE is transparent to the

client.

SELECT * FROM dbo.Employee

So, what's the point in encrypting, if people can access data without

providing the password? TDE protects data 'at rest,' meaning both data and log

files as well as any backup is encrypted. And the master database of my instance

knows the password to decrypt.

Let me take a backup of the payroll database.

BACKUP DATABASE payroll



TO DISK = 'd:\Amaresh\Data\payroll.bak'

I will now try to restore the database on a different instance.

RESTORE DATABASE FROM DISK = N'D:\Amaresh\Data\payroll.bak'



WITH MOVE N'payroll' TO N'D:\Amaresh\Data\payroll.mdf',


MOVE N'payroll_log' TO N'D:\Amaresh\Data\payroll.ldf'


GO

This however gives me an error.

As the database and backup are encrypted, I am not able to restore it on a

different instance, without providing the key.

Restoring encrypted databases



Now you need to provide the new instance with the key and the certificate.

For that you need to first back them up from the old instance.

USE master



GO

BACKUP CERTIFICATE payrollCert



TO FILE = 'd:\Amaresh\Data\payrollCert'


WITH PRIVATE KEY (file='d:\Amaresh\Data\payrollCertKey',


ENCRYPTION BY PASSWORD='MyStrongPassword2')

You would now need to restore them to the new instance.

USE master



GO

CREATE MASTER KEY ENCRYPTION



BY PASSWORD = 'MyNewStrongPassword'

CREATE CERTIFICATE payrollCert



FROM FILE='d:\Amaresh\Data\payrollCert'


WITH PRIVATE KEY (


FILE = 'd:\Amaresh\Data\payrollCertKey',


DECRYPTION BY PASSWORD='MyStrongPassword2')

Now the following restore succeeds.

RESTORE DATABASE FROM DISK = N'D:\Amaresh\Data\payroll.bak'




WITH MOVE N'payroll' TO N'D:\Amaresh\Data\payroll.mdf',


MOVE N'payroll_log' TO N'D:\Amaresh\Data\payroll.ldf'


GO

Conclusion



TDE is an important new addition to the list of security features provided by
Microsoft SQL Server to secure your data. TDE uses symmetric key encryption to

encrypt your entire database. Data is encrypted on the disk and then decrypted

as it is read into memory. This process is transparent to client applications.

The author is top-scorer world-wide in SQL Server Admin Certification on

brainbench.com.

Amaresh Patnaik