Enable Dynamic Data Masking using Azure SQL Database
In this hands-on lab, learners are introduced to the skills required to secure the Azure SQL. Exercises in this lab teach users how to enable dynamic data masking using a logical server and query editor while learning the Azure concepts. The topics covered in this lab are crucial for learners to be effective in Azure administrator roles in IT.
Azure is a dynamic and continually evolving environment. In the Azure SQL database, Dynamic data masking restricts illegal access to delicate data by allowing clients to choose how much of the delicate data to share with minimum influence on the application layer. It’s a policy-based security characteristic that protects the delicate data.
In this challenge, learners will create an Azure SQL Database and enable dynamic data masking to protect the database. For example, with the help of DDM, Azure administrators can protect personally identifiable information (PII) data, so that a developer can query the production database for troubleshooting goals without disrupting compliance regulations. The other guided, advanced, and expert challenges in this series are:
- Configure Geo-Replication for Your Database.
- Can you Enable Always Encrypted Using SQL Server?
- Can you Configure an Autoscale Alert on a VM Scale Set?
Understand the Scenario
In this virtual lab, you are an Azure administrator for a company that is migrating its primary web app from its on-premises datacenter to Azure. Your job responsibility is to create and deploy a new Azure SQL Database and enable dynamic data masking to protect sensitive columns, as a proof of concept. To accomplish this task, you have to deploy the database, then enable and test the configuration. Learners will use an Azure resource group that initially contains no resources. They will create the necessary resources to complete the challenge.
Create an Azure SQL Database
Azure SQL Database is the smart, scalable, cloud database that gives the most comprehensive SQL Server engine adaptability. In this first section of the lab, you will create an Azure SQL Database named db12234483 on a new logical server named sql12234483 within the East US region. After this, for Compute + storage, you will use Standard S0 with 10 DTUs and 250 GB. For the proof of concept, you will use the Sample named AdventureWorksLT as the Data Source. Next, learners will write a query to create a user named user1 in the database and add another statement to add the user to the db_datareader role. Finally, you will run and save the query file as user1.sql and verify that the EmailAddress column is fully visible to the user and save the query as query.sql in the folder of your choice.
Enable dynamic data masking
Dynamic data masking (DDM) strives at real-time data masking of production data. DDM transforms the data stream so that the data requester does not get delicate data access, while no physical modifications to the initial production data occur. In this section of the lab, you will:
- Use the Dynamic Data Masking blade, to add a mask and add the Email Masking field format for the EmailAddress column in the SaleLT.Customer table.
- Use the Query editor blade for the new database to log in as user1 and test a query to select all the rows from the SalesLT. Customer table and verify that the EmailAddress column is now masked to the user.
- Use the Dynamic Data Masking blade to modify the Masking field format to Custom string for the EmailAddress column in the SaleLT.Customer table so that the first two and last four characters are visible and that the characters XXX@XXX are in the middle.
Finally, you will check that the dynamic data masking is enabled using an email mask, verified, and the mask has been modified to use a custom mask.
Test dynamic data masking
Dynamic data masking is a policy-based security characteristic that protects the delicate data in the outcome set of a query over selected database fields. In contrast, the data in the database is not altered. In this section of the lab, you will:
- Use the Query editor blade for the new database to log in as user1 and test a query to select all the rows from the SalesLT.Customer table. Verify that the custom mask is masking the EmailAddress column to the user.
- Login as admin testuser and test a query to verify that the EmailAddress column is visible to the admin user.
- Use the Dynamic Data Masking blade to exclude user1 from masking and use the Query editor blade for the new database to log in as user1 and test a query to verify that the EmailAddress column is now visible to the user.
Lab Summary Conclusion
After completing the “Enable Dynamic Data Masking using Azure SQL Database” virtual lab, you will have accomplished the following:
- Created an Azure SQL Database.
- Enabled dynamic data masking.