Learn how dynamic data masking—when enabled on selected columns in your tables—will block out portions or all of the information presented to users without the proper permissions.
- Dynamic data masking is another way to help protect your sensitive information stored in Azure SQL Database. When enabled on selected columns of your tables, SQL Server will block out portions or all of the information presented to users without the proper permissions. You've seen this technique applied when you type in a password into a webform and the characters that you typed get replaced by asterisks or bullet characters. Different data types in your tables will have different types of masks applied. Let's start by creating a new table in our Azure SQL Database to hold the employee records here inside of Management Studio.
I'm currently connected to my Azure Database. We're going to go in a select the KinetEco Database and create a New Query. Now first, I'm going to create a new table here called Employees. The Employees table will have five columns. The first one will be the EmployeeID. It'll be an integer data type and it'll be the identity column for the table. The next column will store the employee name and we'll save it as an nvarchar 50 data type. Next, we'll have a column to store the Social Security Number of the employee as a char 11 data type.
Then we'll store the email address as a varchar 50 data type. And finally a column for salary. It'll story it as a smallmoney data type. Let's go ahead and Execute this query to create the table. And then, we're going to insert a new record into the table. We'll insert an employee named Adam. And he'll have a Social Security Number of 123-45-6789. Adam's email address is email@example.com and the salary is 50,000.
And we're going to highlight lines 10 through 12 just to make sure that I only execute those and press the Execute button. That'll insert the one row into the table. Now just as a quick sanity check, we're going to Select everything out of the Employees table. And once again, I'll highlight line 14 and Execute it. And here, you can see the one employee that was inserted. So far, so good. Now let's go ahead and enable masking on this table. We'll do that back in the Azure Portal. We can find the Settings by going into our SQL databases.
And then clicking on the database that we're working with the KinetEco Database. In the Settings section, I'll come down and I'll click on Dynamic Data Masking. At the top of the next screen, are the masking rules currently enabled, of which we don't have any, but that's about to change. Below that is an area where we can list database user accounts that'll be allowed to see unmasked values. Notice that administrators are always treated as privileged so that they'll always see unmasked values. Keep that fact in mind as it'll come up again in just a moment.
Finally, at the bottom is a listing of the various columns in our database that Azure thinks will make good candidates for masking. Here we can see the Table name and here are the three columns in the Employees table that Azure thinks'll work. I'm going to add a mask for the Social Security Number email, and salary columns by clicking on the Add Mask button next to each. Back at the top, we'll see previews of the masked values. The Social Security Number will use the Default value mask. The email address will use an email mask. And the salary value will also use the Default value mask.
For the Social Security Number, we're going to see it represented as these four x's. The email address we'll see the first character followed by the three letter extension for the domain. And finally, for the salary we're just going to see the number of zero. Let's go ahead and save these changes. I'll press the Save button at the top. And that'll apply its settings to the table. I'll press OK and then we'll check out the results in Management Studio. Now let's see what happens when I Select everything from the Employees table again. I'll make sure that line is still selected and press the Execute button.
Now since we're logged in as the Administrator we're not going to see any change in the table down below. We can create a login list user and view the database from their perspective to see the change. Let's go down to the next couple of lines. And we're going to create a new user named Marcus. Make sure to include the WITHOUT LOGIN keywords. Then we're going to alter the database rule, db_datareader and add Marcus as a member.
Let's highlight lines 16 through 19. And we'll Execute it to create the user and establish his role. Then we're going to execute the next command as the user Marcus. And the command we want to execute is just to Select everything from the Employees table. When I Execute lines 21 through 24, you'll see the results down below including the masking values. Here we have the Social Security Number. It's all x's. The email address is unreadable. And the salary is just reporting as zero.
Let's revert our account, our security contacts back to the dbo account. I'll highlight line 26 now and Execute it. Now we'll be treated back as the database administrator and if I just Select everything from the Employees table again, we'll see that we are now viewing unmasked values once again. So with dynamic data masking, only privileged users will be able to view the full text of your tabular data. The Settings section of the Azure Portal makes it every easy to enable the feature.
This course is also ideal for anybody preparing for the Provisioning SQL Databases (70-765) exam, one of two exams necessary to earn an MCSA: SQL 2016 Database Administration certification.
- Deploying a Microsoft Azure SQL Database
- Planning for a SQL Server installation
- Planning for an IaaS or on-premises deployment
- Evaluating best practices for installation
- Provisioning an Azure virtual machine
- Deploying SQL Server using templates
- Managing SQL Server instances
- Migrating SQL Server databases to Azure VM
- Migrating client applications