SQL Server 2016 Dynamic Data Masking Hands-on Lab

Overview

Dynamic data masking limits sensitive data exposure by masking it to non-privileged users.

It's a data protection feature that hides the sensitive data in the result set of a query over designated database fields, while the data in the database is not changed. Dynamic data masking is easy to use with existing applications, since masking rules are applied in the query results.

Setup

Prerequisite

There is no infrastructure prerequisite observed for this lab. User is expected to have a valid Azure subscription.

Create SQL VM on Azure using SQL VM Image

Estimated Time to Complete: 30 minutes

  1. Sign in at the Microsoft Azure Management Portal with the logon credentials
  2. Select Virtual Machines located on the side navigation panel on the Microsoft Azure Management Portal page
  3. Click the +NEW button located on the bottom navigation bar and select Compute | Virtual Machines | From Gallery.
  4. In the Virtual Machine Operating System Selection list, click SQL Server 2016 CTP 3 Evaluation - Windows Server 2012 R2 and select deployment model as Resource Manager. Click on Create

    Note: You can choose other images with different edition / version of SQL Server, as required.

  5. On the Virtual Machine Configuration Basics Page, complete the fields as follows:
    - Virtual Machine Name:
    DevTestSQL02 
    - New User Name: Choose a secure local Administrator user account to provision.
    - New Password and Confirm Password fields: Choose and confirm a new local Administrator password.

    - Resource Group Name: HOLRG02

    Click OK.

    Note: If the VM Name or Resource group name is already taken, choose another name.

  6. On the Virtual Machine Configuration Size page, complete the fields as follows:

    - Size: A2 (2 cores, 3.5GB Memory)

  7. On the Virtual Machine Configuration Settings page, leave the default values as it is and click OK
  8. On the Virtual Machine SQL Server Settings page, leave the default values as it is and click OK
  9. On the Virtual Machine Configuration Summary page, validate the configuration and click OK to start provisioning the VM.
  10. After the new virtual machine has finished provisioning (it may take about 5 to 10 minutes), click on the name (DevTestSQL02) and remote to the VM using local administrator credentials.

A new virtual machine has been provisioned based on the supplied Windows Server 2012 R2 OS, SQL Server 2016 virtual machine template as shown in figure 1.

Figure 1 DevTestSQL02 Virtual Machine provisioned from the Azure Management portal

  1. Open SQL Server 2016 Management Studio and Connect to SQL Server Instance using Windows Authentication.

Figure 2 SQL Management Studio 2016 with connection to Microsoft Azure virtual machine DevTestSQL02 database instance

Download AdventureWorks sample databases and restore to SQL instance.

Estimated Time to Complete: 15 minutes

  1. Remote to the VM using local administrator credential.
  2. Open Server Manager (located on the task bar near to start button), click on local server and turn off IE Enhanced Security for Administrators (you can turn it on after the downloads)
  3. Go to http://www.microsoft.com/en-us/download/details.aspx?id=49502 , select and download the AdventureWorks2016CTP3 database back up (.bak) file to a folder.

    Note: The provided link is mapped to SQL 2016 CTP3 version downloads. Look for the latest version.

  4. Open SQL Server 2016 Management Studio and connect to the default instance using Windows credentials.
  5. Right click on the Server and click Restore Database, on the wizard select Device and
    browse for the
    AventureWorks2016CTP3.bak
    file downloaded. Click OK to start the restore.
  6. Wait (it will take 1 – 2 minutes) for the "Database Adventureworks2016CTP3 restored successfully" message and click OK
  7. Right click on the database restored and click on New Query, One the SQL Query window issue the below command and verify result set is returned successfully:
    Select top 10 * from Sales.Customer

From SQL management studio, you can see the AdventureWorks2016CTP3 database created on DevTestSQL02

Figure 3 Customer records displayed through the Management Services

Figure 3 executes against the management service database usage table and returns customer records.

Scenario 1: Configuring Masking rules and creating privileged logins using SQL cmdlets.

Estimated Time to Complete: 15 minutes

  1. Remote to the VM using local Administrator credential.
  2. On the VM, sign in to SQL 2016 Management Studio using the windows credentials (default), right click the instance and open 'New Query'
  3. Issue the below script to change the query context to AdventureWorks2016CTP3 database
    USE [AdventureWorks2016CTP3]
  4. Issue the below command to Issue the below commands to Alter the table definition [Sales]. [Client] to mask the PII information i.e. LastName/PhoneNumber/Email and table [Sales]. [SalesOrderHeader] to mask AccountNumber using different Masking Functions.

  1. Issue the below command to create 2 Users, authorized and Unauthorized.

Post Condition

You should be able to see masked data for tables Sales.Client and Sales.SalesOrderHeader once the proper permissions are granted to the users created above.

Scenario 2: Grant/Revoke Different Masking Permissions

Estimated Time to Complete: 15 minutes

  • Remote to the VM using local Administrator credential.
  • On the VM, sign in to SQL 2016 Management Studio using the windows credentials (default), right click the instance and open 'New Query'
  • Issue the below script to change the query context to AdventureWorks2016CTP3 database

    USE [AdventureWorks2016CTP3]

  • Issue the below script to GRANT UNMASK permission to Authorized User.

  • Issue the below script to GRANT only the SELECT permission to Unauthorized User.

  • Issue the below script to REVOKE UNMASK permission from Authorized User.

Post Condition:

You should be able to see following screenshots:

  1. After granting the UNMASK Permission, AuthorizedUser retrieves unmasked data from the columns for which masking is defined.

    On Lab

  1. After granting only the SELECT Permission, UnauthorizedUser retrieves masked data.

  1. After revoking the UNMASK Permission, UnauthorizedUser now retrieves masked data.

Scenario 3: Deleting Masking Rules

Estimated Time to Complete: 15 minutes

  • Remote to the VM using local Administrator credential.
  • On the VM, sign in to SQL 2016 Management Studio using the windows credentials (default), right click the instance and open 'New Query'
  • Issue the below script to change the query context to AdventureWorks2016CTP3 database

    USE [AdventureWorks2016CTP3]

  • Issue the below script to DELETE Masking rules.

Post Condition:

You should be able to see that after deleting masks from each column, UnauthorizedUser retrieves unmasked data:

On Lab