AWS Database Blog

Secure your data at rest on Amazon RDS Custom for SQL Server using transparent data encryption (TDE) or column-level encryption (CLE)

Customers in industries such as healthcare and financial services take full advantage of the built-in security features of SQL Server to meet their regulatory and compliance requirements. When migrating their database to the AWS Cloud, we often see customers asking for SQL Server security feature support such as TDE, column-level encryption on Amazon Relational Database Service (Amazon RDS) Custom for SQL Server to protect the database from unauthorized users or external threats. Amazon RDS Custom for SQL Server now supports transparent data encryption (TDE) and column-level encryption (CLE) databases to secure data at rest. By default, RDS Custom is required to create the customer managed keys (CMK) to encrypt the data at rest using AWS Key Management Service (KMS). This layer of encryption helps customers secure their data from unauthorized access to underlying storage. With support for TDE and CLE, customers can now take advantage of the native SQL Server security features to add additional layers of security for their mission critical workloads.

In this post, we walk through the steps to implement TDE and CLE to protect the user database on RDS Custom DB instance. In addition, we introduce best practices for migrating the TDE and CLE encrypted database to RDS Custom DB instance.

Data encryption at Rest

The following options encrypt data at rest.

Enable transparent data encryption (TDE) – Use this option to encrypt the entire database.

TDE encrypts the SQL Server database’s physical data (mdf) and log (ldf) files stored on the disk using certificate and keys. TDE automatically encrypts data before writing it to disk and decrypts data as it is read from the disk. It also encrypts the native SQL Server database backups performed on the TDE-enabled database. If an unauthorized person gets access to the backup of the database, TDE-enabled backup will prevent them from restoring the database without the appropriate certificate, keys, and passwords.

Enable column-level encryption (CLE) – Use this option to encrypt at the column level

Column-level encryption provides encryption at a more granular level of data which can be applied on all, or selected columns, instead of the entire database. CLE is a great option when you want to encrypt specific columns that store sensitive data such as credit card numbers, SSNs, PHI, or PII data.

Prerequisites

Before you get started, make sure you have the following prerequisites:

  • Prerequisites for setting up RDS Custom for SQL Server
  • An RDS Custom for SQL Server DB instance
  • An Amazon Elastic Compute Cloud (Amazon EC2) Windows instance with SQL Server Management Studio (SSMS) installed

Enable transparent data encryption on the test database

To enable TDE, connect to your RDS Custom instance and complete task with the following steps.

  1. Create the database master key
  2. Create the certificate
  3. Back up the certificate
  4. Create a manual DB snapshot of the RDS Custom instance
  5. Create a test database and populate with test data
  6. Create the database encryption key
  7. Enable transparent data encryption (TDE) on the database.
  8. Verify the encryption status

1. Create the database master key

The database master key protects other keys that are created in the database and must be created in the master database. To create the database master key, do the following:

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<YourPasswordHere>';
GO

For more information about creating a database master key, refer to Create Master Key (Transact-SQL)

2. Create the certificate

Next, create the certificate using the following Transact-SQL statement.

CREATE CERTIFICATE MyRDSCustomCert WITH SUBJECT = 'My RDS Custom Certificate';
GO

For more information about creating the certificate , refer to Create certificate ( Transact-SQL).

3. Back up the certificate

After you create the certificate, you should backup the certificate immediately to a secure location. This certificate is critical and will be required if you choose to restore the database to another server. Back up the certificate using the following Transact-SQL statement.

BACKUP CERTIFICATE SSNCert
TO FILE = 'D:\rdsdbdata\Backup\SSNCert' 
WITH PRIVATE KEY   
(  
FILE = 'D:\rdsdbdata\Backup\SSNCert_PrivateKeyFile',  
ENCRYPTION BY PASSWORD = 'YourStrongPasswordHere'  
);

For more information about backing up the certificate , refer to Backup Certificate (Transact-SQL).

4. Create a manual DB snapshot of the RDS Custom instance

It is recommended to take a manual DB snapshot of your RDS Custom for SQL Server instance after certificate is created to ensure Certificates are backed up. You can create a DB snapshot using the console or the AWS CLI.

Below is an example for creating a DB snapshot using AWS CLI. Replace the instance name and snapshot name to match with your instance.

aws rds create-db-snapshot  --db-instance-identifier <my-rdscustom-mssql-instance>  --db-snapshot-identifier <my-rdscustom-mssql-snapshot>

5. Create a test database and populate with test data

Create a test database on RDS Custom DB instance to enable encryption.

--Creating the test database
CREATE DATABASE testdb;
Go
USE testdb;
GO
--Creating table 
CREATE TABLE patientinfo
(FirstName VARCHAR(100),
LastName VARCHAR (100),
State VARCHAR (2),
SSN VARCHAR (10),
DOB DATETIME)
GO

-- Populating the tables with records
INSERT INTO patientinfo VALUES('Tom','davis','NJ', 5641434802,'7-1-2009')
INSERT INTO patientinfo VALUES('Shelly','wilson','NY', 3701179724,'6-30-1988')
INSERT INTO patientinfo VALUES('steph','williams','CT', 7927828402,'10-29-1998')
INSERT INTO patientinfo VALUES('steph','williams','OH', 5699867456,'11-3-1995')
INSERT INTO patientinfo VALUES('kevin','brown','NH', 4807085926,'4-19-1991')

-- Viewing the records in the table
SELECT * FROM patientinfo

6. Create the database encryption key

Next, create a database encryption key in the user database by indicating the type of encryption algorithm to use and the certificate created in the previous step.

USE [testdb]
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE [MyRDSCustomCert] 
GO

For more information about creating a database encryption key, refer to CREATE DATABASE ENCRYPTION KEY (Transact-SQL)

7. Enable transparent data encryption (TDE) on the database

Now that the certificate and database encryption is configured, turn on the encryption for the user database by using the following Transact-SQL statement.

USE master;
GO
ALTER DATABASE testdb
SET ENCRYPTION ON;
GO

8. Verify the encryption status

Finally, verify the encryption status by running the following query.

Encryption State should show 3 for TempDB and testdb databases as shown.

SELECT DB_NAME(database_id) AS DatabaseName, 
Encryption_State, Create_Date, Key_Algorithm 
FROM sys.dm_database_encryption_keys

For more information about how to check the status of encryption, refer to TDE and transaction logs.

Enable column-level encryption

To enable CLE, connect to your RDS Custom instance and complete task with the following steps.

  1. Create the database master key
  2. Create the certificate
  3. Back up the certificate
  4. Create symmetric key
  5. Review the certificate and symmetric key
  6. Add new column SSN_ENC to enable column-level encryption
  7. Move the data from unencrypted column SSN to encrypted column SSN_ENC
  8. Decrypt and read the column data

1. Create the database master key
The database master key protects other keys that are created in the database and must be created in the master database. To create the database master key, do the following:

use testdb
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong password>';
GO

2. Create the certificate
Next, we will create the certificate using the following Transact-SQL statement.

use testdb
GO
CREATE CERTIFICATE SSNCert WITH SUBJECT = 'This certificate will be used for CLE';
GO

3. Back up the certificate
Next, you should backup the certificate to a secure location after creating the certificate. This certificate is critical and will be required if you choose to restore the database to another server. Back up the certificate using the following Transact-SQL statement.

BACKUP CERTIFICATE MyRDSCustomCert
TO FILE = 'D:\rdsdbdata\Backup\MyRDSCustomCert' 
WITH PRIVATE KEY   
(  
FILE = 'D:\rdsdbdata\Backup\MyRDSCustomCert_PrivateKeyFile',  
ENCRYPTION BY PASSWORD = 'YourStrongPasswordHere'  
);

For more information on backing up the certificate , refer to Backup Certificate (Transact-SQL).
4. Create symmetric key
Now, generate a symmetric key using the following Transact-SQL statement.

USE testdb
GO
CREATE SYMMETRIC KEY SSNKey 
       WITH ALGORITHM = AES_256
       ENCRYPTION BY CERTIFICATE SSNCert;
GO

For more information about creating a symmetric key, refer to CREATE SYMMETRIC KEY (Transact-SQL)
5. Review the certificate and symmetric key
Next, you can review the certificate and symmetric key using the following Transact-SQL statement.

SELECT name CertName,
       certificate_id CertID,
	pvt_key_encryption_type_desc EncryptType,
	issuer_name Issuer
FROM sys.certificates;


SELECT name KeyName,
 	symmetric_key_id KeyID,
	key_length KeyLength, 
	algorithm_desc KeyAlgorithm
FROM sys.symmetric_keys;


6. Add new column SSN_ENC to enable column-level encryption
Now, edit the table to include a new column SSN_ENC using the Transact-SQL statement.

ALTER TABLE patientinfo
ADD SSN_ENC varbinary(MAX)

SELECT * FROM patientinfo


7. Move the data from unencrypted column SSN to encrypted column SSN_ENC
Use the new column SSN_ENC to store encrypted data from column SSN by doing the following.

-- Open the symmetric key with which to encrypt the data. 
OPEN SYMMETRIC KEY SSNKey DECRYPTION BY CERTIFICATE SSNCert;

-- Encrypt the value in column SSN using the  
-- symmetric key SSNKey.  
-- Save the result in column SSN_ENC.
UPDATE patientinfo SET SSN_ENC = EncryptByKey (Key_GUID('SSNKey'), SSN) FROM patientinfo;
GO

CLOSE SYMMETRIC KEY SSNKey;
GO

8. Decrypt and read the column data
Finally, decrypt and try to read the column data to validate all three columns unencrypted SSN, Encrypted SSN column, and Decrypted SSN column.

-- Verify the encryption.  
-- First, open the symmetric key with which to decrypt the data. 
OPEN SYMMETRIC KEY SSNKey DECRYPTION BY CERTIFICATE SSNCert;

-- Now list the original SSN, the encrypted SSN_ENC column,  
-- and the decrypted ciphertext. If the decryption worked,  
-- the original SSN and SSN_ENC will match the decrypted number. 
SELECT FirstName,LastName,SSN,SSN_ENC AS 'Encrypted SSN',
 CONVERT(varchar, DecryptByKey(SSN_ENC)) AS 'Decrypted SSN'
 FROM patientinfo;

CLOSE SYMMETRIC KEY SSNKey;
GO

Migrate TDE/CLE encrypted database to RDS Custom

When migrating TDE/CLE enabled databases from an on-premises or a self-managed environment to RDS Custom DB instance, you must also copy the backup of the certificate file and the private key file used to encrypt the source database. When you have finished copying both the certificate and private key files to RDS Custom DB instance, do the following:

  1. Create the database master key on RDS Custom DB instance.
  2. Import the certificate with private key into RDS DB instance.
  3. Create a manual DB snapshot of the RDS Custom instance.
  4. Perform backup from source on-premises and restore to RDS Custom DB instance.

Create a database master key on RDS Custom instance if you don’t already have one by doing the following:

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<YourNewOROLDPasswordHere>';
GO

Import the server certificate from the original server certificate backup file and private key file as shown in the following example:

CREATE CERTIFICATE MyRestoredRDSCustomCert
FROM FILE = 'D:\rdsdbdata\Backup\Source_SQL_Server_certificate.cer' 
WITH PRIVATE KEY   
(  
FILE = 'D:\rdsdbdata\Backup\Source_SQL_Server_private_key.pvk',  
DECRYPTION BY PASSWORD = 'YourStrongPasswordHere'  
);

Create a manual DB snapshot of the RDS Custom instance. Below is an example command for creating a DB snapshot using AWS CLI.

aws rds create-db-snapshot  --db-instance-identifier <my-rdscustom-mssql-instance>  --db-snapshot-identifier <my-rdscustom-mssql-snapshot>

Restore the database using the backup file copied from the source server.

You’ll receive an error message like the following if you attempted to restore an encrypted database without first restoring the Certificate and private key files.

Best practices and considerations

Now that we’ve shown you the steps to implement TDE and CLE, consider the following best practices.

  • RDS Custom manages the service master key on your behalf, so you don’t have to take any additional steps to manage the service master key. Do not regenerate the service master key on your RDS Custom instance. In case a service master key is regenerated on your instance, it should be followed by a manual DB snapshot.
  • It is also recommended to take a manual DB snapshot after creating the certificate to enable TDE or CLE.
  • After you enable TDE, you should immediately back up the certificate and its associated private key. If you need to restore the encrypted database to another server, you must have the backup of the certificate and private key. Otherwise, you can’t open the database.
  • When TDE is enabled on a single user database, the TempDB is encrypted as well. This encryption could impact the performance on other non-encrypted databases on the same instance. You should monitor the storage performance before and after enabling TDE to understand the impact.

Conclusion

In this post, we showed how you can use TDE and CLE on your databases deployed on RDS Custom for SQL Server. We also discussed the best practices you should follow when implementing encryption on RDS Custom for SQL Server. You can now migrate your TDE and CLE encrypted databases to RDS Custom using the native SQL Server backup and restore method.

About the authors

Mesgana Gormley is a Senior Database Specialist Solution Architect at Amazon Web Services. She works on the Amazon RDS team providing technical guidance to AWS customers and helping them migrate, design, deploy, and optimize relational database workloads on AWS.

Sudhir Amin is a Database Specialist Solutions Architect at Amazon Web Services. In his role based out of New York, he provides architectural guidance and technical assistance to enterprise customers across different industry verticals, accelerating their cloud adoption. He is a big fan of snooker, combat sports such as boxing and UFC, and loves traveling to countries with rich wildlife reserves where he gets to see world’s most majestic animals up close.