(TDE is designed to protect data at rest by encrypting the physical data files rather than the data itself)
--create master key, certificate & enable encryption
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'testpassword';
go
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate';
go
USE AdventureWorks2012;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
GO
ALTER DATABASE AdventureWorks2012
SET ENCRYPTION ON;
GO
--check encryption is set to 3 for target database
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
--backup certificate & key, this will be needed for restoring the db
USE master;
GO
BACKUP CERTIFICATE MyServerCert
TO FILE = 'C:\Databases\backup\MasterCert.bak'
WITH PRIVATE KEY (
FILE = 'C:\Databases\backup\MasterCert.pvk',
ENCRYPTION BY PASSWORD = 'testpassword');
GO
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.
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 AdventureWorks2012 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 AdventureWorks2012 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.
No comments:
Post a Comment