A Complete Rundown of Implementing Transparent Data Encryption for Data at Rest | Nitor Infotech
Send me Nitor Infotech's Monthly Blog Newsletter!
×
nitor logo
  • Company
    • About
    • Leadership
    • Partnership
  • Resource Hub
  • Blog
  • Contact
nitor logo
Add more content here...
Artificial intelligence Big Data Blockchain and IoT
Business Intelligence Careers Cloud and DevOps
Digital Transformation Healthcare IT Manufacturing
Mobility Product Modernization Software Engineering
Thought Leadership
Aastha Sinha Abhijeet Shah Abhishek Suranglikar
Abhishek Tanwade Abhishek Tiwari Ajinkya Pathak
Amit Pawade Amol Jadhav Ankita Kulkarni
Antara Datta Anup Manekar Ashish Baldota
Chandra Gosetty Chandrakiran Parkar Deep Shikha Bhat
Dr. Girish Shinde Gaurav Mishra Gaurav Rathod
Gautam Patil Harish Singh Chauhan Harshali Chandgadkar
Kapil Joshi Madhavi Pawar Marappa Reddy
Milan Pansuriya Minal Doiphode Mohit Agarwal
Mohit Borse Nalini Vijayraghavan Neha Garg
Nikhil Kulkarni Omkar Ingawale Omkar Kulkarni
Pooja Dhule Pranit Gangurde Prashant Kamble
Prashant Kankokar Priya Patole Rahul Ganorkar
Ramireddy Manohar Ravi Agrawal Robin Pandita
Rohan Chavan Rohini Wwagh Sachin Saini
Sadhana Sharma Sambid Pradhan Sandeep Mali
Sanjeev Fadnavis Saurabh Pimpalkar Sayanti Shrivastava
Shardul Gurjar Shravani Dhavale Shreyash Bhoyar
Shubham Kamble Shubham Muneshwar Shubham Navale
Shweta Chinchore Sidhant Naveria Souvik Adhikary
Sreenivasulu Reddy Sujay Hamane Tejbahadur Singh
Tushar Sangore Vasishtha Ingale Veena Metri
Vidisha Chirmulay Yogesh Kulkarni
Big Data | 07 May 2021 |   11 min

A Complete Rundown of Implementing Transparent Data Encryption for Data at Rest

featured image

In 2006, British mathematician and data science entrepreneur Clive Humby famously coined the phrase “Data is the new oil” which simply translates to data surpassing oil as the world’s most valuable commodity.This obviously brought with it the very real threat posed by cyber criminals who stole valuable information for financial gains.

Data encryption has since then proved to be a robust solution for this problem. Encryption protects confidential data from attackers and when thinking about it, you should consider three cases:

  • Encrypting your data at rest
  • Encrypting your data in transit
  • Encrypting your data in use

In this blog, I will show you how you can implement Transparent Data Encryption (TDE) for Data at rest. We will cover data in transit and data at rest in my subsequent blogs. But for now, let’s start with understanding TDE.

Transparent Data Encryption (TDE) is a tool that is primarily used to protect data by encrypting the physical files or ‘data at rest’, both the data (mdf) and log (ldf) files (as opposed to the actual data stored within the database) in SQL Server 2008 enterprise edition using an Encryption key management (EKM) system.

But, before we move any further, I urge you to make note of the eligible editions with which you can deploy this encryption. Here’s a list:

  • SQL 2016 Evaluation, Developer, Enterprise
  • SQL 2014 Evaluation, Developer, Enterprise
  • SQL Server 2012 Evaluation, Developer, Enterprise
  • SQL Server 2008 R2 Datacenter, Evaluation, Developer, Enterprise, Datacenter
  • SQL Server 2008 Evaluation, Developer, Enterprise

Choosing a correct version of SQL server that allows TDE is key. Some may say that this is an expensive feature since it needs enterprise editions. However, for the sake of testing and development, a developer edition works just as well.

Hierarchy of Encryption

Here is what the hierarchy of encryption looks like:

So, based on that, the tool that we will be using today is an Advanced Encryption Standard provided by SQL Server TDE.

With the help of this, we will take on a solution approach as follows:

For TDE Implementation:

  1. SQL server provides Transparent Data encryption (TDE) feature with the Enterprise edition of SQL server 2008 version & above.
  2. This is used to ensure data security by encrypting all physical files including backup, mdf and ldf
  3. The list of keys & certificates to be created as a part of TDE implementation on the SQL server database are:
    1. Service Master Key: Protects the database master key created at SQL server instance setup
    2. Database Master Key: Database Master key certificate’s private key in Master Database
    3. Master Certificate: Guards the database encryption key in the User database
    4. Database Encryption Key: Connects the certificate with the actual database


Based on this approach, the performance impact can be observed as follows:

Encryption is CPU intensive so applications with high CPU usage will suffer a performance loss of approximately 28%.

Implementing TDE on database

Here are the 5 steps that we must perform to use TDE in SQL server.

  1. Create a Master Key
  2. Create a certificate protected by the master key
  3. Backup the certificate
  4. Create a database encryption key and protect it by the certificate
  5. Enable the encryption on the database

Now that we have our approach in place, let’s see an example that will help us understand better. We will look at TDE on Source Server (Server1) as follows.

  1. Create a Master Key [Set the complex password]

create master key encryption by password=’P@$$w0rd12’/*P@$$w0rd12= Any Complex Password Can be given */

select * from sys.symmetric_keys

  1. Create a certificate protected by the master key.

create certificate SQLTDECert /*SQLTDECert is the name of the Cerficate */
with subject='Certificate to protect TDE key'

As soon as the certificate is created, it is always recommended to take the backup of the certificate.

  1. Backup the Certificate

BACKUP CERTIFICATE SQLTDECert
TO FILE = 'D:\TestTDE\MSSQLCert'/* Creates Cerficate File at specified path */
WITH PRIVATE KEY (file='D:\TestTDE\MSSQLCertKey', /*Creates Private Key File at the specified Path */
ENCRYPTION BY PASSWORD='P@$$w0rd122') /* Password is important to remember */
select * from sys.certificates

  1. Create Database Encryption Key (DEK)

use TestTDE
Go
create database encryption key
with algorithm=AES_128    /* By using AES-128 algorithm the database will be encrypted */
encryption by server certificate SQLTDECert;
Go

  1. Enable the encryption on the database

Alter database TestTDE
set Encryption on;
Go

Steps to Perform on the Destination Server (Server2)

  1. Copy the Certificate and Private key backup files to the destination server.

Note: The path of the source and destination should be same.

  1. Create a master key

create master key encryption by password='P@$$w0rd12'/* Any Complex Password Can be given */

  1. Restore/Create certificate from the certificate backup file

CREATE CERTIFICATE SQLCertificateTDE
from FILE = 'D:\TestTDE\MySQLCert'
WITH PRIVATE KEY (file='D:\TestTDE\MySQLCertKey',
DECRYPTION BY PASSWORD='P@$$w0rd122'); /*Password Should be same as the Source */
Go

  1. Take the backup of the database
  2. Restore on the destination

Enable TDE for Multiple Databases

Repeat the steps 4 and 5 depending on how many databases are needed to implement TDE.

Key Rotation in TDE

When we create a certificate, by default the expiry date of the certificate will be one year.

To rotate the certificate, the first thing we need to do is create a new certificate as in step 2 of the source.

USE TDE_New
GO
ALTER DATABASE ENCRYPTION KEY ENCRYPTION BY SERVER CERTIFICATE TDE_New

Next, backup the certificate and recreate it on the destination server.

Here, handling restores is a challenging task.

e.g.: If we rotate the certificates on Tuesday afternoon, and then we restore Monday night’s backup, we will need the older certificate on the system. If we need a restore on Wednesday, we should use the new certificate.

So before rotating the Keys, it is recommended to stop the backup jobs.

In conclusion, we have successfully seen how we can use SQL server to implement Transparent Data Encryption (TDE) for physical data to gain an added layer of protection for data at rest.

To learn more about deriving insights from data, peruse through our Big Data services and reach out to us at Nitor Infotech to know how we leverage Blockchain to ensure secure and affordable transaction tracking.

Related Topics

Artificial intelligence

Big Data

Blockchain and IoT

Business Intelligence

Careers

Cloud and DevOps

Digital Transformation

Healthcare IT

Manufacturing

Mobility

Product Modernization

Software Engineering

Thought Leadership

<< Previous Blog fav Next Blog >>
author image

Chandra Gosetty

Lead Engineer

Chandra is an enthusiastic SQL DBA responsible for implementation, configuration, maintenance, and performance tuning of critical SQL Server systems. He is also responsible for ensuring availability and consistent performance of applications. He is Microsoft certified and has completed 70-764 Certification. He believes in giving a proper solution to any technical issue in SQL Server. He is also responsible for SQL Server databases to remain online by implementing the high availabilities like Logshipping, Replication, Always On and Clustering. He has honed his skills by learning MSBI and PowerBI. He is also skilled in Azure SQL Administration.

   

You may also like

featured image

10 Heuristic Principles in UX Engineering

Say, you’ve built a modern, cutting-edge application. It has a complex, multi-layered user interface (UI), that is the basis for some amazing features. Since you’re the one who has built the applic...
Read Blog


featured image

ETL Testing: A Detailed Guide

Just in case the term is new to you, ETL is defined from data warehousing and stands for Extract-Transform-Load. It covers the process of how the data is loaded from the multiple source system to t...
Read Blog


featured image

Getting Started with ArcGIS Online

GeoServer is an open-source server that facilitates the sharing, processing and editing of geospatial data. When we are dealing with a large set of geospatial d...
Read Blog


subscribe

Subscribe to our fortnightly newsletter!

We'll keep you in the loop with everything that's trending in the tech world.

Services

    Modern Software Engineering


  • Idea to MVP
  • Quality Engineering
  • Product Engineering
  • Product Modernization
  • Reliability Engineering
  • Product Maintenance

    Enterprise Solution Engineering


  • Idea to MVP
  • Strategy & Consulting
  • Enterprise Architecture & Digital Platforms
  • Solution Engineering
  • Enterprise Cognition Engineering

    Digital Experience Engineering


  • UX Engineering
  • Content Engineering
  • Peer Product Management
  • RaaS
  • Mobility Engineering

    Technology Engineering


  • Cloud Engineering
  • Cognitive Engineering
  • Blockchain Engineering
  • Data Engineering
  • IoT Engineering

    Industries


  • Healthcare
  • Retail
  • Manufacturing
  • BFSI
  • Supply Chain

    Company


  • About
  • Leadership
  • Partnership
  • Contact Us

    Resource Hub


  • White papers
  • Brochures
  • Case studies
  • Datasheet

    Explore More


  • Blog
  • Career
  • Events
  • Press Releases
  • QnA

About


With more than 16 years of experience in handling multiple technology projects across industries, Nitor Infotech has gained strong expertise in areas of technology consulting, solutioning, and product engineering. With a team of 700+ technology experts, we help leading ISVs and Enterprises with modern-day products and top-notch services through our tech-driven approach. Digitization being our key strategy, we digitally assess their operational capabilities in order to achieve our customer's end- goals.

Get in Touch


  • +1 (224) 265-7110
  • marketing@nitorinfotech.com

We are Social 24/7


© 2023 Nitor Infotech All rights reserved

  • Terms of Usage
  • Privacy Policy
  • Cookie Policy
We use cookies to ensure that we give you the best experience on our website. If you continue to use this site we will assume that you are happy with it. Accept Cookie policy