Enabling Always Encrypted in Azure SQL

45 minutes
  • 6 Learning Objectives

About this Hands-on Lab

Part of what defines a modern cloud engineer’s role is privacy concerns. Between the GDPR and the various US states’ attempts to govern PII, it has become more and more important that we understand how to secure data. In this hands-on lab, students will employ one of the most important and common “data at rest” protection mechanisms: Always Encrypted SQL data. We will build a SQL Server and populate a database with sample data, then connect and encrypt a targeted portion of that data.

Learning Objectives

Successfully complete this lab by achieving the following learning objectives:

Create a Virtual Network and Network Security Group

Note: Unless otherwise stated, select the default options or, in the case of the subscriptions and resource groups, the only available option.

  1. Create a virtual network.
    • The name can be anything ("SSMSVnet1" in this example).
    • The primary address space should 10.0.0.0/24.
    • The subnet address range should be 10.0.0.0/26.
  2. Create a network security group.
    • The name can be anything ("SSSMSNSG1" in this example).
    • Create an inbound exception for port 3389.
    • Associate the NSG with the virtual network you just created.
Create a Virtual Machine

Note: Unless otherwise stated, select the default options or, in the case of the subscriptions and resource groups, the only available option.

  1. Create a virtual machine
    • The VM name can be anything ("SSMSServer1" in this example).
    • The VM should be imaged with Windows Server 2019.
    • The VM size should be B2s Standard.
    • Username and password can be anything ("mythicaladmin" and "RUBYmountain135" in this example).
    • The virtual network should be the previously created Vnet ("SSMSVnet1" in this example).
    • IMPORTANT: Set Boot Diagnostics to Off.
Create a SQL Server and SQL Database

Note: Unless otherwise stated, select the default options or, in the case of the subscriptions and resource groups, the only available option.

  1. Create a single SQL database.
    • The database name can be anything ("sampleDB1" in this example).
  2. Create a new server.
    • The server name can be anything unique ("sampleserver#####" in this example).
      • Note: It’s recommended to append a random five- or six-digit number at the end of the server name.
    • Username and password can be anything ("mythicaladmin" and "RUBYmountain135" in this example).
    • Ensure Allow Azure services to access server is checked.
    • Change Compute + storage to Standard, 200 DTUs (or a Standard S04 server).
    • On the Additional settings screen:
      • Under Data Source, select Sample.
      • Set Enable Advanced Data Security to Not now.
Create an Azure Key Vault

Note: Unless otherwise stated, select the default options or, in the case of the subscriptions and resource groups, the only available option.

  1. Create an Azure key vault.
    • The key vault name can be anything unique ("samplevault#####"" in this example).
      • Note: It’s recommended to append a random five- or six-digit number at the end of the vault name.
  2. On the Access policy screen, in the Key Permissions column, click Select all for the logged-in lab user.
Use RDP to Connect to the Virtual Machine and Install SQL Server Management Studio

Use the Remote Desktop client (available from Microsoft for Windows clients natively and Mac clients here).

Note: Browse to the VM first, and take down the public IP address of the server from the Overview tab in the server blade.

  1. Connect to the server via RDP, logging in with the credentials assigned above.
  2. Once connected, turn off IE Enhanced Security Configuration.
  3. Browse to this link and download SSMS.
Connect to the SQL Server and Encrypt Some Data

Continue from the previous step in the RDP session on the VM.

Note: The SQL Server address can be found in the SQL Azure blade, browsing to the Overview tab of the sampledb1 created earlier.

  1. Connect to the SQL Server.
  2. Change the Authentication type to SQL Server Authentication.
  3. Connect using the credentials provided earlier ("mythicaladmin" and "RUBYmountain135" in this example).
  4. (Note : If you are prompted to log into Azure, use the lab provided credentials)
  5. Browse to Databases > sampledb1 > Tables > and right-click on SalesLT.Customer.
  6. Select Encrypt Columns.
  7. Enable Always Encrypted via the wizard.
  8. Select the FirstName, MiddleName, and LastName columns, and set all three to use Deterministic encryption.
  9. Store the key in Azure key vault (log in with the lab Azure credentials).

Additional Resources

In this lab, we will take on the role of a Cloud Security Engineer for the company Mythical Corp. We have been tasked with validating that existing data in Azure SQL can be, in a granular fashion, encrypted. We are required to ensure access to this data going forward, and we are required to ensure future data added to the database tables in concern is also encrypted. Lastly, we must complete all of these tasks from the cloud itself.

What are Hands-on Labs

Hands-on Labs are real environments created by industry experts to help you learn. These environments help you gain knowledge and experience, practice without compromising your system, test without risk, destroy without fear, and let you learn from your mistakes. Hands-on Labs: practice your skills before delivering in the real world.

Get Started
Who’s going to be learning?

How many seats do you need?

  • $499 USD per seat per year
  • Billed Annually
  • Renews in 12 months

Ready to accelerate learning?

For over 25 licenses, a member of our sales team will walk you through a custom tailored solution for your business.


$2,495.00

Checkout
Sign In
Welcome Back!
Thanks for reaching out!

You’ll hear from us shortly. In the meantime, why not check out what our customers have to say about ACG?