Enable Always Encrypted using SQL Server

This IT Pro Challenge’s virtual lab trains users to create and deploy a Microsoft SQL Server on an Azure virtual machine (VM) and an Azure SQL Database. Intermediate level learners who have had some experience with Azure and SQL Server Management Studio (SSMS) would benefit from the lab exercises. Mac users will need to have the Microsoft Remote Desktop application to do the challenge. Each section contains a script to check the successful completion of its tasks as well as hints leading to documentation with screenshots.

Always Encrypted ensures that information transmitted from the selected database field/s remains secure between the SQL server and client. For example, a Database Administrator (DBA) can choose to scramble social security numbers and dates of birth through an algorithm, when a client sends the data. In the case of this lab, a VM client hosts the SQL Server Management Studio and encrypts the data.

While Azure stores the data administrator may not see the data. Only the data owner, through the client, can give a key to view the encrypted data contents. Although, administrators may perform some tasks to manage the encrypted data, such as indexing the data for better search performance. Access to data for technical processes (through Azure) without access to data’s contents (controlled by SSMS), demonstrates a good privacy practice and better compliance with laws such as the EU’s General Data Protection Regulation (GDPR).

Create an Azure VM with SQL Server:

Participants set a client responsible for securing data contents on SSMS. Steps require initiating a VM in Azure, connecting to the VM using Remote Desktop Protocol (RDP), and verifying that the SQL server runs. At the end of this exercise, the VM has a SSMS connected through a private Transmission Control Protocol (TCP) ready to display the contents of a SQL database on the server. Users check the VM launches successfully and verify it through the app.

Create an Azure SQL Database:

In this exercise, learners create the SQL database on Azure with a content sample, “AdventureWorksLT.” Trainees will test that the database has been created successfully by running a query on the SalesLT Customer Table. Then the learner will use the SSMS, on the VM client, to access the Azure database and its contents. Both the server and the client can see the text in the database, and encryption will occur during the next step. Algorithms, available within the lab exercise, validates successful setup.

Enable Always Encrypted:

Learners encrypt the phone database column, using the Always Encrypted feature. The user creates a key and sets data encryption to be Deterministic.

Deterministic encryption produces the same cipher every time data contents are communicated over the network. Deterministic encryption can be less secure, as eavesdroppers can pick up patterns and guess the contents. However, this encoding configuration facilitates technical maintenance by allowing SQL grouping and joins on the encrypted data.

After encrypting the phone column, lab participants validate by a query on the Azure SQL that phone contents do not show. Learners then disable the encryption configurations in the SSMS, for the phone column, and views the text.

Summary:

Learners know how to use Always Encrypted, designed to secure Azure SQL database contents. This lab provides hands-on experience through three modules.

• • Create an Azure VM with SQL Server.
• • Create an Azure SQL Database.
• • Enable Always Encrypted.

Those learners that wish to maximize skills securing data contents may want to consider other labs.

• GUIDED CHALLENGE: Configure Geo-Replication for Your Database

• GUIDED CHALLENGE: Enable Dynamic Data Masking Using Azure SQL Database