In these turbulent times, encrypting your sensitive data only makes sense. The question is not so much whether to encrypt, but rather, which method of encryption to employ. There are several options, the three main widely used database encryption methods being:

• Application Programming Interface (API) – application level
• Plug-In – database level
• Transparent Data Encryption – disk/OS level

The closer we are to the application, the more source code changes are required. Conversely, the closer we get to the OS, less effort is required on the developers’ part. Disk encryption is also the most secure because even with access to the physical database server, a hacker can’t read the data.

Implemented in SQL Server 2008, Azure SQL Database, and Azure SQL Data Warehouse data files, Microsoft’s Transparent Data Encryption (TDE) achieves this by encrypting the database as data is written to the disk. Likewise, data is unencrypted when read from the disk. Therefore, data is in an unencrypted state only when in memory.

By default, SQL Server does not encrypt data at all, let alone to disk. A few steps are required to activate it. In today’s tip, we’ll review how to turn on TDS in SQL Server.

Create a master key

USE master;
GO
CREATE MASTER KEY ENCRYPTION
    BY PASSWORD='Use a Strong Password For the Database Master Key';
GO


Create or obtain a certificate protected by the master key

USE master;
GO
CREATE CERTIFICATE My_TDE_Certificate
       WITH SUBJECT='Certificate for TDE';
GO


Create a database encryption key and protect it by the certificate

USE MyDatabase
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;


Set the database to use encryption

ALTER DATABASE MyDatabase SET ENCRYPTION ON;
GO

Backing up the Certificate

Although this step is not required to encrypt a database using TDE, it’s vitally important that you can recover your encrypted data from a database backup, should your main database become corrupted. You should also backup the certificate if you’d like to move an encrypted database to another server. Here’s the code to accomplish the backup:

Make sure to store your backup password in a safe place. You will need this password to restore the certificate if you have to rebuild the server instance that hosts your encrypted database or need to move your database to another server.

Database Backups

One of the benefits of encrypting a database using TDE is that database backups will also be encrypted, thus enhancing data security. Since SQL Server 2016 you can also apply compression to your TDE-enabled database backups. Compressing your database backups is important because it enables you to save disk space by generating a backup file that is smaller than the database. In addition, it shortens the time required to restore the database.

Here’s how to apply compression to your TDE-enabled database backup: