Skip to main content

Enabling Always Encrypted in Azure SQL

Hands-On Lab

 

Photo of Dan Sasse

Dan Sasse

Azure Training Architect II

Length

00:45:00

Difficulty

Advanced

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.

What are Hands-On Labs?

Hands-On Labs are scenario-based learning environments where learners can practice without consequences. Don't compromise a system or waste money on expensive downloads. Practice real-world skills without the real-world risk, no assembly required.

Enabling Always Encrypted in Azure SQL

Introduction

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.

Solution

Log in to the Azure Portal using the credentials provided on the lab instructions page.

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. Navigate to Resource groups in the left-hand menu to verify the region our resource group is located in.
  2. Navigate to Virtual networks in the left-hand menu and click Create virtual network.
  3. Set the following values:
    • Name: Anything you'd like (e.g., "SSMSVnet1")
    • Address space: 10.0.0.0/24
    • Resource group: Select the one listed in the dropdown
    • Location: The location we just noted
    • Address range: 10.0.0.0/26
  4. Click Create.
  5. Navigate to All services > Network security groups.
  6. Click Create network security group.
  7. Set the following values:
    • Name: Anything you'd like (e.g., "SSSMSNSG1")
    • Resource group: Select the one listed in the dropdown
    • Location: The same location as before
  8. Click Create.
  9. Once it's deployed, click the name of the NSG.
  10. Click Inbound security rules.
  11. Click Add.
  12. Set the following values:
    • Destination port ranges: 3389
    • Name: Port_3389
  13. Click Add.
  14. Click Subnets
  15. Click Associate.
  16. Click Virtual network and select our listed virtual network.
  17. Click Subnet and select default.
  18. Click OK.

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. Click Virtual machines in the left-hand menu.
  2. Click Create virtual machine, and set the following values:
    • Resource group: Select the one listed in the dropdown
    • Virtual machine name: Anything you'd like (e.g., "SSMSServer1")
    • Region: Select the one listed in the dropdown
    • Image: Windows Server 2019
    • Size: B2s Standard
    • Username: Anything you'd like (e.g., "mythicaladmin")
    • Password: Anything you'd like (e.g., "RUBYmountain135")
  3. Click Next: Disks.
  4. Leave settings as-is and click Next: Networking.
  5. Set the Virtual network to the one we previously created.
  6. Click Next: Management.
  7. Set Boot Diagnostics to Off.
  8. Click Next: Advanced > Next: Tags > Review + create.
  9. Click Create.

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. Navigate to All services > Azure SQL.
  2. Click Create Azure SQL resource.
  3. In the SQL databases card, with Resource type set to Single database, click Create.
  4. Set the following values:
    • Resource group: Select the one listed in the dropdown
    • Database name: Anything you'd like (e.g., "sampleDB1")
    • Server: Create new
      • Server name: Anything unique you'd like (e.g., "sampleserver" with five or six random numbers appended at the end)
      • Server admin login: Anything you'd like (e.g., "mythicaladmin")
      • Password: Anything you'd like (e.g., "RUBYmountain135")
      • Location: The same location as before
      • Allow Azure services to access server: Check
      • Click OK.
    • Compute + storage: Configure database
      • Select Standard and set it to 200 DTUs (i.e., a Standard S04 server).
      • Click Apply.
  5. Click Next: Additional settings, and set the following values:
    • Use existing data: Sample
    • Enable Advanced Data Security: Not now
  6. Click Next: Tags > Review + create.
  7. Click Create.

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. Navigate to All services > Key vaults.
  2. Click Create key vault.
  3. Set the following values:
    • Resource group: Select the one listed in the dropdown
    • Key vault name: Anything unique you'd like (e.g., "samplevault" with five or six random numbers appended at the end)
    • Region: Select the one listed in the dropdown
  4. Click Next: Access policy.
  5. In the Key Permissions column, click Select all for the logged-in lab user.
  6. Click Next: Virtual network > Next: Tags > Review + create.
  7. Click Create.

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).

  1. Navigate to Virtual machines in the left-hand menu, and click the listed VM.
  2. In the Overview tab, copy and paste its public IP address since we'll need it in a minute.
  3. Connect to the server via RDP, logging in with the credentials assigned above.
  4. Once connected, turn off IE Enhanced Security Configuration.
  5. Open Internet Explorer, and browse to this link to download the latest version of SSMS.
  6. Once it's saved to the Remote Desktop client desktop, click to open and install it.

Connect to the SQL Server and Encrypt Some Data

  1. In the Azure browser window, navigate to Azure SQL resources > Overview.
  2. Copy the server name listed for it.
  3. Back in the Remote Desktop client, paste the server name into the SQL Server pop-up.
  4. Change the Authentication to SQL Server Authentication.
  5. Enter the login credentials you created for the server earlier (e.g., "mythicaladmin" and "RUBYmountain135").
  6. Click Connect.
  7. Browse to Databases > sampledb1 > Tables > and right-click on SalesLT.Customer.
  8. Select Encrypt Columns.
  9. Click Next.
  10. On the Column Selection screen, select FirstName, MiddleName, and LastName.
  11. In the Encryption Type column, click the Choose Type dropdown for each and set them to Deterministic encryption.
  12. Click Next.
  13. On the Master Key Configuration screen, select Azure Key Vault.
  14. Click to Sign in to Azure and use the credentials provided on the lab page.
  15. Once signed in, the key vault should autopopulate.
  16. Click Next > Next > Finish.
  17. Click to select the logged-in Azure user account.
  18. The results should be fully encrypted columns.

Conclusion

Congratulations on successfully completing this hands-on lab!