Skip to content

4. Data encryption (at rest and in transit)

For SQL databases, data encryption at rest and in transit ensures data security both when it's stored and when it's transmitted between clients and the server.

Encryption at Rest for SQL Databases:

Encryption at rest protects data when it is stored on disk. Most SQL databases provide built-in support for this.

  1. SQL Server:
  2. Transparent Data Encryption (TDE): Encrypts the entire database file (data and log files), protecting against unauthorized access at the storage level.

  3. Always Encrypted: Encrypts sensitive data in the database and ensures it’s encrypted/decrypted on the client side, so the data is never exposed in plain text on the server.

  4. MySQL / MariaDB:

  5. Encryption Plugins: You can use the built-in InnoDB encryption to encrypt database files. The key management is often handled via an external service or system.

  6. You can also use filesystem-level encryption tools (e.g., LUKS or BitLocker) to protect the storage media.

  7. PostgreSQL:

  8. pgcrypto: You can use this extension for column-level encryption.

  9. Filesystem Encryption: Use OS-level encryption like LUKS, or encrypted file systems (e.g., eCryptfs) to protect data at rest.

  10. Oracle Database:

  11. TDE (Transparent Data Encryption): Oracle provides TDE for encrypting database files.
  12. Database Vault: Ensures more strict controls for accessing sensitive data.

Best Practices:

  • Key Management: Use centralized, hardware-backed key management systems (e.g., AWS KMS, Azure Key Vault).
  • Backup Encryption: Ensure backups are encrypted using the same standards.
  • Limit Permissions: Restrict access to encryption keys and limit who can manage encryption.

Encryption in Transit for SQL Databases:

Encryption in transit protects data as it moves between the client and the database server. This is typically done using protocols like TLS/SSL.

  1. SQL Server:
  2. SSL/TLS Encryption: SQL Server supports TLS for encrypted connections. You can enforce SSL/TLS encryption for all connections using configuration settings.

  3. Force Encryption: This option forces encrypted communication for all client-server communications.

  4. MySQL / MariaDB:

  5. SSL/TLS: MySQL supports encrypted connections via SSL. You can configure client and server to require SSL connections, enforcing encryption in transit.

  6. Secure Connections: You can configure the MySQL server to only allow connections using --require_secure_transport.

  7. PostgreSQL:

  8. SSL/TLS: PostgreSQL supports SSL for encrypted communication between the client and server.

  9. Configure SSL by setting ssl = on in the postgresql.conf file and using certificates for the connection.

  10. Oracle Database:

  11. Oracle Net Encryption: You can use Oracle's native encryption for secure communication between the client and the server.

Best Practices:

  • Enforce Encryption: Always enforce SSL/TLS for all connections between clients and servers.
  • Use Strong Ciphers: Ensure that strong encryption ciphers are used (e.g., AES-256) and disable weak ones (e.g., RC4).
  • Validate Certificates: Always verify that the server's SSL certificate is valid and signed by a trusted certificate authority.

Both at-rest and in-transit encryption are critical to protect sensitive data in SQL databases from unauthorized access, tampering, or eavesdropping.