AWS Database Blog

Column-level encryption on Amazon RDS for SQL Server

Amazon Relational Database Service (Amazon RDS) for SQL Server supports column-level data encryption. Column-level encryption provides encryption at a more granular level of data that can be applied on all or selected columns. With column-level encryption, you can define different encryption keys for each different column.

You can use encryption in SQL Server for connections, data, and stored procedures. For more information about encryption, refer to SQL Server Encryption.

In this post, we show you how to implement column-level encryption on Amazon RDS for SQL Server.

SQL Server encryption

Amazon RDS natively provides encryption at rest using AWS Key Management Service (AWS KMS) to protect the underlying storage of database instances, automated backups, read replicas, and snapshots.

Amazon RDS for SQL Server supports Transparent data encryption (TDE), a feature supported on Microsoft SQL Server Enterprise and Standard editions. TDE encrypts sensitive data in a database and uses a certificate to protect the keys that encrypt the data. This solution prevents anyone without the keys from using the data. You must plan this kind of protection in advance. TDE does real-time I/O encryption and decryption of data and log files. The encryption uses a database encryption key (DEK). The database boot record stores the key for availability during recovery. The DEK is a symmetric key. It’s secured by a certificate that the server’s primary database stores or by an asymmetric key that an EKM module protects.

TDE protects data at rest, which is the data and log files. It lets you follow many laws, regulations, and guidelines established in various industries. This ability lets software developers encrypt data by using AES and 3DES encryption algorithms without changing existing applications, and automatically decrypts data when the data is read from storage. To prevent unauthorized decryption, TDE stores the encryption keys in a security module external to the database. If you’re looking to implement a full database-level encryption, then TDE is a good option.

TDE encrypts an entire database, whereas column-level encryption allows for individual columns within a database to be encrypted. Encryption make the data useless without the corresponding decryption key or password. Encryption doesn’t solve access control problems. However, in some the use cases, it enhances security by limiting data loss even if access controls are bypassed. For example, if the database host computer is misconfigured and a hacker obtains sensitive data, that stolen information might be useless if it’s encrypted.

Solution overview

Column-level data encryption in SQL servers is based on an encryption hierarchy. An encryption hierarchy is used to protect the data and encryption keys. The hierarchy levels are as follows:

  • Windows level – This level uses the Windows Data Protection (DP) API to encrypt and protect the next level.
  • SQL Server level – This level contains the Service Master Key (SMK), which is protected by the Windows level. The SMK is used to protect the next level.
  • Database level – This level contains the Database Master Key (DMK) and the remaining keys and certificates. The DMK encrypts and protects the certificates, symmetric keys, and asymmetric keys within the database.

The following figure shows the full encryption hierarchy (architecture reference from Microsoft documentation).

Column-level encryption can be achieved using symmetric keys or asymmetric keys in combination with a certificate or password, respectively. The following diagram shows the high-level view of symmetric and asymmetric column-level encryption.

In the following sections, we show you the implementation of both methods.

Method 1: Column encryption with symmetric keys

The following are the implementation steps for column-level encryption with a symmetric key:

On the RDS instance, verify that SMK exists using the following command:

Use Master go select * from sys.symmetric_keys go

To create a symmetric key for the database, we first need to set up the user database with a primary key and a certificate, which act as protectors of our symmetric key store.

  1. Create a database-level primary key: 
    use <dbname> CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘<password>’
  2. Create a certificate: 
    use <dbname>
CREATE CERTIFICATE <certificate-name> WITH SUBJECT = 'A label for this certificate'
  3. Create your symmetric key: 
    use <dbname>
CREATE SYMMETRIC KEY <key-name>
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE <certificate-name>;
GO
  4. Encrypt the column: 
    use <dbname>
OPEN SYMMETRIC KEY <key-name>
DECRYPTION BY CERTIFICATE <certificate-name>;
UPDATE <table-name>
SET <encrypted-column-name> = EncryptByKey(Key_GUID('<key-name>’), <column-name>);
  5. Decrypt the column: 
    use <dbname>
OPEN SYMMETRIC KEY <key-name>
DECRYPTION BY CERTIFICATE < certificate-name>;
GO
SELECT
CONVERT(varchar, DecryptByKey(<encrypted-column-name>)) AS
'Decrypted-column’ FROM <table-name>;
GO

Method 2: Column encryption with asymmetric keys

The following are the implementation steps for column-level encryption with an asymmetric key:

On the RDS instance, verify the SMK, already created in Amazon RDS during the instance launch:

Use Master
   go
   select * from sys.symmetric_keys
   go

To create an asymmetric key for the user database, you first need to set up your database with a primary key and then create asymmetric keys by encrypting them with a password:

  1. Create a database-level primary key: 
    use <dbname>
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘<key-password>’
  2. Create your asymmetric key: 
    use <dbname>
IF NOT EXISTS (SELECT *
FROM sys.asymmetric_keys
WHERE name = '<Asym-key-name>')

BEGIN
CREATE ASYMMETRIC KEY <Asym-key-name>
WITH ALGORITHM = RSA_2048
ENCRYPTION BY PASSWORD = '<Asym-key-password>' ;
END
GO
  3. Encrypt the column: 
    use <dbname>
UPDATE <table-name>
SET <encrypted-column-name> = ENCRYPTBYASYMKEY(ASYMKEY_ID('<Asym-key-name>'), <column-name>)
GO
  4. Decrypt the column: 
    use <dbname> 
SELECT *, <Decrypted-column-name> = CONVERT(CHAR(11),DECRYPTBYASYMKEY(ASYMKEY_ID ('<Asym-key-name>'), <encrypted-column-name>, N'<Asym-key-password>’)) FROM <table-name>; 
GO

Summary

In this post, you learned how to implement column-level encryption on Amazon RDS for SQL Server. Although encryption is a valuable technology to help ensure security, it should not be considered for all data or connections. When you’re deciding whether to implement encryption, consider how users will access the data. If users access the data over a public network, data encryption is strongly recommended to increase security. However, if all access involves a secure intranet configuration, encryption might not be required. Any use of encryption should also include a maintenance strategy for passwords, keys, and certificates.

If you have any comments or questions, share them in the comments section.

About the Authors

Kiran Mulupuru is a Database Specialist Technical Account Manager at Amazon Web Services. She focuses on Amazon RDS and Amazon Aurora databases. She works with enterprise customers, providing technical assistance on database operational performance and sharing database best practices.

Lakshman Thatisetty is a Database Specialist Solutions Architect with Amazon Web Services. He works with AWS customers, designing customer solutions on database projects, and helping them migrate and modernize their existing databases to the AWS Cloud as well as orchestrate large-scale migrations in AWS.