Enabling Transparent Data Encryption 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 (DB) (typically named SO_<systemname>).

To maintain the encryption of WhatsApp responses whilst they are in the Apteco platform, the Apteco Social DB must be encrypted to protect the data whilst ‘At rest’ in this system.

If you hold a copy of WhatsApp message data outside of WhatsApp, such as in a FastStats build or exported to list for an ESP, then please consider the security of that copied data and what access controls are needed.

The form of encryption to be used is Transparent Data Encryption (TDE).

Enabling this encryption ensures that you have a Database Master Key and Database Encryption Certificate for your database. The link above has full information on TDE.

The steps you need to go through to ensure your social database is encrypted vary depending on your setup, but an example set of steps is shown below.

Note: You should only follow these steps if you understand them fully, are authorised to do so, and are aware of the implications involved.

To encrypt a social database:

Check if the database is already encrypted.

Run the following SQL query to report whether the database is encrypted or not (replacing <DatabaseName> with the name of your database). If this reports that it is encrypted or in the process of being encrypted then you don’t need to do anything further.

Copy

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>';

Create a Database Master Key, if required.

Check if you have a Database Master Key by running the following SQL (using a user that has the appropriate rights to the system views involved):
Copy
```
SELECT * 

FROM master.sys.symmetric_keys 

WHERE name = '##MS_DatabaseMasterKey##';
```
If a master key exists you can skip to step 3, otherwise create one with the following command:
Copy
```
USE master; 

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<StrongPasswordHere>';
```
As described in the TDE documentation (linked to above) you should create a backup of your master key, as if this is lost your data will not be retrievable.
Copy
```
BACKUP MASTER KEY TO FILE = '<MasterKeyFileBackupPath>' 

ENCRYPTION BY PASSWORD = '<PasswordForBackup>';
```
Create a Database Encryption Key Certificate, if required.

Run the following command to see what certificates you have available.
Copy
```
SELECT * FROM master.sys.certificates;  
```
If there is one there that you want to use then skip to step 4 (and use the name of the certificate in place of MyServerCert in the following steps). Otherwise create one with the following command. You may want to change the name of the certificate created from MyServerCert to something else. If so, remember to also change it in the following steps.
Copy
```
USE master; 

CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate';
```
As described in the TDE documentation (linked to above), you should create a backup of your certificate as if this is lost your data will not be retrievable.
Copy
```
BACKUP CERTIFICATE MyServerCert TO FILE = '<CertFileBackupPath>' WITH PRIVATE KEY ( 

FILE = '<PrivateKeyFileBackupPath>', 

ENCRYPTION BY PASSWORD = '<PasswordForBackup>' 

);
```

Create a Database Encryption Key within the Social database to be encrypted.

Copy

USE <DatabaseName>; 

CREATE DATABASE ENCRYPTION KEY

Copy

WITH ALGORITHM = AES_256 

ENCRYPTION BY SERVER CERTIFICATE MyServerCert;

Enable encryption on the database.

Copy

ALTER DATABASE <DatabaseName> 

SET ENCRYPTION ON;

Verify that the database is now encrypted/encrypting.

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 should still be able to read and write to it as normal, but the .mdf and .ldf files (as well as backups created from them) will no longer be read/writeable without the key and certificate.

See the TDE documentation for further implications of this process.