Transparent Data Encryption is designed to protect data by encrypting the physical files of the database, rather than the data itself. Its main purpose is to prevent unauthorized access to the data by restoring the files to another server. With Transparent Data Encryption in place, this requires the original encryption certificate and master key. It was introduced in the Enterprise edition of SQL Server 2008. John Magnabosco explains fully, and guides you through the process of setting it up.
Keyless entry for automobiles was first introduced by American Motors Corporation in 1983 and, today, there are a variety of methods that are available that permit the user to gain access to their automobile without inserting a physical key into the door lock. These include pushing a button on a device that transmits a radio frequency, entering a code into a key pad located beneath the driver's side door handle, or possessing a device, called a fob, that is detected by the automobile's security system.
Of these devices, only the fob offers transparency to the owner of the vehicle. The fob is recognized by the security system and the door is automatically unlocked; in other words, the fob holder is granted access to the vehicle without any distinguishable action required on his or her part. If a person who does not possess the fob attempts to open the door of the automobile, the door remains locked, denying access into the vehicle.
The experience of the car owner in possession of a fob is similar to the experience of the user attempting to gain access to a database in which Transparent Data Encryption (TDE) has been enabled. TDE is distinct from other techniques in that it secures data by encrypting the physical files of the database, rather than the data itself. The data files for a given database are encrypted using a database encryption key in the user database. This key references a key hierarchy in the Master database, and this dependency prevents the data files from being viewed outside their instance.
Therefore, a valid user can access the decrypted contents of the database files without any distinguishable actions, and without even being aware that the underlying data files are encrypted. However, a would-be data thief, who has obtained access to the data files through a stolen backup file, will find he or she is unable to access the data it contains. Overall, this is a straightforward, low-impact feature that has great security benefits; the only caveat being that it requires SQL Server 2008 Enterprise Edition.
In this article we will explore the considerations of TDE that must be understood prior to its use. We will also walk through an example of implementing and validating TDE using the HomeLending database. Finally, we will cover the process of removing TDE, should the need arise.
How TDE Works
Transparent Data Encryption (TDE) was introduced in SQL Server 2008, as a feature of the Enterprise Edition of that product. The Developer Edition of SQL Server 2008 also offers TDE, but its license limits its use to development and testing only.
As noted above, TDE's specific purpose is to protect data at rest by encrypting the physical files of the database, rather than the data. These physical files include the database file (.mdf), the transaction log file (.ldf) and the backup files (.bak).
The protection of the database files is accomplished through an encryption key hierarchy that exists externally from the database in which TDE has been enabled. The exception to this is the database encryption key, which was introduced to the database encryption key hierarchy specifically to support the TDE feature, and is used to perform the encryption of the database files.
In Figure 1, the key hierarchy, and their required location of each key, is illustrated. The service master key exists at the instance level. The database master key and certificate at the Master database are used to protect the database encryption key that is located at the user database, which is the HomeLending database in our example. The database encryption key is then used to decrypt the database files of the user database.
Figure 1: TDE Encryption Key Hierarchy.
The dependency upon the encryption key hierarchy in the Master database, as well as the instance, prevents the database files from being restored to an instance of SQL Server that does not contain the referenced keys. This level of protection is a great comfort if a backup tape that contains your database backup files were to fall into the wrong hands.
Additionally, the encryption of the backup files prevents the plain text values that are contained within the database being disclosed by opening the backup files using a text editor and scanning its contents. The details regarding this scenario will be covered later in this article.
Benefits and Disadvantages of TDE
Comparing TDE to cell-level encryption is a bit like comparing apples to oranges. They are solutions for different challenges. TDE offers general protection to the database while cell-level encryption offers specific protection to data. I would encourage you to consider using TDE in conjunction with other encryption and obfuscation methods, for a layered approach to protection. To determine whether or not TDE should be part of your protection strategy for sensitive data, consider the following benefits and disadvantages.
Benefits
- Implementation of TDE does not require any schema modifications.
- Since the physical data files and not the data itself are encrypted, the primary keys and indexes on the data are unaffected, and so optimal query execution can be maintained.
- The performance impact on the database is minimal. In their whitepaper titled "Database Encryption in SQL Server 2008 Enterprise Edition", Microsoft estimates the performance degradation for TDE to be 3-5%, while cell-level encryption is estimated to be 20-28%. Of course, the impact well may vary, depending upon your specific environment, and volume of data.
- The decryption process is invisible to the end user.
Disadvantages
- Use of TDE renders negligible any benefits to be gained from backup compression, as the backup files will be only minimally compressed. It is not recommended to use these two features together on the same database.
- TDE does not provide the same granular control, specific to a user or database role, as is offered by cell-level encryption.
- TDE is available only with SQL Server 2008, Enterprise Edition and so will probably not be available to all installations within your environment.
Considerations when Implementing TDE
Prior to implementing TDE, there are several issues to take into consideration, discussed over the following sections.
Master Key Interdependency
The process of implementing TDE involves the creation of a database master key and certificate, or asymmetric key, on the Master database. Only one database master key can be created for a given database so any other user databases that share the instance, and have TDE implemented, will share a dependency upon the Masterdatabase master key.
This interdependency increases the importance of performing a backup of the Master database master key to ensure the continued accessibility of the TDE-enabled databases.
Performance Impact on TempDB
When TDE is initially implemented, the physical file of the TempDB system database is also encrypted. Since theTempDB database contains temporary data from the TDE-enabled database, its encryption is required to maintain full protection by this feature; otherwise the information that is temporarily stored in the TempDB database from the TDE enabled databases would be exposed through the physical files of TempDB.
The TempDB database is used by all user and system databases in the instance to store temporary objects, such as temporary tables, cursors and work tables for spooling. It also provides row versioning and the ability to rollback transactions.
Once the TempDB database is encrypted, any reference and use of this database by other databases, regardless of whether they have TDE enabled or not, will require encryption and decryption. While this encryption and decryption of the TempDB database files remains transparent to the user, it does have a minimal performance impact on the entire instance. Microsoft has estimated the entire impact of TDE on a SQL Server instance to be 3-5% depending on the server environment and data volume.
TDE and Decryption
TDE is designed to protect data at rest by encrypting the physical data files rather than the data itself. This level of protection prevents the data and backup files from being opened in a text editor to expose the file's contents.
TDE encryption occurs prior to writing data to disk, and the data is decrypted when it is queried and recalled into memory. This encryption and decryption occurs without any additional coding or data type modifications; thus it’s transparency. Once the data is recalled from disk, into memory, it is no longer considered to be at rest. It has become data in transit, which is beyond the scope of this feature. As such, alongside TDE, you should consider applying additional supporting layers of protection to your sensitive data, to ensure complete protection from unauthorized disclosure. For example, you may wish to implement, in addition to TDE, encrypted database connections, cell-level encryption, or one-way encryption. For additional data in transit protection that is required, externally from the database, you may need to consult with, or defer to, your Network Administration team.
Backup and Recovery
As noted previously, TDE prevents the backup files from being opened by a plain text editor. It also limits the recovery of the database backup file to the instance that holds the encryption key hierarchy that was in existence at the time the backup was created.
As illustrated in Figure 1, backup files of databases with TDE enabled are encrypted using a key hierarchy that includes the service master key of the SQL Server instance, the database master key and certificate for the Masterdatabase.
Despite this dependency, none of these keys are included with the standard database backup, and must be backed up separately via the following commands:
- BACKUP SERVICE MASTER KEY to backup of the service master key.
- BACKUP MASTER KEY to backup of a database master key.
- BACKUP CERTIFICATE to backup the certificate.
This behavior is one of the security benefits of TDE. In order to restore the encrypted data to another instance of SQL Server, a user needs to recover the service master key backup file, the Master database master key backup file and the Master database certificate private key, prior to recovering the database backup file.
The database encryption key that is created in the user database, in which TDE has been implemented, is included in the standard database backup. It is stored in the boot record of the database file so that it can be accessed and used to decrypt the user database.
When the service master key and database master key are backed up, it is recommended to store their backup files in a separate location from the database files. This separation will ensure continued protection of the encrypted data in the event that the database backup media is stolen or compromised.
TDE and Replication
If the TDE-enabled database is part of a replication setup, the subscribing database must also have TDE implemented. The data that is traveling between the databases will be in plain text and is vulnerable to unauthorized disclosure. A method of encrypting connections, such as secure socket layers (SSL) or Internet protocol security (IPSec), is recommended.
TDE and FileStream Data
The FILESTREAM data type stores large unstructured objects, such as documents and images, in an integrated physical file that is separate from the database file. When TDE is implemented on a user database that containsFILESTREAM data, the filestream files remain unencrypted.
Implementing TDE
In this section, we will implement TDE using the HomeLending database. Our TDE implementation, in comparison to cell-level encryption, will be very simple. There are no modifications to the schema required, there are no permissions that need to be granted to database users and roles in order to use TDE, and there are no additional database objects that must be created to perform the encryption and decryption methods.
On the other hand, the person performing the implementation of TDE does require specific permissions; namelyCONTROL permissions on the Master and HomeLending databases. It is recommended to perform this process while the database is not in use by other users.
Backup before Proceeding
It is a general best practice to backup a database prior to making modifications. However, it is especially important when implementing TDE, in order to ensure that, should the TDE implementation need to be reversed, you can cleanly recover the database in its original form.
In addition, by performing a database backup, a new checkpoint will be established in the transaction log. The creation of a checkpoint truncates all inactive items in your transaction log prior to the new checkpoint. This will ensure that your transaction log is free from unencrypted items, prior to the TDE implementation. Listing 1 shows the backup command for the HomeLending database.
USE HomeLending;
GO
BACKUP DATABASE HomeLending
TO DISK = 'D:\HomeLending\Backup\HomeLending.bak'
WITH NOFORMAT,
INIT,
NAME = 'HomeLending-Full Database Backup',
SKIP,
NOREWIND,
NOUNLOAD,
STATS = 10
GO
Listing 1: Backing up the HomeLending database, prior to TDE.
With the backup successfully completed, we can begin the process of implementing TDE.
The Master Database
Our first step is to create a database master key for our Master database, using the CREATE MASTER KEYmethod, as shown in Listing 2.
USE master;
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'MyStr0ngP@ssw0rd2009';
GO
Listing 2: Creating the database master key in the Master database.
Notice that, while ENCRYPTED BY PASSWORD is a required argument to the method, our intent, as in Chapter 5, is to instead protect the database master key with the service master key. This option is automatically available to us, upon creation of the database master key.
A search against the sys.key_encryptions catalog view for the ##MS_DatabaseMasterKey## key, as shown in Listing 3, returns ENCRYPTION BY MASTER KEY, in reference to the service master key.
USE master;
GO
SELECT
b.name,
a.crypt_type_desc
FROM
sys.key_encryptions a
INNER JOIN sys.symmetric_keys b
ON a.key_id = b.symmetric_key_id
WHERE
b.name = '##MS_DatabaseMasterKey##';
GO
Listing 3: Confirming protection of the database master key by the service master key.
The next step is to create a self-signed certificate that is protected by the database master key of our Masterdatabase. All certificates created within SQL Server, as opposed to being imported, are self-signed. This associates the certificate to the database.
Certificates are created using the CREATE CERTIFICATE method:
USE HomeLending;
GO
CREATE CERTIFICATE MyHighCert
WITH SUBJECT = 'Cert used for sensitive class of high';
GO
Listing 4.
Since this certificate is located in the Master database and will be used to protect the database encryption key of our HomeLending database, we will name this certificate MasterCert, as shown in Listing 5.
USE master;
GO
CREATE CERTIFICATE MasterCert
WITH SUBJECT = 'Cert used for TDE';
GO
Listing 5: Creating the MasterCert self-signed .
As for Listing 4, by omitting the ENCRYPTION BY PASSWORD argument, we are specifying that the certificate is to be protected by the database master key.
At this point in the process you should perform a backup of the certificate with its private key, using the BACKUPCERTIFICATE command shown in Listing 6. In the event that the HomeLending database needs to be restored, this certificate and its private key will be required.
USE master;
GO
BACKUP CERTIFICATE MasterCert
TO FILE = 'D:\HomeLending\Backup\MasterCert.bak'
WITH PRIVATE KEY (
FILE = 'D:\HomeLending\Backup\MasterCert.pvk',
ENCRYPTION BY PASSWORD = 'MyB@ckUpP@ssw0rd');
GO
Listing 6: Backing up the MasterCert certificate.
Since our MasterCert certificate is protected by the Master database master key, the DECRYPTION BYPASSWORD argument is not included in the WITH PRIVATE KEY argument of this command.
The User Database
Having created the database master key and the MasterCert certificate in the Master database, we are ready to create the database encryption key for the HomeLending database which we will use to perform the cryptographic functions for the physical files of our database.
The database encryption key is created using the CREATE DATABASE ENCRYPTION KEY command. The arguments to this method include:
- WITH ALGORITHM: Specifies the algorithm used, which in turn dictates the strength of the key.
- ENCRYPTION BY: Defines the protection method of the key. The key used in the ENCRYPTION BYargument can be a certificate or an asymmetric key that is located in the Master database.
Listing 7 shows the exact command used for the HomeLending database's database encryption key.
USE HomeLending;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MasterCert;
GO
Listing 7: Creating the HomeLending database encryption key.
The AES_128 option specifies Advanced Encryption Standard (AES) with a 128 bit key length, and we protect the database encryption key with the MasterCert certificate that was created in the Master database.
The final step in the setup process of TDE is to enable it. This is accomplished by executing the ALTER DATABASE command with the SET ENCRYPTION ON argument.
USE HomeLending;
GO
ALTER DATABASE HomeLending
SET ENCRYPTION ON;
GO
Listing 8: Enabling TDE.
At this point, an encryption scan occurs, which is the process by which the physical files of the database are scanned and encrypted. Included in this scan process are the database files, TempDB database files and transaction log files.
Transaction log files contain information that is used to maintain data integrity and are used in the restoration process. Within these files are a series of smaller units called virtual log files (VLFs). These VLFs contain records that pertain to transactions within the database file. Prior to the implementation of TDE, these VLFs contain unencrypted data. During the encryption scan any pages that have been in the buffer cache and modified, known asdirty pages, are written to disk, a new VLF is created and the prior inactive VLFs are truncated. This results in a transaction log that only contains encrypted data.
The duration of the encryption scan will vary depending upon the size of the database files. Once the process has completed, the encryption_state column in the sys.dm_database_encryption_keys dynamic management view will reflect the encryption state of "encrypted", and will show the value of "3" in this column, for our HomeLendingdatabase.
Verifying TDE
Once the implementation of TDE is complete there are a few ways you can verify that these steps indeed succeeded.
Using Dm_Database_Encryption_Keys
Dynamic management views (DMV) are built-in views that provide metadata regarding the settings, health and properties of SQL Server instances and databases. The sys.dm_database_encryption_keys DMV presents information about the database encryption keys used in a given database, as well as the encryption state of the database.
Note:
Database encryption keys are only utilized for the benefit of the TDE feature of SQL Server 2008; therefore this DMV is not available in SQL Server 2005.
Through the use of a query in which the sys.dm_database_encryption_keys DMV and the sys.databasescatalog view are joined through the database_id column, we are able to determine the success of the TDE implementation, as demonstrated in Listing 9.
USE master;
GO
SELECT
db.name,
db.is_encrypted,
dm.encryption_state,
dm.percent_complete,
dm.key_algorithm,
dm.key_length
FROM
sys.databases db
LEFT OUTER JOIN sys.dm_database_encryption_keys dm
ON db.database_id = dm.database_id;
GO
Listing 9: Verifying TDE using dm_database_encryption_keys.
A return value of "1" for the is_encrypted column of the sys.databases catalog view indicates that the database has been encrypted through TDE.
The value of the encryption_state column from the sys.dm_database_encryption_keys DMV reflects whether or not the encryption process is complete. A value of "3" indicates that the encryption process is complete. A value of "2" in this column indicates that the encryption process is in progress. The percent_complete column from the same DMV indicates the progress of the encryption process. This column only reflects a value other than "0" when the database encryption state is in the process of changing (being encrypted or decrypted).
In this sample query, I added the key_algorithm and key_length columns to illustrate an interesting dynamic in regard to the TempDB database, as shown in the results in Table 1.
Table 1: Results of TDE verification query.
As previously noted, the encryption of the TempDB is a byproduct of implementing TDE on any given database within an instance of SQL Server. The is_encrypted column for our HomeLending database contains the value of "1" which indicates that it has been successfully encrypted; but the TempDB contains the value of "0", while the values in the other columns indicate that encryption has taken place. This is because the TempDB database is encrypted outside of the established TDE key hierarchy.
This is further emphasized by the algorithm that is used to encrypt the TempDB database. As you will recall, the creation of the database encryption key for the HomeLending database was designated as AES_128, which uses a key length of 128 bits. The results of this query show that the TempDB database is actually using a key length of 256 bits.
The reason for the separate encryption process lies in the inherent behavior of the TempDB database; when the SQL Server instance is stopped and started the TempDB database is dropped and recreated. This can be verified by performing the following steps:
- Stop the SQL Server instance.
- Start the SQL Server instance.
- Execute SELECT * FROM SYS.DATABASES, using the Master database.
The result of the third step will reveal that the column titled CREATE_DATE for the TempDB database will be approximately the date and time that you restarted the SQL Server instance. When thesys.dm_database_encryption_keys DMV is executed, the database encryption key for the TempDB database will still be included in the results and the column titled CREATE_DATE will also reflect the time that the instance was restarted. This illustrates that when the TempDB database is recreated so is its database encryption key.
At first glance the comparison of the CREATE_DATE columns of the sys.databases andsys.dm_database_encryption_keys DMV may raise concern since they do not match; but consider that thesys.dm_database_encryption_keys DMV reflects the date and time in Greenwich Mean Time (GMT) while thesys.databases catalog view reflects the date and time according to your time zone. Depending on your location this may appear to be in the future or in the past. In my case, being on Eastern Standard Time (EST) in the United States the sys.dm_database_encryption_keys DMV CREATE_DATE is five hours into the future.
Verification through Backup and Recovery
Another method of verifying the success of a TDE implementation is to perform a backup of the database, after TDE has been enabled, as shown in Listing 10. When doing so, make sure not to overwrite the backup file that was created prior to implementing TDE.
USE HomeLending;
GO
BACKUP DATABASE HomeLending
TO DISK = 'D:\HomeLending\Backup\HomeLending_PostTDE.bak'
WITH NOFORMAT,
INIT,
NAME = 'HomeLending-Full Database Backup',
SKIP,
NOREWIND,
NOUNLOAD,
STATS = 10
GO
Listing 10: Backing up the HomeLending database after TDE is implemented.
The next step is to compare the contents of the pre-TDE and post-TDE backup files, by opening both files up in a simple text editor such as Notepad, Wordpad or Textpad. We can perform a search within the pre-TDE backup file for the plain text of a known sensitive data value. For example, we will search for the value of "319726 Rocky Fabien Avenue" which is contained in the Borrower_Address table in the HomeLending database.
This search reveals the searched value in plain text, as shown in Figure 2. In addition, if you were to manually scan through the backup file, you would find that the metadata of our database objects, such as tables, views, stored procedures and user defined functions are revealed in plain text.
Figure 2: Backup File - Unencrypted.
The same search on our post-TDE backup file will result in the message box shown in Figure 3, stating that it cannot find the requested value. This is because the entire backup file, including the metadata of our database objects, has been encrypted and no longer contains any plain text values.
Figure 3: Search Results in Encrypted Backup File.
One final test in regard to the backup file is to attempt to restore the post-TDE backup file onto a different instance than the one in which the HomeLending database resides, using the RECOVER DATABASE command, as shown in Listing 11.
USE master;
GO
RESTORE DATABASE HomeLending
FROM DISK = 'D:\HomeLending\Backup\HomeLending_PostTDE.bak'
WITH FILE = 1,
NOUNLOAD,
REPLACE,
STATS = 10
GO
Listing 11: Attempting to restore the HomeLending database.
This attempt will return an error message that states that the certificate at the Master database level, in which theHomeLending database encryption key is protected, does not exist; therefore the attempt will fail.
Msg 33111, Level 16, State 3, Line 2
Cannot find server certifiate with thumbprint…
Msg 3013, Level 16, State 3, Line 2
RESTORE DATABASE is terminating abnormally
Using EXECUTE AS
Finally, we can perform a test to determine that the data that is contained within the encrypted HomeLendingdatabase files can be read by valid users of the database, as shown in Listing 12. We use EXECUTE AS USER to impersonate various users within the database and test their ability to access the encrypted data. The use ofREVERT terminates the impersonation and returns us to our original user account.
USE HomeLending;
GO
-- execute as a user who is a member of Sensitive_high role
EXECUTE AS USER = 'WOLFBA';
GO
SELECT * FROM dbo.Borrower;
GO
REVERT;
GO
-- execute as a user who is a member of Sensitive_medium role
EXECUTE AS USER = 'KELLEYWB';
GO
SELECT * FROM dbo.Borrower;
GO
REVERT;
GO
-- execute as a user who is a member of Sensitive_low role
EXECUTE AS USER = 'JONESBF';
GO
SELECT * FROM dbo.Borrower;
GO
REVERT;
GO
Listing 12: Verifying TDE using EXECUTE AS queries.
Each query in the above script successfully returns the contents of the Borrower table within the HomeLendingdatabase. This demonstrates that the automatic decryption is functioning as expected, and verifies that permissions to the appropriate database objects are not affected.
Reversing the Implementation of TDE
It was once said that the only things certain in life are death and taxes. It could be argued that change is another certainty. You may find yourself in a situation where TDE has been implemented, you have validated that it works, are ready for users to begin using the newly-encrypted database and then, lo-and-behold, a request to reverse TDE comes your way.
Boss: "I would like you to proceed with implementing TDE immediately …"
DBA: "Cool, I'll get right on it."
(DBA Implements TDE)
Boss: "Hey, as I was saying yesterday: I would like you to proceed with implementing TDE next week after our presentation to the Technology Committee."
DBA: "No problem … ah …"
(Begin reversal process)
If, at the time this request comes your way, no transactions have been performed on the encrypted database then you can reverse the TDE implementation using the following steps:
- Restore the backup file of the HomeLending database that was created before TDE was implemented.
- Drop the certificate that was created in the Master database. This should only be done if there are no other user databases in the instance that have been TDE-enabled. If there are other user databases in the instance that have TDE enabled, you will want to leave the Master database items untouched.
- Drop the database master key that was created in the Master database. This should only be done if there are no other user databases in the instance that have TDE enabled. Otherwise, you will want to leave theMaster database items untouched.
- Restart the instance in which the HomeLending database resides. If there are not any other user databases on the instance that have TDE implemented, this action will force the recreation of the TempDBdatabase in an unencrypted format.
Listing 13 shows the code to implement these steps.
USE master;
GO
RESTORE DATABASE HomeLending
FROM DISK = 'D:\HomeLending\Backup\HomeLending.bak'
WITH FILE = 1,
NOUNLOAD,
REPLACE,
STATS = 10;
GO
DROP CERTIFICATE MasterCert;
GO
DROP MASTER KEY;
GO
-- Restart Instance Though SQL Server Management Studio:
-- Right-Click instance and click on "Restart" option.
Listing 13: Reversing TDE when no transactions have occurred.
If the request to reverse the implementation of TDE comes after transactions have occurred, or a copy of the pre-TDE backup file is no longer available, the following steps can be performed:
- Alter the HomeLending database to have the ENCRYPTION option set to the value of OFF.
- Wait until the decryption process is complete. Use the sys.dm_database_encryption_keys DMV to determine its status. A value of "1" returned in the encryption_status column indicates that the decryption is complete.
- Drop the database encryption key for the HomeLending database.
- Restart the instance in which the HomeLending database resides. If there are not any other user databases on the instance that have TDE implemented, this action will force the recreation of the TempDBdatabase in an unencrypted format.
Listing 14 shows the code to implement these steps.
USE HomeLending;
GO
ALTER DATABASE HomeLending
SET ENCRYPTION OFF;
GO
-- Before proceeding, wait until the decryption process is complete.
-- Use the sys.dm_database_encryption_keys dmv to determine this.
DROP DATABASE ENCRYPTION KEY;
GO
-- Restart Instance Though SQL Server Management Studio:
-- Right-Click instance and click on "Restart" option.
Listing 14: Reversing TDE after transactions have occurred.
We will need to keep the certificate and database master key that was setup in the Master database, since there will remain some dependencies upon these keys that affect the recoverability of the database.
It is recommended to perform either of these removal options while the database is not in use by other users. In addition, it is always recommended that a backup be made of the database, the database master key and certificate prior to reversing TDE.
Summary
Through this demonstration we have successfully implemented TDE for the HomeLending database and validated that it is enabled. In addition, the steps to reverse TDE have been provided in the event that it is requested.
While TDE does not offer the granularity of protection that cell-level encryption offers, it does provide a level of protection that cannot be achieved through cell-level encryption. This feature provides a means to render unreadable the plain text that would otherwise be disclosed in the database backup files.
Thanks to the encryption key hierarchy that is external to the database, additional protection is offered by restricting restoration of the encrypted database to the instance from which the backup files were created.
Implementing TDE in conjunction with cell-level encryption provides a layered approach to data security, which enhances its effectiveness. Another option for protecting sensitive data that is available is one-way encryption, also referred to as hashing. One-way encryption can be applied alongside TDE, cell-level encryption and other obfuscation methods.
Discuss 
Briefcase
