Transparent Data Encryption on a Social database

The Apteco Social database (typically named SO_<systemname>) holds data from the Meta platform. This includes responses to WhatsApp messages received via the WhatsApp endpoints in the Orbit Responses API.

To maintain encryption of WhatsApp responses whilst they're in the Apteco platform, you must encrypt the Social database to protect the data at rest. You might hold WhatsApp message data outside WhatsApp, for example in a FastStats build or a list exported for an ESP. If so, consider the security of that copied data, and what access controls you need.

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 the query reports that SQL Server has encrypted the database, or is still encrypting it, you don't need to take further action.

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 you lose it, your data won't 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 you lose it, your data won't 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 SQL Server has encrypted the database, or is still encrypting it. Wait for the process to complete.

Once SQL Server encrypts the database, you can still read and write to it as normal. However, the .mdf and .ldf files, and any backups created from them, are no longer readable without the key and certificate.

See the TDE documentation for further implications of this process.