Implement Azure SQL Database using Always Encrypted

This hour and fifteen-minute virtual lab targets intermediate learners. They demonstrate capabilities of creating a Microsoft Azure database and encrypt it on a virtual machine (VM) with the 2017 SQL Server Management Studio (SSMS). Lab exercises have learners deploying an Azure SQL database, creating an Azure VM image containing a Windows Server 2016 Operating System and a Microsoft SQL Server 2017 developer, and enabling Always Encrypted on a SQL ‘phone’ column.

This hands-on experience sets learners up with an Azure resource group; however, they must create the SQL database and VM to complete the lab exercises. Lab users must plan to take the lab, without interruption, as they cannot pause and return to their work.

Participants check their work by running test queries in Azure and the SSMS. Learners also validate encryption and decryption, configured to the ‘phone’ column, and execute a query on the ‘Phone’ column.

If this lab runs on a Mac, then Microsoft Remote Desktop needs to be installed.

System and data administrators must know how to set up and secure a Microsoft SQL database and data server properly. Regulations around who has access to data have grown stricter with the General Data Protection Regulation (GDPR) and the California Consumer Privacy Act (CCPA).

Understanding the Scenario:

This virtual lab assigns you the role of a system administrator for a company that is migrating its database services from its data center to Azure. You need to create and deploy a Windows Azure VM that hosts Microsoft SQL Server. Finally, you must create an Azure SQL Database, and enable and test Always Encrypted by using SSMS on the Azure VM, as a proof of concept.

Deploy an Azure SQL Database:

In this portion, you build a Windows Azure SQL database with a sample data source and a logical SQL server. You verify that the SQL server and database released successfully by using the Query editor blade to test a query.

You need to specify a connectivity method of a public endpoint to allow Azure services to access the server. To secure the SQL server, you configure a firewall rule for it, by adding a client IP and using your IP address. You limit access to the server via your IP only.

Deploy an Azure VM with SQL Server:

Learners create an Azure VM to enable encryption on a ‘phone’ column in the SalesLT.Customer table. The VM consists of a SSMS 2017 executed from a Windows Server 2016 Datacenter. Participants enable Remote Desktop Protocol (RDP), through selecting the VM’s inbound port.

Configuring this port setting allows access to the VM for the next exercise. The user must wait several minutes for the VM to finish the deployment. The next lab portion requires successful VM construction for login and to work with the SSMS.

Enable and Verify Always Encrypted:

In this exercise, you log into the VM built in the previous lab portion. You connect to the Azure SQL database, created in the first exercise. Then you verify that the SQL database decryption by running a test query.

You use the Always Encrypted wizard to encrypt the ‘Phone’ column. Configuration takes five minutes. Then you verify the encryption by running the same query as specified in a previous step and run parameterization for always encrypted, as needed, as another method to validate Always Encrypted.

Summary:

Upon finishing the lab’s three modules, learners show they understand how to create and secure a Microsoft SQL database in windows. Learners use skills in a Windows environment. Learners:

• Deploy an Azure SQL Database and SQL Server.
• Deploy an Azure VM with SSMS.
• Activate Always Encrypted and check encryption status.