Securing SQL server 2008 databases

BANGALORE, INDIA: 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.

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