What Is TDE in SQL?

Encrypting data at rest is a critical component in cybersecurity on enterprise servers. Transparent data encryption (TDE) is a technology used by database vendors to encrypt data at the file level. TDE protects data during physical theft when drives are stolen or when attackers exfiltrate files from a compromised server. It’s an added layer of security in data protection strategies.

What Is TDE?

TDE is a form of file-level encryption for databases. The database system stores a symmetric key and uses it to encrypt data when it’s written to a drive and decrypts it when data is retrieved. TDE also encrypts log files used for backups and tracking transactions made on the database server.

When data is encrypted at rest, it means that files are encrypted prior to being stored and transparently decrypted when the system retrieves them. TDE secures data at rest only, so administrators must ensure that data in transit is also encrypted, meaning data transferred from the database to another location must be encrypted independently from TDE configurations. If files are stolen, they would be unusable to the attacker without access to the symmetric key, so TDE is a viable strategy for advanced cybersecurity and data protection.

What Is TDE in SQL?

Microsoft is one database developer that incorporates TDE to protect enterprise data. When you configure Microsoft SQL Server to work with TDE, the database generates a certificate and stores it in the master database. The certificate is used to encrypt the symmetric key, which is then used to encrypt data stored to disk or retrieved and held in memory, making it unreadable if exfiltrated.

The entire process of encrypting and decrypting data is transparent to administrators and users working with database queries. TDE does not affect performance even with the extra encryption step in the storage and retrieval workflow. Symmetric key encryption is generally fast and does not interfere with performance or productivity.

Vendors such as Amazon use Elastic Block Store (EBS). EBS encrypts data in blocks and not at the file level. Data is encrypted when it’s stored and retrieved, but data files are not encrypted. The difference in security is that file-level encryption protects from physical theft. If files are exfiltrated from a compromised system, TDE encrypts files making them unusable to the attacker.

Examples of TDE in Popular DBMSs

While Microsoft uses TDE in its SQL Server database products, TDE is also incorporated into other vendor database applications. IBM uses TDE in its Db2 database software. Oracle also uses TDE as an advanced security option for its 10g and 11g database applications. All three vendors require administrators to enable and configure TDE before it can be used.

MySQL also incorporates TDE. TDE in MySQL works similarly to TDE in Microsoft SQL Server. The two-tier encryption process generates a public and private asymmetric key used to encrypt the symmetric key. The symmetric key encrypts and decrypts data as it’s stored and retrieved. The master encryption key is stored in a vault where only administrators and the database system can access it.

Benefits and Advantages of Using TDE

Data at rest is any information stored on a device. It’s the opposite form of data in transit, which describes data transferred from one device to another or data moving to a different location. Data at rest is distinct from data in transit, so it needs its own strategy for data protection and cybersecurity.

Some compliance regulations require data at rest to be encrypted. A good example is the Health Insurance Portability and Accountability Act (HIPAA). HIPAA has strict regulations for protecting data at rest, especially on servers and workstations storing patient information. Always ensure that your infrastructure follows any regulatory standards overseeing your industry and payment workflows.

Microsoft integrates TDE seamlessly with its SQL Server database engine. Users and administrators notice no difference in its operability, performance, or the way SQL Server responds to queries. Administrators can move files to backup locations without manually encrypting backup files to follow compliance and data protection best practices.

Downsides or Reasons Not to Use TDE

Any cryptographic workflow adds complexity to the environment, so some administrators might hesitate to use it. Although TDE does not interfere with performance on a modern server with hardware to handle enterprise queries, it might interfere with query performance on older hardware. Encrypted data also requires additional storage capacity, and the storage capacity must scale as companies acquire more data.

TDE encrypts the entire file stored on disk, so administrators do not have granular control over cell-level or column-level encryption. All disk I/O activity is encrypted, so it’s an “all or nothing” feature for SQL Server databases. SQL Server also offers cell-level or column-level encryption, which gives administrators more granular control of encrypting specific fields and information.

FlashArray™ provides simple-to-use AES-256 standard data-at-rest encryption that does not compromise performance. Read this white paper to learn more. >>  

Conclusion

Encryption is critical in data protection especially when hosting data in the cloud. Administrators can manually configure their SQL Server database to encrypt files. Backups and logs of these files will be encrypted automatically once TDE is enabled. If you oversee compliance of a database, TDE adheres to common regulations requiring file-level encryption of sensitive data. 

Is your storage layer holding back your SQL performance? Accelerate the performance of your SQL Server deployments with all-flash storage solutions from Everpure.