TDE on a Social database

Data from the Meta platform—including responses to WhatsApp messages received via the WhatsApp endpoints in the Orbit Responses API—is held within the Apteco Social database (typically named SO_<systemname>).

To maintain the encryption of WhatsApp responses whilst they are in the Apteco platform, the Social database must be encrypted to protect the data at rest. If you hold a copy of WhatsApp message data outside of WhatsApp (such as in a FastStats build or exported to a list for an ESP), consider the security of that copied data and what access controls are needed.

The form of encryption to use is Transparent Data Encryption (TDE). Enabling this encryption creates a Database Master Key and Database Encryption Certificate for your database.

Encrypt a social database¶

Step 1—Check if the database is already encrypted¶

Run the following SQL query to report the encryption state of your database (replace <DatabaseName> with the name of your database). If this reports that it is encrypted or in the process of being encrypted, no further action is needed.

SELECT
    DB_NAME(database_id) AS database_name,
    encryption_state,
    encryption_state_desc = CASE encryption_state
        WHEN '0' THEN 'No database encryption key, no encryption'
        WHEN '1' THEN 'Unencrypted'
        WHEN '2' THEN 'Encryption in progress'
        WHEN '3' THEN 'Encrypted'
        WHEN '4' THEN 'Key change in progress'
        WHEN '5' THEN 'Decryption in progress'
        WHEN '6' THEN 'Protection change in progress'
        ELSE 'No Status'
    END,
    percent_complete,
    encryptor_thumbprint,
    encryptor_type
FROM sys.dm_database_encryption_keys
WHERE DB_NAME(database_id) = '<DatabaseName>';

Step 2—Create a Database Master Key (if required)¶

Check whether a Database Master Key already exists:

SELECT *
FROM master.sys.symmetric_keys
WHERE name = '##MS_DatabaseMasterKey##';

If a master key exists, skip to step 3. Otherwise, create one:

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

As described in the TDE documentation, create a backup of your master key—if it is lost, your data will not be retrievable:

BACKUP MASTER KEY TO FILE = '<BackupFilePath>'
ENCRYPTION BY PASSWORD = '<StrongPassword>';

Step 3—Create a Database Encryption Key Certificate (if required)¶

View existing certificates:

SELECT * FROM master.sys.certificates;

If you want to use an existing certificate, skip to step 4 and substitute its name for MyServerCert in the following steps. Otherwise, create a new certificate:

USE master;
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate';

Back up the certificate—if it is lost, your data will not be retrievable:

BACKUP CERTIFICATE MyServerCert TO FILE = '<CertFilePath>'
WITH PRIVATE KEY (
    FILE = '<PrivateKeyFilePath>',
    ENCRYPTION BY PASSWORD = '<StrongPassword>'
);

Step 4—Create a Database Encryption Key within the Social database¶

USE <DatabaseName>;
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;

Step 5—Enable encryption on the database¶

ALTER DATABASE <DatabaseName>
SET ENCRYPTION ON;

Step 6—Verify encryption¶

Run the same query from step 1. You should now see that the database is encrypted or in the process of being encrypted. Wait for the process to complete.

Once the database is encrypted, you can still read and write to it as normal, but the .mdf and .ldf files (and backups created from them) will no longer be readable without the key and certificate.

See the TDE documentation for further implications of this process.