Testing the operation of “transparent” (TDE) encryption 1C based on DBMS MSSQL 2022

4000 MHz (1 core)

Or set the settings in the screenshot below:

Note:

• These settings are only applicable to X9 series motherboards. In other cases, the settings are individual.

• When using the Custom mode, we will end up with a fixed frequency of 3.3 GHz on all cores in constant mode.

Part 2. Software optimization. 1C Server.

Part 3. Optimization of the software part. MS SQL server.

These instructions will be enough for basic SQL server setup.

Part 4. Enabling Transparent Data Encryption (TDE).

Description of technology.

Transparent Data Encryption performs real-time I/O encryption and decryption for data and log files. Encryption uses a database encryption key (DEK). The database boot record stores the key for availability during recovery. The DEK is a symmetric key protected by a certificate stored in the master server database or an asymmetric key protected by an EKM.

The description of TDE technology is well described in the official blog of Microsoft. We will comment on some features of this instruction.

https://learn.microsoft.com/ru-ru/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-ver16

Database file encryption is performed at the page level. Pages in an encrypted database are encrypted before they are written to disk and decrypted when they are read into memory.

If we analyze this quote, it becomes clear that, theoretically, if an attacker gains administrator rights to an active server, then by copying database data directly from RAM it is possible to obtain unencrypted data.

While Microsoft's blog doesn't explicitly mention the performance penalty when TDE is enabled, we found some references that the loss is between 3 and 5 percent, and in some studies, up to 28 percent. Let's test this theory with load testing.

If we talk about the details of the test, then within the framework of our research we will not generate new tables and enter new data, the basis of the research is the operations of conducting, re-conducting and reading from the database, which satisfies the principles of TDE encryption.

Enabling encryption:

Step 1 – Create a master key.

USE master

go

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'

Step 2 – Create a certificate.

CREATE CERTIFICATE DEK_EncCert WITH SUBJECT = 'DEK Encryption Certificate'

Step 3 – create a backup of the certificate and key.

BACKUP CERTIFICATE DEK_EncCert

TO FILE ='C:\certs\DEK_EncCert.cert'

WITH PRIVATE KEY(

FILE = 'C:\certs\DEK_EncCert.prvk',

ENCRYPTION BY PASSWORD = 'password'

);

BACKUP MASTER KEY TO

FILE = 'C:\certs\master_key.bak'

ENCRYPTION BY PASSWORD = 'password'

Step 4 – create a Database Encryption Key (DEK) in the database.

USE MySecretDB

go

CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE DEK_EncCert

Step 5 – Enable encryption for the database.

ALTER DATABASE ERP SET ENCRYPTION ON

Step 6 – Check the encryption status.

SELECT DB_NAME(database_id), encryption_state, percent_complete FROM sys.dm_database_encryption_keys