Set up Always Encrypted with Amazon RDS for SQL Server

Customers within a variety of industries (healthcare and life sciences, financial services, and retail, among others) require a stronger security posture. Particularly, if the databases being hosted contain sensitive data such as credit card numbers or national identification numbers (for example, US Social Security numbers), providing a solution that allows you to encrypt this data and control access to database administrators becomes crucial. Always Encrypted is Microsoft SQL Server’s answer to this security requirement.

Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the database engine. This provides a separation between those who own the data and can view it, and those who manage the data but shouldn’t have access: database administrators, cloud database operators, or other high-privileged unauthorized users. As a result, Always Encrypted enables you to confidently store your sensitive data in the cloud, and to reduce the likelihood of data theft by malicious insiders. Always Encrypted makes encryption transparent to applications. An Always Encrypted-enabled driver installed on the client computer achieves this by automatically encrypting and decrypting sensitive data in the client application. The driver encrypts the data in sensitive columns before passing the data to the database engine, and automatically rewrites queries so that the semantics to the application are preserved. Similarly, the driver transparently decrypts data, stored in encrypted database columns, contained in query results.

In this post, we provide you with step-by-step instructions on how to set up Always Encrypted on an Amazon Relational Database Service (Amazon RDS) for SQL Server instance using the Windows Certificate Store. Other key stores available for Always Encrypted (Azure Key Vault, Hardware Security Module) are not supported as of this writing.

Prerequisites

To set up this solution, you need a working environment with the following resources:

• Development host with SQL Server installed (Windows device where the Always Encrypted Certificate will be generated).
• Target application server running Windows and hosted on Amazon Elastic Compute Cloud (Amazon EC2).
• SQL Server 2016 or higher instance (RDS for SQL Server instance).
• Amazon Simple Storage Service (Amazon S3) bucket to store the certificate file.
• Always Encrypted certificate.

To generate a certificate for the column master key using a local SQL Server instance running on your development machine, complete the following steps:

1. With SQL Server Management Studio (SSMS), open the folder Column Master Keys under the Security node of the database you are planning to encrypt, right-click on the “Column Master Key” folder, and select the “New Column Master Key…” option, as shown.
   
2. Store the certificate in the Windows Certificate Store – Current User key store.
   
3. Locate the Always Encrypted certificate and take note of the thumbprint (choose the certificate to open the details dialog box) using the Certificates option on the Microsoft Management Console (MMC). You will need this thumbprint for the scripts we develop to automate the process. If you haven’t accessed the Certificates Snap-in before, follow the detailed steps included here.
   • Open the Microsoft Management Console (MMC).
   • Navigate to the File Menu and select the option Add or Remove Snap-ins.
   • Select the Certificates option from the available snap-ins listed.
   • Navigate to the Personal\Certificates folder.
   • Double-click on Always Encrypted Certificate.
   • Select the Details tab, scroll down, and select the Thumbprint property.

   

4. Export the certificate to a PFX file by choosing Copy to File, as shown in the following dialog box.
   
5. Choose Next on the Certificate Export Wizard dialog box.
6. Select the option “Yes, export the private key” and then choose Next.
7. Select the Personal Information Exchange (.PFX) format option and then choose Next.
8. Type-in a password, select the “AES256-SHA256” encryption option, and then choose Next.
9. Specify the appropriate path and file name; choose Next.
10. Choose Finish to complete the certificate export process.
11. Upload this file to the designated S3 bucket.

Configure Always Encrypted on an RDS for SQL Server instance

Once all pre-requisite steps have been completed and you have been able to successfully export and Always Encrypted certificate to a PSX file, you are ready to configure Always Encrypted on your Amazon RDS for SQL Server instance. In order to do so, complete the following steps:

1. Connect remotely to the target application server (Windows EC2 client) leveraging Microsoft Remote Desktop Protocol Client (RDP).
2. Download the PSX file with the certificate from Amazon S3 to a local folder.
3. Import the certificate into the target application server.
4. Connect to Amazon RDS for SQL Server using SQL Server Management Studio (SSMS) with the Always Encrypted option enabled as shown below.
   
5. Open a new query window and adjust the query options to enable parameterization for Always Encrypted.
   
6. Create a new database to host some encrypted data:

   USE master; 
   GO
   
   IF DB_ID('AlwaysEncrypted') IS NULL
   CREATE DATABASE [AlwaysEncrypted];
   GO

7. Create the column master key, referencing that certificate thumbprint we captured earlier:

   CREATE COLUMN MASTER KEY [AE_ColumnMasterKey]
   WITH
   (
          	KEY_STORE_PROVIDER_NAME = 'MSSQL_CERTIFICATE_STORE',
          	KEY_PATH = 'CurrentUser/My/a619fb0c4029cfcd9d9e935dbb8dc98e0902000f',
   );
   GO

8. Create a column encryption key. One option is to leverage SQL Server Management Studio GUI. In this case, locate the “Column Encryption Keys” folder under the “Always Encrypted Keys” node, right-click on the folder and select the option “New Column Encryption Key…”. Type-in an appropriate name for your column encryption key, select the column master key created above and click “OK”. Alternatively, you can leverage the T-SQL script below. Make sure to adjust the encrypted value of the certificate shown in the following code to match the certificate you are actually using in your environment:

   CREATE COLUMN ENCRYPTION KEY [AE_ColumnEncryptionKey]
   WITH VALUES
   (
          	COLUMN_MASTER_KEY = [AE_ColumnMasterKey],
          	ALGORITHM = 'RSA_OAEP',
          	ENCRYPTED_VALUE = 0x016E000001630075007200720065006E00740075007300650072002F006D0079002F006100360031003900660062003000630034003000320039006300660063006400390064003900650039003300350064006200620038006400630039003800650030003900300032003000300030006600374AEE2DA655985A4822614237F61F217171DD13882CE89120BC7B2D5DDD6863668EC2EFE24A64E55ADA2E8A52B0F1E758CD3717157E6612784BB21DE65FDD005322EAA78BC96EA9CB833F5F73E1DD859DB0AE92A6F9D272DEDF53934AF9B43445A01E9FBDBDEF5FCD68087D4EDE85D7479F2BE3D21E401CEABBC6630C004BE1A4BD29BBE2167850F2A08F688BD4AA430F73959D934B44412C62E8EE63D2949B31A1AA07EC80248E1351CF53F5E0C94A142FBE05817A2DEBA87E191B158A748F8925854E52EEBC5D0D620C9BE9BB8157880105A2108F4409B30EE8E8FBF5B51B8C2A884F69B08C568709176A8F9DC1C56E005363BFB2E8E0C5FB27C17551A447E5626432546249839A5AAF334371004BD105F553F5FFCB138C83B4AF54F62163FC08825365B11B0888AF1DB2487C41FBFFE6138C0091500C2B3AD5D3326D36504F5D00C1725C4418263C8D2943BF1DD93B0454DF952975AB795191CF309154B7B6430E55725BF1FC0529C3617B3D44B4A25B983C75339ED999C0143137BB728EB0ACD2878C1DB5780513F456AA50334913931F777297B2EFA42DC5916CCD4F01D05D25F654E65058DED9BF45BE712036AD57A627A8011B14B6406B4C5459C8A7A41D92957C364997FFFDC016A0A64923B1F5794819186443D891F5C534805FDF12EFFF65BCC60FBD4757D9F06E7727C50FE3F5EF440B80292E3AB7536CF09D98
   );
   GO

9. Verify that encryption was properly set up (a sample output is included for your reference):

   USE [AlwaysEncrypted];
   GO
   
   select * from sys.column_master_keys;
   select * from sys.column_encryption_keys;
   select * from sys.column_encryption_key_values;
   GO

   

10. Create a table with encrypted columns. The following sample includes both options for a column encryption key: deterministic and randomized. In general, the most used option is deterministic because it’s significantly faster, but in some use cases, randomized is the better option (when the cardinality of the column is very low, for example). It is important to note in the T-SQL statement below that for the deterministic encryption option on a string column, a binary-code point collation (_BIN2) is required.

    IF OBJECT_ID('dbo.CustomerInfo', 'U') IS NOT NULL
        DROP TABLE dbo.CustomerInfo;
    GO
    
    CREATE TABLE dbo.CustomerInfo
    (
    CustomerId INT IDENTITY(10000,1) NOT NULL PRIMARY KEY,
    CustomerName NVARCHAR(100) COLLATE Latin1_General_BIN2
        ENCRYPTED WITH (
            ENCRYPTION_TYPE = DETERMINISTIC, 
            ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', 
            COLUMN_ENCRYPTION_KEY = AE_ColumnEncryptionKey
        ) NOT NULL,
    CustomerPhone NVARCHAR(11) COLLATE Latin1_General_BIN2
        ENCRYPTED WITH (
        ENCRYPTION_TYPE = RANDOMIZED, 
        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', 
        COLUMN_ENCRYPTION_KEY = AE_ColumnEncryptionKey
        ) NOT NULL
    );
    GO

11. Query the recently created table:

    SELECT TOP 3 * FROM dbo.CustomerInfo WITH(NOLOCK);
    GO

12. Insert a few records. In this sample, it’s important to keep the insert operations as single statements, otherwise SSMS will throw an error.

    DECLARE @CName AS nvarchar(100) = 'CustomerA', @CPhone AS nvarchar(11) = '12123330988'; INSERT INTO [dbo].[CustomerInfo] VALUES (@CName, @CPhone);
    GO
    
    DECLARE @CName AS nvarchar(100) = 'CustomerB', @CPhone AS nvarchar(11) = '14152220786'; INSERT INTO [dbo].[CustomerInfo] VALUES (@CName, @CPhone);
    GO
    
    DECLARE @CName AS nvarchar(100) = 'CustomerC', @CPhone AS nvarchar(11) = '19255550484'; INSERT INTO [dbo].[CustomerInfo] VALUES (@CName, @CPhone);
    GO

13. Query the table again:

    SELECT TOP 3 * FROM dbo.CustomerInfo WITH(NOLOCK);
    GO

Note that the values are unencrypted. If you run this query from any other client that doesn’t have the certificate deployed, you will only see the encrypted values. A possible test is to remove the certificate from the client and then run the query again.

Conclusion

In this post, we demonstrated how to set up Always Encrypted on an Amazon RDS for SQL Server instance using the Windows Certificate Store. It is important to keep in mind that Microsoft SQL Server Always Encrypted has been released with a number of limitations and they still apply once you have setup this feature on your Amazon RDS for SQL Server instance. Should you not need to use the resources created following along the instructions included in this post, please remember to clean-up your environment: delete the Amazon RDS for SQL Server instance created during the exercise, delete the Amazon S3 bucket used to host the PSX certificate file, and terminate the Amazon EC2 instance used as the target application server (Always Encrypted Client).

If you have any questions, comments, or feedback, please leave them in the comments section of this post.

About the Author

Camilo Leon is a Principal Solutions Architect at AWS specialized on databases and based off San Francisco, California. He works with AWS customers to provide architectural guidance and technical support for the design, deployment and management of their AWS relational database workloads and business applications. In his spare time, he enjoys mountain biking, photography, and movies.