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 [payroll] 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 [payroll] 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