Securing each part and datas in a database is now an important task for DBAs to accomplish else the datas might be hacked. Keeping in mind Microsoft has started encryption features from SQL 2005 and its greatly improved its feature in SQL Server 2008. Lets recall the encryption features in previous versions of SQL Server.
SQL Server 2000 This version of SQL Server is not accompanied with any of the encryption facility. Hence the developers need to write their own code in their client applications to encrypt their datas.
SQL Server 2005 Microsoft has introduced new encryption feature to encrypt column level (some times called as cell level) datas. This method offers datas to be encrypted in the database level however applications need to re-architecture to accompany this feature.
SQL Server 2008 Ok, In SQL Server 2005 we have encryptions to datas. What happens if the database files itself is copied stolen, so that the datas can be easily read loosing confidential datas. To prevent this Microsoft has introduced a new encryption feature in SQL Server 2008 Enterprise edition and this is Transparent Data Encryption (TDE).
TDE Available in the below Edition
- SQL Server 2008 Enterprise Edition
- SQL Server 2008 Developer Edition
Features of TDE (Transparent Data Encryption)
- TDE performs real time IO encryption to physical files of the database (i.e data & log file), hence you wont be able to attach restore the database without original encryption certificate and master key.
- Encryption is at Database level hence you can choose the important database to be encrypted and this allows minimal usage of resources
- The name itself implies that this is transparent encryption which means that there is no need to re-code re-architecture the applications to fit in to this encryption.
- Its simple to implement
- Minimal resource usage compared to the encryption feature available in SQL 2005 (column level)
- If a database is being used in database mirroring or log shipping, both databases will be encrypted. The log transactions will be encrypted when sent between them
Limitations of TDE (Transparent Data Encryption)
- TDE does not provide encryption across communication channels.
- tempdb database will be encrypted if any of the db is using TDE, this makes overhead for other databases which is using tempdb for its operation.
- FILESTREAM data is not encrypted even when TDE is enabled.
- Read-only filegroups are not encrypted even when TDE is enabled
- Replication does not automatically replicate data from a TDE-enabled database in an encrypted form. You must separately enable TDE if you want to protect the distribution and subscriber databases.
- TDE does not provide protection at the memory or transit level
- TDE encrypted databases cannot be used in other editions (the server will error out on attempts to attach or restore)
How Transparent Data Encryption (TDE) works
When TDE is enabled (or disabled), the database is marked as encrypted in the sys.databases catalog view and the DEK state is set to Encryption In Progress. The server starts a background thread (called the encryption scan or scan) that scans all database files and encrypts them. When the encryption scan is completed, the DEK state is set to the Encrypted state. At this point all database files on disk are encrypted and database and log file writes to disk will be encrypted.
Encryption of the database file is performed at the page level. The pages in an encrypted database are encrypted before they are written to disk and decrypted when read into memory. TDE does not increase the size of the encrypted database.
What happens when data is written to Disk
As I already said TDE doesnt encryption of datas that exists in the buffer pool. Hence when the datas is written from buffer pool to disk (MDF, LDF & NDF) the datas are first encrypted and then then encrypted datas will be sent to disk. When the database page file is written to disk, the headers are not encrypted with the rest of the data because this information is necessary for the page to be reloaded. The header contains status details such as the database compatibility level, database version, mirroring status, and so forth. Any important data (such as the DEK) is encrypted before it is inserted into the header.
What happens when data is read from disk
When the data is read from the disk, first the data will be decrypted and then it will be sent to buffer pool. Since TDE doesnt support encryption in buffer hence the datas read from the disk first needs to be decrypted and sent to buffer pool.
What happens to Snapshot and Backups
When TDE is enabled to a database then encryption is available to its backup and also for its snapshot. Encrypting at the I/O level also allows the snapshots and backups to be encrypted, thus all snapshots and backups created by the database will be encrypted by TDE. The certificate that was used to protect the DEK when the file was written must be on the server for these files to be restored or reloaded. Thus, you must maintain backups for all certificates used, not just the most current certificate.
Quote from Microsoft:When enabling TDE, you should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database. The encrypting certificate or asymmetric should be retained even if TDE is no longer enabled on the database. Even though the database is not encrypted, the database encryption key may be retained in the database and may need to be accessed for some operations.
How indexes will work on encrypted datas
The performance impact of TDE is minor. Because the encryption occurs at the database level, the database can leverage indexes and keys for query optimization. This allows for full range and equality scans. The overall performance impact was estimated to be around 3-5% (As per Microsoft document) and can be much lower if most of the data accessed is stored in memory. Encryption is CPU intensive and is performed at I/O. Therefore, servers with low I/O and a low CPU load will have the least performance impact.
Supported encryption algorithms are AES with 128-bit, 192-bit, 256-bit keys or 3 Key Triple DES for TDE.
There are many methods of encryption available for a database. This encryption method will mainly focus on securing the physical files of the database with some limitations in it. Hence you can choose this encryption method if you want to secure your database files.