TDE: What Is Is and Why It Exists
When it comes to database encryption, there are two areas to think about: encryption during transport and encryption at rest.
Encryption during transport means the communication between the database and your client (your application, or SQL Server Management Studio for example) is encrypted. Many developers who use SQL Server are already familiar with specifying Encrypt=True in a connection string. It isn't necessary to create a certificate to use this feature, but in a Production environment you'd want to create a certificate and configure the client to only trust that certificate.
All well and good, but encryption during transport doesn't change the fact that the database data on disk is not encrypted. If you dumped the database file of your Contacts database, you would see visible names and contact information. If someone made off with that file, they'd have access to the data.
This is where encryption at rest comes in: keeping the database data encrypted on disk. That means data is encrypted when inserted or updated, and decrypted when queried. If you consider what would be involved in doing this yourself in your application code, it's pretty daunting: you'd need to be sure encryption and decryption was applied uniformly, and doing so without a performance impact would be a major feat; plus, external applications like report generators would no longer be able to do anything with the database.
Fortunately, the Transparent Data Encryption feature exists and it is extremely well done. Once you turn it on, it just works. Data in the data file is encrypted. Data you work with isn't. Conceptually, you can think of it like the diagram below (and if you want all the specific encryption details, see the Microsoft documentaton link at the top of this post). And as we said earlier, the data can also be encrypted during transport with a connection string option.
In my experience TDE doesn't noticably impact performance. If you're an authorized user who has specified valid credentials, nothing will seem at all different to you. But if you dumped the database files, you would no longer be able to see understandable data.
Although TDE is a very nice feature, it's only available in Enterprise Edition--so it comes at a price. There is one other edition where TDE is available, and that's Developer Edition. This means you can experiment with the feature--or demonstrate it to a client--without having to buy Enterprise Edition up front. Understand, however, that you cannot use Developer Edition in a Production environment.
Enabling TDE
The procedure to enable TDE is not difficult. These are the steps:1. Install SQL Server Developer Editon or Enterprise Edition.
2. Run SQL Statements to create a key and certificate.
3. Run SQL Statements to enable TDE.
4. Back up the certificate and key file.
1. Install SQL Server Developer Edition or Enterprise Edition
You can download SQL Server Developer Edition from the MSDN web site. For Enteprise Edition, follow the instructions you receive through your purchasing channel to obtain the software.
Create or restore a database, and ensure the database is functional and that you can get to it from SQL Server Management Studio.
2. Run SQL Statements to Create a Certificate
A master key and a certificate are needed for the encryption feature. To create them, run the statements below again the MASTER database.
USE master
GO
CREATE
MASTER KEY ENCRYPTION BY PASSWORD = 'my-password';
GO
CREATE
CERTIFICATE TDEServerCert WITH SUBJECT = 'My DEK Certificate';
GO
3. Run SQL Statements to Enable TDE
USE App
GO
CREATE
DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER
CERTIFICATE TDEServerCert;
GO
ALTER
DATABASE App SET ENCRYPTION ON;
GO
4. Back Up the Certificate and Key File
This next step makes a back up of the certificate and key file used for TDE. This step is vital: any backups you make from this point forward cannot be restored unless you have the certificate and key files.
BACKUP CERTIFICATE TDEServerCert TO FILE = 'c:\xfer\TDEServerCert.crt'
WITH PRIVATE KEY
(
FILE = 'c:\xfer\TDEServerCert.pfx',
ENCRYPTION BY PASSWORD = 'my-password'
)
Confirming DTE
After enabling DTE, you'll want to confirm your application still works like it always has.
To confirm to yourself that TDE is really active, or provide evidence to an auditor, you can use this query:
SELECT [Name], is_master_key_encrypted_by_server, is_encrypted from master.sys.databases
This will display a list of databases and whether or not they are encrypted.
name is_master_key_encrypted_by_server is_encrypted
master 1 0
tempdb 0 1
model 0 0
msdb 0 0
App 0 1
If you're still skeptical, you can also dump your database files.
