UPDATE November 2016 – With SQL Server 2016 SP1, Microsoft will include key enterprise-class features in every edition of SQL Server 2016. This includes TDE! So lets have a recap.
What is it?
Quite simply – ENCRYPTION at rest; your data files will be encrypted on disk which is done at page level. So the page will be encrypted when written to disk and decrypted when read into the buffer pool.
Taken from Books on line (BOL) we will ultimately be going though the below steps in strict order.
SELECT * FROM sys.symmetric_keys
You will see that I have a Service master key and I have already created my Database master key. So let’s create a certificate.
USE master GO CREATE CERTIFICATE TDECertAW WITH SUBJECT = 'tde Certificate AW'
Now that we have created a certificate we will use it to encrypt the Database Encryption key in the user database.
USE [CustRecords] GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE TDECertAW; GO
Output = Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.
So yes, you really should follow the instructions above and store these files in a secure offsite location.
USE master; GO BACKUP CERTIFICATE TDECertAW TO FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\TDECertAW.cer' WITH PRIVATE KEY (FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\certificate_TDECertAW.pvk', ENCRYPTION BY PASSWORD = '!£$Strongpasswordherewelikesqlserver#')
ALTER DATABASE [CustRecords] SET ENCRYPTION ON; GO
--check USE master; GO SELECT db.name,db.is_encrypted,dm.encryption_state,dm.percent_complete,dm.key_algorithm,dm.key_length FROM sys.databases db LEFT OUTER JOIN sys.dm_database_encryption_keys dm ON db.database_id = dm.database_id WHERE name = 'CustRecords'
State 3 which means encrypted. (Please note that TempDB will also get encrypted).
Let’s look at a page before encryption was enabled. First the contents of a table from SSMS (SQL Server Management Studio)
So let’s calculate the offset for page 78: 78*8192 = 638976, so when we use the hex-editor we shall look for 638976 (decimal) as the beginning of the page.
You will see the ASCII representation of the data. This is what we expect for a database that does not have TDE enabled.
So what happens when TDE is enabled?
Let’s check a page first using print option 3 which means per row interpretation.
DBCC TRACEON (3604); GO DBCC PAGE (CustRecords, 1, 78, 3);
Above is a snippet where you see decrypted data – as you would expect.
So let’s look at the MDF (on-disk) using the technique discussed earlier – again navigating to the offset via a hex-editor.
This is the outcome of enabling TDE – see the difference?