Transparent Data Encryption Setup and Gotchas
By Tom Nonmacher
In the world of data management, security remains a paramount concern. One of the most powerful tools in the arsenal of a database administrator is Transparent Data Encryption (TDE), a technology that encrypts database files to enhance security. This blog post will delve into the setup of TDE on different platforms including SQL Server 2016, SQL Server 2017, MySQL 5.7, DB2 11.1, and Azure SQL.
In SQL Server 2016/2017, TDE is set up by creating a database encryption key and setting it to use a certificate protected by the master key. After the database encryption key is created, you can set the database to use encryption. The following T-SQL script illustrates this process:
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password123';
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate';
USE MyDatabase;
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
ALTER DATABASE MyDatabase
SET ENCRYPTION ON;
For MySQL 5.7, the setup process is a little bit different. MySQL introduces a new concept of tablespaces which are essentially containers for tables and associated indexes. You can encrypt your tablespaces using the ENCRYPTION='Y' clause when creating a new tablespace. However, please note that MySQL TDE does not encrypt temporary files, binary logs, or relay logs.
In DB2 11.1, you can employ the new native TDE feature to encrypt your databases. However, this feature is only available in the DB2 Advanced Workgroup Server Edition and the DB2 Advanced Enterprise Server Edition. You can configure TDE by using the db2se command to create a secure keystore, then set the db2set DB2_ATS_ENABLE=DB2_ATS_OPT and restart the instance. Once done, you can create an encrypted database using the ENCRYPTED YES keyword in the CREATE DATABASE command.
In Azure SQL, TDE is enabled by default for all new databases. To enable TDE for an existing database, you can navigate to the database settings in the Azure portal and slide the Data encryption toggle to ON. Alternatively, you can also enable TDE using the Set-AzureRmSqlDatabaseTransparentDataEncryption PowerShell cmdlet.
While TDE is an effective tool for securing your data, there are a few gotchas to be aware of. For one, TDE does not protect data in transit. This means data can still be intercepted while being transmitted to and from your database. Additionally, TDE does not protect against SQL injection attacks, so you'll still need to sanitize your inputs and use parameterized queries where possible. Lastly, remember that TDE increases CPU usage, so be sure to monitor your server's performance and adjust accordingly.
In conclusion, TDE is a powerful tool that can help protect your sensitive data at rest. However, like any tool, it should be used judiciously and in conjunction with other security measures to achieve a comprehensive security posture. Always remember to test your setup thoroughly and monitor your system's performance to ensure that your encryption does not adversely impact your system's functionality.