Running XMPie Server Products Connected to SQL Server with Transparent Data Encryption (TDE)

Summary: This article describes how to encrypt sensitive data in the database and protect the keys that are used to encrypt the data with a certificate using Transparent Data Encryption (TDE). TDE performs real-time I/O encryption and decryption of the data and log files.

Audience: XMPie customers and database administrators who wish to run XMPie Server products connected to an encrypted SQL Database.

Quick Links Hide

Overview

Transparent Data Encryption (TDE) encrypts SQL Server Database data files, known as encrypting data at rest. You can take several precautions to help secure the database, such as designing a secure system encrypting confidential assets and building a firewall around the database servers. However, in a scenario where the physical media (such as drives or backup tapes) are stolen, a malicious party can simply restore or attach the database and browse the data. One solution is to encrypt the sensitive data in the database and protect the keys that are used to encrypt the data with a certificate. This prevents anyone without the keys from using the data. TDE performs real-time I/O encryption and decryption of the data and log files. The encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery. The DEK is a symmetric key secured by using a certificate stored in the master database of the server, or an asymmetric key protected by an EKM module. TDE protects data "at rest", meaning the data and log files. It provides the ability to comply with many laws, regulations, and guidelines established in various industries. This enables software developers to encrypt data by using AES and 3DES encryption algorithms without changing existing applications.

About TDE

TDE enables you to encrypt an entire database. TDE is completely transparent to the application and requires no implementation of code changes. TDE requires SQL Server Enterprise edition. It is not available for SQL Server Standard edition. TDE is also available for SQL Server Datacenter edition. Encryption of the database file is performed at the page level. The pages in an ecnrypted database are ecnrypted before they are written to the disk and decrypted when read into the memory. TDE does not increase the size of the encrypted database.

Information Applicable to the SQL Database

When using TDE with SQL Database, the server-level certificate stored in the master database is automatically created for you by the  SQL Database.

Information Applicable to the SQL Server

When enabling TDE, 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, otherwise you will not be able to open the database. The encrypting certificate should be retained even if TDE is no longer enabled on the database. Even though the database is not encrypted, parts of the transaction log may still remain protected, and the certificate may be needed for some operations until the full backup of the database is performed. A certificate that has exceeded its expiration date can still be used to encrypt and decrypt data with TDE.

Encryption Hierarchy

The following illustration shows the architecture of TDE encryption. Only the database-level items (the database encryption key and ALTER DATABASE portions) are user-configurable when using TDE on the SQL Database.

 

 

Important: TDE does not provide encryption across communication channels. For information on how to encrypt data across communication channels, see Enable Encrypted Connections to the Database Engine (SQL Server Configuration Manager).

In addition, TDE protects the data at rest, but an authorized user such as a security administrator or a database administrator can access the data in a TDE-encrypted database. To prevent an SA or DBA from accessing selected parts of the data, you need to use application-level encryption. This is beyond of the scope of this document.

Using TDE

To use TDE, follow these steps using Transact-SQL.

 From this point forward, the instructions assume that you have completed the XMPie Server products installation process.

To access SQL Server Management Studio:

1.     On the taskbar, click Start, point to All Programs, point to Microsoft SQL Server, and then click SQL Server Management Studio.

The Connect to Server window is displayed.

2.     Enter/select the SQL Server credentials and click Connect to login to the SQL Server Management Studio.

3.     In the Object Explorer on the left pane, connect to an instance of the Database Engine.

4.     On the Standard bar, click New Query.

5.     Copy and paste the following example into the query window and then click Execute.

The following example illustrates encrypting the XMPDB2 database using a certificate installed on the server named MyServerCert.

 

-- Create a database master key and a certificate in the master database.
  
USE master;
GO
  
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '*rt@87(RT&Yask6';
GO
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'Certificate to protect TDE key'
GO
  
-- Create a backup of the server certificate in the master database.
  
-- The following code stores the backup of the certificate and the private key file
-- (C:\Program Files\Microsoft SQL Server\Backup\).
  
BACKUP CERTIFICATE MyServerCert
TO FILE = 'MyServerCert'
WITH PRIVATE KEY
(
    FILE = 'SQLPrivateKeyFile',
    ENCRYPTION BY PASSWORD = '*rt@87(RT&Yask6'
);
GO

 

-- Switch to the new database you would like to encrypt.

-- Create a database encryption key, that is protected by the server certificate in the master database.

-- Alter the new database to encrypt the database using TDE.

USE XMPDB2;

GO

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_128

ENCRYPTION BY SERVER CERTIFICATE MyServerCert;

GO

ALTER DATABASE XMPDB2

SET ENCRYPTION ON;

GO

 Make sure to repeat the above Transact-SQL script in order to encrypt the remaining XMPie databases.

The following example illustrates encrypting the DBname-to-Encrypt database using a certificate installed on the server named MyServerCert.

USE DBname-to-Encrypt;

GO

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_128

ENCRYPTION BY SERVER CERTIFICATE MyServerCert;

GO

ALTER DATABASE DBname-to-Encrypt

SET ENCRYPTION ON;

GO

 

Caution: Backup files of databases that have TDE enabled are also encrypted by using the database encryption key. As a result, when you restore these backups, the certificate protecting the database encryption key must be available. This means that in addition to backing up the database, you have to make sure that you maintain backups of the server certificates to prevent data loss. Data loss will result if the certificate is no longer available.

See also

Most of the information in this article has been taken from Microsoft's MSDN library. For further reading regarding Transparent Data Encryption (TDE), refer to : https://msdn.microsoft.com/en-us/library/bb934049.aspx.

Created by: Arik Michaelovich, last updated: November 2, 2015