Yesterday, we reviewed multiple encryption strategies available to secure data. Starting with SQL Server 2008, Microsoft introduced Transparent Data Encryption (TDE). Today, we will review TDE in detail.
TDE is also knows as data at rest encryption because the data is encrypted while saving to data store using keys. Anyone without keys will be unable to use data, even if they were able to get hold of it. TDE is a real-time I/O encryption / decryption technique. When data is being saved to the data store, both data and log files are encrypted. Conversely, when being read, the data is decrypted in real time.
The encryption / decryption process uses a database encryption key, also called as DEK. This key is stored in the database boot record to ensure it is available for recovery.
Database Encryption Key is a symmetric key which is secured by a certificate stored in the master database. It could also be an asymmetric key which is protected by an EKM module. Both AES and 3DES algorithms can be used to encrypt/decrypt data at this level. TDE encryption is performed at the page level. Page is encrypted before saving to data store and decrypted when read from the data store.
If you move the database from one server to another, you must decrypt the database first, move the database, restore and then re-enable TDE on the destination server.
When you enable TDE, make sure to backup the certificate and the associated private key. If the certificate ever becomes unavailable, you will need the backups of both the certificate and the private key to read the data.
Even if you remove TDE, make sure to keep the certificate handy because some transaction logs may still be encrypted.
Below is the architectural illustration of TDE
Following commands can be used to enable TDE
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strongpassword>';
go
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate';
go
USE <Your_Database>;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
GO
ALTER DATABASE <Your_Database>
SET ENCRYPTION ON;
GO
Thank you.
TDE is also knows as data at rest encryption because the data is encrypted while saving to data store using keys. Anyone without keys will be unable to use data, even if they were able to get hold of it. TDE is a real-time I/O encryption / decryption technique. When data is being saved to the data store, both data and log files are encrypted. Conversely, when being read, the data is decrypted in real time.
The encryption / decryption process uses a database encryption key, also called as DEK. This key is stored in the database boot record to ensure it is available for recovery.
Database Encryption Key is a symmetric key which is secured by a certificate stored in the master database. It could also be an asymmetric key which is protected by an EKM module. Both AES and 3DES algorithms can be used to encrypt/decrypt data at this level. TDE encryption is performed at the page level. Page is encrypted before saving to data store and decrypted when read from the data store.
If you move the database from one server to another, you must decrypt the database first, move the database, restore and then re-enable TDE on the destination server.
When you enable TDE, make sure to backup the certificate and the associated private key. If the certificate ever becomes unavailable, you will need the backups of both the certificate and the private key to read the data.
Even if you remove TDE, make sure to keep the certificate handy because some transaction logs may still be encrypted.
Below is the architectural illustration of TDE
Following commands can be used to enable TDE
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strongpassword>';
go
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate';
go
USE <Your_Database>;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
GO
ALTER DATABASE <Your_Database>
SET ENCRYPTION ON;
GO
Thank you.
No comments:
Post a Comment
As always, your comments are welcome and appreciated!