Thursday 4 July 2019

SQL Server : How to configure Transparent Data Encryption (TDE)?

How to configure Transparent Data Encryption (TDE)?
USE master;
GO
 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password@123'; -- Create master Key

/*command to view Keys*/
SELECT name KeyName,
  symmetric_key_id KeyID,
  key_length KeyLength,
  algorithm_desc KeyAlgorithm
FROM sys.symmetric_keys;



CREATE CERTIFICATE TDECerti WITH SUBJECT = 'TDE certificate'; --Create Certificate

/*command to view certificate */
SELECT name CertName,
  certificate_id CertID,
  pvt_key_encryption_type_desc EncryptType,
  issuer_name Issuer
FROM sys.certificates
WHERE issuer_name = 'TDE certificate';


/* create DEK */

USE SQLDummyDB;
GO
 
CREATE DATABASE ENCRYPTION KEY 
WITH ALGORITHM = AES_256 
ENCRYPTION BY SERVER CERTIFICATE TDECerti;


/*Command to view all DEK */
SELECT DB_NAME(database_id) DbName,
  encryption_state EncryptState,
  key_algorithm KeyAlgorithm,
  key_length KeyLength,
  encryptor_type EncryptType
FROM sys.dm_database_encryption_keys;



ALTER DATABASE SQLDummyDB SET ENCRYPTION ON; --Enable TDE

Backup the certificate and keys
Use master;
GO
 
BACKUP SERVICE MASTER KEY 
TO FILE = 'E:\MSSQLServer\Backup\SvcMasterKey.key'
ENCRYPTION BY PASSWORD = 'Password@123';


BACKUP MASTER KEY 
TO FILE = 'E:\MSSQLServer\Backup\DbMasterKey.key'
ENCRYPTION BY PASSWORD = 'Password@123'


BACKUP CERTIFICATE TDECerti 
TO FILE = 'E:\MSSQLServer\Backup\TdeCerti.cer'
WITH PRIVATE KEY(
  FILE = 'E:\MSSQLServer\Backup\TdeCerti.pvk',
  ENCRYPTION BY PASSWORD = 'Password@123');
GO
/* Restore Certificate Key */
USE Master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='DPassword@123';
GO


USE MASTER
GO
Create CERTIFICATE TDECerti
FROM FILE = 'D:\Backups\TdeCerti.cer'
WITH PRIVATE KEY (FILE = 'D:\Backups\TDECerti.pvk',
DECRYPTION BY PASSWORD = 'Password@123' );
GO
Advantages
1. The performance impact of TDE is minor.
2. Performs real-time I/O encryption and decryption of the data and log files.
3. No application code changes are required.
4. No architectural changes required.
5. Encrypts the Entire data in Database.

Disadvantages
1. Not granular – Cannot just encrypt specific tables/columns
2. Not good for high CPU bottleneck servers
3. No protection for data in memory
4. Not protected through communication/networks

4 comments:

  1. Greetings! Very useful advice within this article!
    It's the little changes that will make the largest changes.
    Thanks a lot for sharing!

    ReplyDelete
  2. Hey there, You have done a fantastic job.
    I'll definitely digg it and personally suggest to
    my friends. I'm sure they will be benefited from this
    web site.

    ReplyDelete
  3. Its such as you read my thoughts! You appear to grasp so much approximately this,
    like you wrote the guide in it or something. I believe that you just could do with some percent to power
    the message home a little bit, but instead of
    that, this is wonderful blog. A great read. I will certainly
    be back.

    ReplyDelete
  4. What a information of un-ambiguity and preserveness of valuable familiarity on the topic of
    unexpected emotions.

    ReplyDelete