Question

What versions of SQL Server is it available in? What does it mean that TDE is certificate compliantand What happens when the certificate is expired?

Answer 1

SQL Server Versions :

RELEASE NAME	VERSION
SQL Server 1.0 (16-bit)	1.0 (OS/2)
SQL Server 1.1 (16-bit)	1.1 (OS/2)
SQL Server 4.2A (16-bit)	4.2A (OS/2)
SQL Server 4.2B (16-bit)	4.2B (OS/2)
SQL Server 4.21a	4.21a (WinNT)
SQL Server 6.0	6.0
SQL Server 6.5	6.5
SQL Server 7.0	7.0
SQL Server 7.0 OLAP Tools	-
SQL Server 2000	8.0
SQL Server 2000 64-bit Edition	8.0
SQL Server 2005	9.0
SQL Server 2008	10.0
Azure SQL database (initial release)	10.25
SQL Server 2008 R2	10.50
SQL Server 2012	11.0
SQL Server 2014	12.0
SQL Server 2016	13.0
SQL Server 2017	14.0

TDE CERTIFICATE :

Transparent Data Encryption (TDE) encrypts SQL Server, Azure SQL Database, and Azure SQL Data Warehouse data files, known as encrypting data at rest. You can take several precautions to help secure the database such as designing a secure system, encrypting confidential assets, and building a firewall around the database servers. However, in a scenario where the physical media (such as drives or backup tapes) are stolen, a malicious party can just restore or attach the database and browse the data. One solution is to encrypt the sensitive data in the database and protect the keys that are used to encrypt the data with a certificate. This prevents anyone without the keys from using the data, but this kind of protection must be planned in advance.

TDE performs real-time I/O encryption and decryption of the data and log files. The encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery. The DEK is a symmetric key secured by using a certificate stored in the master database of the server or an asymmetric key protected by an EKM module. TDE protects data "at rest", meaning the data and log files. It provides the ability to comply with many laws, regulations, and guidelines established in various industries. This enables software developers to encrypt data by using AES and 3DES encryption algorithms without changing existing applications.

What happens when the certificate is expired?

Certificate used in TDE will continue to work even after its expiration date. This is because the Database Encryption Key (DEK) in the user database is the key that encrypts the data at rest. DEK is the symmetric key stored in the user database boot record. The certificate which is stored in the master database is used to secure and protect the DEK. Hence, this explains why data at rest encrypted with TDE will still work even after the certificate used in TDE has expired.