SQL Server 2016 Dynamic Data Masking Demonstration Script

Demo Overview

The focus of the demo outlined here is to showcase Dynamic Data Masking security feature in SQL 2016. The demo uses the SQL Server 2016 in an Azure VM.

The demo uses the standard tools that accompany Windows Server 2012 R2, SQL Server 2016 and Microsoft Azure.

Note: Practice the demos at the screen resolution that will actually be used as this impacts the available real estate for window layout. This is especially important when running remote desktops. Avoid nasty surprises!

Dynamic Data Masking Background

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. This demo is focusing on show casing various examples using these T-SQL elements.

Setup

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.

Demo Scripts

Script 1 – How to configure Masking rules and creating privileged logins using SQL cmdlets.

  • Open SQL Server 2016 Management studio and configure different masking policies.
    • Follow steps in scenario one from Dynamic Data Masking lab guide to complete this.
  • T-SQL Scripts used in the scenario are as follows:
    USE [AdventureWorks2016CTP3]

  • T-SQL script to create 2 Users, Authorized and Unauthorized:

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

Script 2 – Grant/Revoke Different Masking Permissions

  • Open SQL Server 2016 Management studio and run the script to grant/revoke masking permissions.
    • Follow steps in scenario two from 'Dynamic Data masking' lab guide to complete this.
  • T-SQL Scripts used in the scenario are as follows:

    USE [AdventureWorks2016CTP3]

  • Walkthrough on the result returned from each permission granted:

Script 3 – Deleting Masking Rules

  • Open SQL Server 2016 Management studio and run the script for deleting masking rules.
    • Follow steps in scenario three from 'Dynamic Data masking' lab guide to complete this.
  • T-SQL Scripts used in the scenario are as follows:

  • Walkthrough on the result returned from deleting masks: