Click here to monitor SSC
  • Av rating:
  • Total votes: 56
  • Total comments: 7
John Magnabosco

Transparent Data Encryption

16 March 2010

 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 Master database 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 the TempDB 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 Master database.

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 contains FILESTREAM 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; namely CONTROL 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 KEY method, 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 Master database. 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 BACKUP CERTIFICATE 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 BY PASSWORD 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 BY argument 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 as dirty 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 HomeLending database.

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.databases catalog 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:

  1. Stop the SQL Server instance.
  2. Start the SQL Server instance.
  3. 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 the sys.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 and sys.dm_database_encryption_keys DMV may raise concern since they do not match; but consider that the sys.dm_database_encryption_keys DMV reflects the date and time in Greenwich Mean Time (GMT) while the sys.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 the HomeLending 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 HomeLending database 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 of REVERT 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 HomeLending database. 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:

  1. Restore the backup file of the HomeLending database that was created before TDE was implemented.
  2. 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.
  3. 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 the Master database items untouched.
  4. 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 TempDB database 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:

  1. Alter the HomeLending database to have the ENCRYPTION option set to the value of OFF.
  2. 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.
  3. Drop the database encryption key for the HomeLending database.
  4. 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 TempDB database 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.

John Magnabosco

Author profile:

John Magnabosco is a writer and a SQL Server Database Consultant. He is also the current President and Co-Founder of the Indianapolis Professional Association for SQL Server (IndyPASS) as well as the Speaker Coordinator and Co-Founder of IndyTechFest. As a database developer and administrator John has had the opportunity to work on databases as small as single user systems and as large as a terabyte enterprise databases in the banking and government arenas.

Search for other articles by John Magnabosco

Rate this article:   Avg rating: from a total of 56 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: Excellent Article!
Posted by: BigE67 (not signed in)
Posted on: Monday, March 22, 2010 at 10:30 AM
Message: Nice.

Subject: Very useful article
Posted by: zmughal (view profile)
Posted on: Thursday, April 01, 2010 at 5:34 PM
Message: This is a very interesting article and explains a lot about internals of TDE. I'm developer in SQL Security team at Microsoft and own some portions of TDE. I'd like to send you my feedback via email if you can give me your email address.

Thanks!

Subject: Some correction about DEK's dependencies
Posted by: zmughal (view profile)
Posted on: Thursday, April 15, 2010 at 6:53 PM
Message:
"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."

- DEK is encrypted by the server certificate which is encrypted by the Master key. If the Certificate is backed up with its private key then it is all you need to decrypt a TDE database on any server. The DEK is encrypted by the Certificate, so you need the certificate's private key to decrypt it. If the private key is backed up to a file along with the certificate then it can be restored on any other server. After that it can be used to decrypt the DEK that it encrypted on another server.


"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"


- This is not correct. All you need to restore the database on another server is the backup of the certificate with the private key. You do not need the database master key and the service master key.

"USE HomeLending;
GO
 
CREATE CERTIFICATE MyHighCert
    WITH SUBJECT = 'Cert used for sensitive class of high';"

- I don't understand exactly why this is required for TDE.

Subject: Certificate on database Homlending
Posted by: lamgak (view profile)
Posted on: Friday, March 18, 2011 at 1:25 AM
Message: USE HomeLending;
GO

CREATE CERTIFICATE MyHighCert
WITH SUBJECT = 'Cert used for sensitive class of high';

Please tell me: certificate on database HomeLending for what?

Subject: TDE in other than SQL 2008, 2008 Enterprise
Posted by: NetLib (view profile)
Posted on: Friday, September 07, 2012 at 1:16 PM
Message: I hope it is ok to mention here that there are alternatives for TDE for other versions/editions of SQL Server. Encryptionizer for SQL Server (http://www.netlib.com) supports TDE in SQL 2000 through 2012 and Express through Enterprise. Among the other differences is that Encryptionizer allows you to encrypt System Databases, such as Master.

Subject: TDE in other than SQL 2008, 2008 Enterprise
Posted by: NetLib (view profile)
Posted on: Friday, September 07, 2012 at 1:17 PM
Message: I hope it is ok to mention here that there are alternatives for TDE for other versions/editions of SQL Server. Encryptionizer for SQL Server (http://www.netlib.com) supports TDE in SQL 2000 through 2012 and Express through Enterprise. Among the other differences is that Encryptionizer allows you to encrypt System Databases, such as Master.

Subject: Check Performance after implementing TDE
Posted by: pratikrawal (view profile)
Posted on: Tuesday, June 18, 2013 at 7:49 AM
Message: Hi,
Thanks for this wonderful article.

How can I check the performacne after implementing TDE on the spcific database.?

thanks in advance for your help.
Pratt.

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

SQL Server XML Questions You Were Too Shy To Ask
 Sometimes, XML seems a bewildering convention that offers solutions to problems that the average... Read more...

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.