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