SQL Server Encryption
Overview
SQL Server provides a comprehensive approach to data security by offering methods to encrypt data both in transit and at rest. 🔐
For data moving across the network, Transport Layer Security (TLS) is used to create a secure, encrypted channel between the client application and the SQL Server, preventing any eavesdropping on the information as it travels.
For data stored on the disk, Transparent Data Encryption (TDE) encrypts the entire database, including its backups and log files, rendering the physical files unreadable without the correct keys. This layered strategy ensures data is protected from unauthorised access whether it is being actively used or simply stored. Successfully implementing either form of encryption, particularly TDE, depends on rigorous and secure management of the associated encryption certificates and keys, as their loss can lead to irreversible data loss.
https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/sql-server-encryption
Encryption in transit / Transport Level Security (TLS)
Synergetic natively supports encryption in transit using Transport Layer Security (TLS). This is a crucial security measure that protects data while it is travelling over a network. By implementing TLS, Synergetic ensures that sensitive information, such as personal or financial data, is secure from unauthorised access.
What is TLS?
TLS is a security protocol that encrypts data to create a more secure communication channel between two points. When data is sent over a network, it can be vulnerable to interception. TLS prevents this by encrypting the data, making it unreadable to anyone without the correct decryption key.
Synergetic and SQL Server Encryption
By default, network traffic to and from Microsoft SQL Server is not encrypted. Synergetic addresses this vulnerability by using TLS to encrypt connections to the SQL Server. This is the recommended method for securing your data, providing more detailed control over encryption compared to other methods like Internet Protocol Security (IPSec). The process involves configuring the SQL Server to use a security certificate, which enables the TLS encryption. It is important to manage these certificates proactively to prevent service disruptions when they expire.
Known Issues
Be aware that enabling TLS 1.2 can sometimes cause issues with certain Synergetic components, such as Crystal Reports and SEQTA Sync. There are workarounds available for these known problems, and it is recommended to review the Synergetic support documentation for the latest information and guidance.
Additional links:
https://help.synergetic.net.au/s/article/kb-2297987775-sql-server-network-traffic-encryption-tls
Encryption at rest / Transparent Data Encryption (TDE)
Encryption at rest is a security measure that protects your data where it is stored. Transparent Data Encryption (TDE) is a technology used by Microsoft SQL Server to encrypt entire database files, a process known as encrypting data at rest. This means the data files and their corresponding backups are encrypted on the physical disk.
Synergetic and TDE
While Synergetic does not actively support TDE, it can be used to provide another layer of security for your database. TDE performs real-time encryption and decryption of data and log files. This process is 'transparent' because it happens automatically in the background without requiring any changes to applications that access the database. If the physical media, such as disk drives or backup tapes, were stolen, the data would be unreadable to an unauthorised party.
An FYI guide on doing this can be found at Encrypting database backups
This is not something that we can support directly due to the use of credentials out of our control.
Important Caveats on Key Management
Implementing TDE introduces a critical dependency on the management of encryption keys. SQL Server uses a hierarchy of keys to protect the data, including a Database Encryption Key (DEK) and a certificate stored in the master database.
It is absolutely vital that you back up the certificate and the master key used for encryption. These must be stored in a separate, secure location. If you lose these keys, you will not be able to restore the database on another server or recover your data in a disaster recovery scenario. Losing the encryption keys will result in the permanent loss of your data. Due to these significant risks, careful planning and robust key management procedures are essential before implementing TDE.
Additional links: