Table of Contents

Data Encryption in Database

1. Introduction

Data security is a critical requirement in any modern information system, especially in environments governed by regulations such as the GDPR. This document presents a structured approach to encrypting data in relational databases using the Entity Framework Core (EF Core) ORM. It emphasizes best practices, the algorithms used, and the technical and functional implications of their integration.

2. Types of Encryption

2.1 Symmetric Encryption

Symmetric encryption relies on the use of a single key for both encryption and decryption. It is particularly suitable for data storage due to its execution speed and ease of implementation.

Examples: AES, XChaCha20

2.2 Asymmetric Encryption

Asymmetric encryption uses a key pair: a public and a private key. Although slower, it is appropriate for use cases such as secure key exchange or digital signatures.

Examples: RSA, ECC

Note: In application contexts, symmetric encryption is preferred for database storage due to its favorable performance-to-security ratio.

3. Algorithm: ChaCha20-Poly1305

Choosing the encryption algorithm is crucial. ChaCha20-Poly1305 is an authenticated encryption algorithm (AEAD) known for its robustness and modern design.

3.1 Advantages

  • Well-established security: used in numerous reference projects.
  • Extended nonce (192 bits): minimizes risks related to accidental reuse.
  • High performance, even on platforms without hardware acceleration (unlike AES-GCM).
  • Simple implementation, reducing errors related to nonce or padding handling.
  • Authenticated encryption: ensures both confidentiality and integrity of data.
  • Regulatory compliance: meets GDPR requirements and ANSSI recommendations.

4. Best Implementation Practices

Integrating encryption into an application system requires adherence to a set of best practices:

  • Secure key storage:
    • Development: via Windows user secrets.
    • Production: via secret management systems such as Kubernetes Secrets.
  • Use of a unique nonce or salt for each encryption operation.
  • Authenticated encryption (AEAD) is strongly recommended.
  • Avoid logging sensitive data before or after encryption.
  • Include unit tests to verify encryption reversibility.
  • Key rotation is recommended though not mandatory.

For configuring the secrets for the encryption key you are using. Add the following configuration to the secrets section:

dotnet user-secrets set "EncryptionKey" "[Your_Encryption_Key]" --id [Your_Root_Namespace].AspNetCore
Important

The encryption key must be encoded in base 64 and have a length of 32 bytes.

Note

If no user secrets is provided for the encyption key, a default key wil be use.

5. Integration with Entity Framework Core

5.1 Using a ValueConverter

EF Core supports encryption via the ValueConverter feature. This mechanism centralizes encryption logic at the data access layer.

Warning

Encryption is only supported by String type properties.

Warning

Encryption is not supported by Yaml persistence.

Advantages:

  • Encryption and decryption are transparent to the developer.
  • Business logic remains unchanged:
user.Email = "[email protected]";
  • Compatible with:
    • Migrations (encryption of existing data)
    • LINQ queries (excluding queries on encrypted fields)

6. Technical Limitations

6.1 SQL Queries

The following operations are not supported on encrypted columns:

  • WHERE
  • LIKE
  • ORDER BY
  • JOIN

This disables indexing, sorting, or direct filtering on protected fields.

6.2 Performance

  • Encryption and decryption incur a non-negligible CPU cost.
  • May slow down read/write operations.
  • Recommendation: encrypt only sensitive fields.

7. Database Storage Impact

Encryption increases data size due to:

  • The addition of a nonce
  • Message authentication
  • Encoding (e.g., Base64)

Size Comparison:

Plaintext Data Encrypted (Binary) Encrypted (Base64)
10 bytes ~50 bytes ~68 characters
100 bytes ~140 bytes ~188 characters
1 KB ~1040 bytes ~1420 characters

Implications

  • Use nvarchar(max) or text types for encrypted fields.
  • Avoid strict length constraints.
  • Anticipate impact on total data volume.

8. Use Case: Encrypting the ConnectionString

Important

Starting from version 3.1, the plaintext data column has been removed from the DatabaseServer entity. Only the encrypted version of the connection string is persisted. The PlainTextConnectionString entity property and its plaintext data column no longer exist, and the migration/synchronization mechanism described below only applies to versions prior to 3.1.

Encrypt the ConnectionString property in tenant databases while ensuring a secure and gradual transition.

Tip

To indicate that a column is encrypted, simply check the Encrypted data box in the entity properties screen under database options.

The ConnectionString data column contains existing data that needs to be encrypted. To do this, follow the steps below.

8.1 Implementation Steps

  1. Add a data column EncryptedConnectionString for storing the encrypted data bing to the entity property ConnectionString.
  2. Add a entity property PlainTextConnectionString for the unencrypted version bind to tha data column ConnectionString.
  3. Implement a migration interceptor to:
    • Iterate over existing records.
    • Read plaintext values PlainTextConnectionString.
    • Encrypt them.
    • Populate the target column EncryptedConnectionString.

The source code of the interceptor is available on Azure Repos.

8.2 Runtime Synchronization

Add a Saving event handler on the DatabaseServer entity:

  • Each update to ConnectionString is automatically encrypted.
  • Updates the PlaintextConnectionString property synchronously for the plaintext version.

The source code of the event is available on Azure Repos.

8.3 Finalization

  • Both ciphertext and plaintext columns coexist temporarily.
  • Since version 3.1:
    • Remove the plaintext data column.
    • Rename the encrypted data column to ConnectionString.

8.4 Why the Plaintext Column Was Removed

The temporary coexistence of a plaintext column and an encrypted column was a transition mechanism, not a target architecture. Keeping both columns meant that the sensitive value remained readable in clear text at rest, which effectively cancelled out most of the benefits of encrypting it in the first place.

A connection string is not an ordinary piece of data: it concentrates a host, a database name and, above all, credentials granting direct access to a tenant's entire dataset. As long as the plaintext column existed, that value was exposed through every channel where the database is readable or copied:

  • Database backups and dumps, which are frequently stored on less protected storage, transferred over the network, or restored on development workstations.
  • Read replicas, replication logs and CDC streams, which propagate the plaintext value beyond the primary server.
  • Direct SQL access by DBAs, support staff or monitoring tools, none of which need the credentials in clear.
  • SQL injection or over-privileged application accounts, for which a single SELECT was enough to harvest every tenant's credentials.
  • Query plans, traces and diagnostic tooling, which may capture column values.

Removing the plaintext column applies three complementary principles:

  • Data minimisation (GDPR, article 5): only data that is strictly necessary should be stored. Once the encrypted value is authoritative, the plaintext duplicate has no functional purpose.
  • Reduction of the attack surface: the fewer the locations holding a secret, the fewer the paths an attacker can exploit. A duplicated secret is a secret protected only by its weakest copy.
  • Defense in depth: encryption at rest is only meaningful if the protected value has no unprotected twin sitting next to it in the same row.

Maintaining two synchronised columns also carried an operational risk. Any code path writing directly to the data column, any bulk import, or any manual SQL update could silently desynchronise the two representations, leading to a stale or inconsistent plaintext value that was both useless and dangerous. With a single encrypted column, the ValueConverter is the only entry point, and correctness is enforced by construction.

The trade-off is deliberate and must be understood: since version 3.1, the encryption key becomes the sole means of recovering the connection strings. Losing it means losing access to the values. Key management — secure storage, backup, controlled rotation — therefore becomes a first-class operational responsibility, as described in section 4.

9. Conclusion

Integrating encryption in EF Core provides a robust and flexible solution for securing sensitive data. Despite certain limitations-especially regarding queries and performance-these can be mitigated through careful selection of protected fields and controlled use of EF Core mechanisms.