Overview

Introduction

The Data Encryption module provides you with the instructions and devices to develop your hands-on skills in the following topics.

  • Encrypting data using keys
  • Managing column-level encryption
  • Managing encryption and decryption of files using transparent data encryption

Lab time: It will take approximately 60 minutes to complete this lab.

Exam Objectives

Five exam objectives are covered in this lab.

  • Create symmetric and asymmetric keys
  • Encrypt a column of data using transact SQL
  • Perform real-time I/O encryption and decryption of the data and log files using a symmetric database encryption key (DEK)

Exercise 1 - Encrypting Data Using Keys

Encryption facilitates data protection in SQL Server. You can follow a hierarchical encryption structure in SQL Server to safe.guard critical and sensitive database information. There are various types of encryption mechanisms. These are as follows:

  • Certificates
  • Asymmetric keys
  • Symmetric keys
  • Transparent Data Encryption (TDE)
  • Transact-SQL functions

The symmetric and asymmetric keys are not stored inside SQL Server. These are stored by Extensible Key Management (EKM) module outside SQL Server.

In this exercise, you will learn about certificates, asymmetric keys, and symmetric keys by performing the following tasks:

  • Create a database master key.
  • Create a self-signed certificate.
  • Create an asymmetric key.
  • Create a symmetric key.

Exercise 2 - Managing Column-Level Encryption

You can encrypt a column of data in a table using symmetric encryption. You can use Transact-SQL statements to perform symmetric encryption. The symmetric encryption is also known as column-level encryption or cell-level encryption.

In this exercise, you will use symmetric encryption to encrypt a column of data in a database table.

Exercise 3 - Managing Encryption and Decryption of Files Using Transparent Data Encryption

You can encrypt sensitive database data to protect the database from harmful attacks. You can perform real-time I/O encryption and decryption of the data and log files using transparent data encryption (TDE). In this method, a database encryption key (DEK) is used to recover the data. The DEK is stored in the database boot record. The steps to use TDE are as follows:

  • Create a database master key.
  • Create or find a certificate protected by the master key.
  • Create a DEK and protect it using the certificate.
  • Enable the required database to use the encryption mechanism.

In this exercise, you will perform the steps to use TDE.

Comprehensive Learning

See the full benefits of our immersive learning experience with interactive courses and guided career paths.