Enabling Always Encrypted in Azure SQL

45 minutes
  • 4 Learning Objectives

About this Hands-on Lab

**Scenario**

To help you walk through the lab, consider the following scenario:

You are assuming the role of a Cloud Data Engineer. You’ve been asked to ensure customer data is always encrypted at rest. Staff such as Administrators and Backup Operators should not have access to customer data, even when using tools like SQL Server Management Studio. The unencrypted data should only be available to the application.

In this hands-on lab, you will:

* Create an Azure SQL Database
* Create an Azure Key Vault
* Encrypt data using SQL Server Management Studio (SSMS)
* View encryption results

Learning Objectives

Successfully complete this lab by achieving the following learning objectives:

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.
    • The username and password for the virtual machine can be used for the server admin.
    • 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

*RDP Clients

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

  1. Connect to the server via RDP, logging in with the credentials provided.
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.
  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

Lab Setup

Log in to the Azure portal by right-clicking Open Azure Portal and selecting the option to open it in a new private browser window. (This option will read differently depending on your browser — for example, in Chrome, it reads Open Link in Incognito Window.) Then, sign in using the credentials provided on the lab page.

The objectives for this hands-on lab can be completed using the Azure portal and the provided VM.

RDP Clients We will be using RDP to access our Windows virtual machines in this lab. For MacOS and Linux workstations, you may need to download an RDP application in order to connect to these virtual machines:

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.

Sign In
Welcome Back!

Psst…this one if you’ve been moved to ACG!

Get Started
Who’s going to be learning?