What is Transparent Database Encryption (TDE)?
SQL Server’s Transparent Database Encryption protects data-at-rest by encrypting all the user data in a database’s data files (.mdf and .ndf’s), in the log file (.ldf), a database snapshot (.ss), in the database backup files (.bak, .trn), and when its data is temporarily stored on disk in the tempdb system database. It does not require applications to be changed to gain this protection. TDE became available in SQL Server version 2008, only in the Enterprise edition.
Limitations of TDE
TDE only protects data-at-rest, primarily from file-level attacks. It does not protect against front-door attacks, it does not protect FileStream data, and it does not protect data-in-transit. It is also possible that some decrypted data could be paged from memory to the Windows paging file in clear-text.
Regardless of whether a person is a legitimate user making a mistake or a malicious cracker, TDE offers no protection from someone who has the permissions to read or change data (i.e. front-door attacks). When data is encrypted on disk by TDE and a user sends a command for which they have permissions, SQL Server will honor that command: If the command is to fetch data, SQL Server will decrypt the data and send it to the requesting user or process, and if the authorized command is to drop a table, SQL Server will drop it.
Once SQL Server begins to send out a subset of data in response to an authorized request, TDE is not involved. Protection for data-in-transit is primarily provided by enabling or forcing encryption over the TCP or other endpoints. (Research “SQL Server encrypted connections”.)
Costs of TDE
The cost of TDE always includes a performance hit. First, the CPUs must encrypt all data going into a TDE database and decrypt all data coming out of it. Next, TDE requires encryption of the tempdb system database, and once TDE is used on any database in an instance of SQL Server, all data that goes into tempdb must be encrypted, and all data that is retrieved from tempdb must be decrypted. Since SQL Server does not create a separate tempdb for non-TDE databases, this tempdb encryption/decryption work is a CPU-performance hit that occurs with all user databases, not just the TDE databases.
Additional possible costs include:
- A need for additional disk space. Encrypted data is effectively uncompressible, so if a database or backups are using data compression without TDE, enabling TDE could dramatically increase the amount of disk space needed.
- A more expensive SQL Server license. TDE requires the Enterprise edition of SQL Server, which is more expensive than the standard edition. If the standard edition could be used except for the need to use TDE, then TDE will cost an upgraded license.
- An additional SQL Server license. If two instances of SQL Server are used to avoid non-TDE databases having to use an encrypted tempdb, then TDE will cost an additional SQL Server license.
Alternatives to TDE
While it is true that TDE would provide a significant boost in file-level security if there were no other file-level protections in place, there should always be other file-level protections in place. Here are the foremost options for protecting your data-at-rest instead of (or in addition to) TDE:
- Access Control Lists (ACLs)
- Disk-level encryption (e.g. BitLocker)
- Least privileges
- Role-based security
- Private networks
- Password-encrypted backup files (e.g. password-based AES encryption in WinZip)
- Application-based encryption (possible from all-data to individual cell-level data)
When to use TDE
Since there are many good alternatives to using TDE and since there are costs imposed by using TDE, TDE should not be used unless there are specific reasons for which the alternatives are inadequate and the costs are justifiable. In practice, this means that TDE should rarely be implemented.
Despite its limitations, here are some reasons why TDE might be an appropriate solution:
- If data needs maximum defense-in-depth to guard against unknown vulnerabilities despite the costs and the database administrators (DBAs) can be trusted. Examples of such data might include a list of spies whose lives are at risk if their identities are exposed or a database containing data with mixed levels of security (e.g. unclassified, secret, top secret, etc.) If the DBAs cannot be trusted, TDE is not an appropriate solution, and the data should be encrypted by an application before it is sent to SQL Server.
- If for any reason all of the alternatives are unusable.
How to enable TDE
There are four steps to enable TDE on a user database:
- Create a Database Master Key (DMK) in the master database.
- Create a TDE certificate in the master database.
- Create a Database Encryption Key (DEK) in the user database.
- Alter the user database to set encryption on.
- Create the DMK. If it does not already exist, create the Database Master Key (DMK) in the master database.
IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = ‘##MS_DatabaseMasterKey##’)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘1dU8feRh&tN3$m#si9@vHsW38!$’;
- Create the TDE certificate. The name of the TDE certificate should a descriptive name, such as this one for a user database named “Test”. The “WITH SUBJECT” parameter is optional, but it allows a longer or easier to read description.
CREATE CERTIFICATE TestDatabaseTDECertificate WITH SUBJECT = ‘TDE Certificate for the Test Database’;
- Create the DEK. It’s possible to encrypt the DEK with an asymmetric key instead of your TDE certificate, but doing so will make it difficult or impossible to restore the database backup to another instance of SQL Server. There are also other encryption algorithms that can be used, but AES_256 is the strongest option. While weaker algorithms may provide faster performance, using TDE requires sacrificing performance for maximum security, so the strongest possible DEK might as well be used.
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE TestDatabaseTDECertificate
- Set encryption on. Just creating all the keys and the certificate does not enable TDE; The ALTER DATABASE command must be used to complete the process.
ALTER DATABASE Test SET ENCRYPTION ON
Whether or not TDE is enabled can be confirmed by checking the is_encrypted column of sys.databases (0 = not encrypted by TDE, 1 = encrypted by TDE) or by checking the encryption_state column of sys.dm_database_encryption_keys. Here are the meanings for the encryption_state integers:
0 = No database encryption key present, no encryption
1 = Unencrypted
2 = Encryption in progress
3 = Encrypted
4 = Key change in progress
5 = Decryption in progress
6 = Protection change in progress
How to backup a TDE database
A TDE database is backed up the same way as any other user database but the TDE certificate and the certificate’s private key must also be backed up, as well as the password that encrypts the private key backup file or it will be difficult or impossible to restore the database on any other instance of SQL Server. You do not need to backup the SMK or DMK in order to restore a TDE database on an alternate server.
BACKUP CERTIFICATE TestDatabaseTDECertificate
TO FILE = ‘TestDatabaseTDECertificate’
WITH PRIVATE KEY
( FILE = ‘TestDatabaseTDEPrivateKeyFile’,
ENCRYPTION BY PASSWORD = ‘kGjw#SvH9P!oE4R0$mWni4#hAqbH2’ );
This command will give you a backup file of the public key (the certificate) and a backup file of the private key. You can specify a file path in addition to the file names, otherwise the default file path is similar to “C:\Program Files\Microsoft SQL Server\<SQL version-instance>\MSSQL\DATA”.
You may store the certificate backup, private key backup, and private key password together, but they must be stored separately from the database backups or there is no point in having an encrypted database backup since anyone who can access the database backup, certificate, and key can decrypt it. For maximum security, store the password to the private key backup file separately from the backup file.
How to restore a TDE database to the same server
There are usually no special considerations for restoring a TDE database on its original instance of SQL Server because the TDE certificate and its private key are already in the master database. Simply restore it as you would any non-TDE database.
How to restore a TDE database to an alternate server
The only difference between restoring a TDE database on a non-original instance of SQL Server and the original instance is that the TDE certificate must be restored on the new instance before the TDE database can be restored. Neither the SMK nor the DMK need to be restored from the original instance of SQL Server. Here are the steps to restore a TDE certificate and its database:
- There must be a Database Master Key (DMK) on the master database of the new instance.
- The DMK must be managed by SQL Server or it must be manually opened.
- The certificate backup file and the private key backup file must be available on the new server.
- The private key backup file’s password must be known.
- The TDE Certificate must be restored on the new instance of SQL Server.
- The TDE database must be restored (or reattached).
- There must be a Database Master Key (DMK) on the master database on the new instance. If it isn’t already there, it must be created. If a DMK is being created on the new instance, the DMK password does not have to be the same as the password on the DMK on the original server.
IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = ‘##MS_DatabaseMasterKey##’)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘iY4fnw3Q2!@3JF6so#q$V%u^Ml&r’;
- If the DMK has not been modified to remove its dependency on the SMK, then the SQL Server database engine will automatically open the DMK when needed. Otherwise you must use the “OPEN MASTER KEY” command to allow SQL Server to use it.
3. Recreate the server certificate by using the original server certificate backup file and private key backup file. The password must be the same password that was used when the backup was created.
CREATE CERTIFICATE TestDatabaseTDECertificate
FROM FILE = ‘<file path>\TestDatabaseTDECertificate’
WITH PRIVATE KEY
( FILE = ‘<file path>\TestDatabaseTDEPrivateKeyFile’,
DECRYPTION BY PASSWORD = ‘ kGjw#SvH9P!oE4R0$mWni4#hAqbH2′);
4. Now you can use the RESTORE DATABASE or CREATE DATABASE… FOR ATTACH commands to recover your TDE database on an alternate, non-original instance of SQL Server:
RESTORE DATABASE Test FROM DISK = N'<file path>\Test.bak’
WITH FILE = 1,
MOVE N’Test’ TO ‘<file path>\Test.mdf’,
MOVE N’Test_log’ TO N'<file path>\Test_1.ldf’
CREATE DATABASE Alpha ON
( FILENAME = N'<file path>\Test.mdf’ ),
( FILENAME = N'<file path>\Test_log.LDF’ )
TDE/Encryption-Related System Tables
SELECT * FROM sys.asymmetric_keys
SELECT * FROM sys.certificates
SELECT * FROM sys.databases
SELECT * FROM sys.dm_database_encryption_keys
SELECT * FROM sys.credentials
SELECT * FROM sys.cryptographic_providers
SELECT * FROM sys.crypt_properties
SELECT * FROM sys.key_encryptions
SELECT * FROM sys.symmetric_keys
TDE’s Encryption Hierarchy
 When TDE is applied to a database with a log file that has already been used, there is usually a delay before all the data in the log file is encrypted.
 If you drop the last TDE database in an instance of SQL Server, tempdb will continue to encrypt/decrypt all data going through it until the SQL Server database engine is restarted.
 There are other options, but they don’t support the method of restoring a TDE database to an alternate server as described in this paper.
 You can disable TDE with “ALTER DATABASE <dbname> SET ENCRYPTION OFF”, but tempdb will remain encrypted until there are no more TDE user databases and the SQL Server service has been restarted.
 An exception is in the unlikely event that SMK dependence has been removed from the DMK, in which case the DMK will have to be manually opened prior to restoring the database.
 TDE certificates and databases from SQL Server 2008 and 2008 R2 can be restored to SQL Server 2012.
 Opening the DMK is session-dependent: If the SMK dependency has been removed from the master database’s DMK with the ALTER MASTER KEY command and the connection is later closed, the DMK must be manually re-opened before running the RESTORE DATBASE or CREATE DATABASE… FOR ATTACH commands.
After Executing the command SET ENCRYPTION ON the databases will get encrypt in the backend and if you want to check the Status of Encryption please through below link for script :