Enable data at rest encryption

Introduction

You are able to encrypt your data such that users with access to the database will see ciphertext in the DB columns, while authorized users accessing the data in Cinchy will see it as plain text. To do so, your database administrator will need to create a Database Master Key.

note

When migrating an encrypted database from one AWS RDS instance to another, it is crucial to regenerate the encryption keys to ensure that encrypted data can be accessed correctly. Review the section below for instructions fon regenerating encryption keys after restoring a database to a new server.

Enable Data at Rest Encryption

1. Create the Master Key in the database
   1. As the administrator account, connect to the database that runs your Cinchy instance.
   2. Run the below query to create the key. Ensure that the password you choose adheres to your organization's password policy:

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
2. Validate that you can encrypt data by navigating to Design Table > Columns > Show Advanced.

Back Up the Master Key

After you have created your master key you can create a backup file of that key in case any data corruption occurs in future.

You will need the password you used to create your master key as well as the path to where you want to store the backup.

1. Run the following command:

BACKUP MASTER KEY TO FILE = 'path_to_file'   
    ENCRYPTION BY PASSWORD = 'password' 

Further documentation on creating a backup master key can be found here.

Restore master key

In the use case where you require to restore your master key due to data corruption, you may use the following steps.

You will need the password you used to create you master key and the path you specified when you backed it up.

1. Run the following command:

RESTORE MASTER KEY FROM FILE = 'path_to_file'   
    DECRYPTION BY PASSWORD = 'password'  
    ENCRYPTION BY PASSWORD = 'password'  
    [ FORCE ] 

Further documentation on restoring the master key can be found here.

Migrating an Encrypted Database

When migrating an encrypted database from one AWS RDS instance to another, it is crucial to regenerate the encryption keys to ensure that encrypted data can be accessed correctly. This document provides step-by-step instructions for regenerating encryption keys after restoring a database to a new server.

SQL Server uses encryption keys to encrypt and decrypt data in tables. When a database is restored to a different server, the encryption keys must be regenerated to match the new server's security context. Failing to do so will result in errors when trying to access tables with encrypted columns.

Steps for Regenerating Encryption Keys

1. Run the below query to open and decrypt the Master Key using the password that was used when creating the key originally.

OPEN MASTER KEY DECRYPTION BY PASSWORD = '<password>';

2. Regenerate the Master Key with a new password. This ensures that the Key is re-encrypted in the context of the new server.

ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = '<password>';

3. Close the Master Key to secure the database by running the below command. (Closing the Key ensures that it is no longer available for operations that require decryption)

CLOSE MASTER KEY;

4. Once the encryption keys have been regenerated, verify that the tables containing encrypted columns can be accessed correctly. This can be done through the SQL Server Management Studio (SSMS) or any database management tool you use.

For more information, review the documentation here