How To Implement SQL Server Dynamic Data Masking?

Naveen Sharma | Modified: July 12th, 2018 | SQL |

SQL Server 2016 added a new data protection feature known as Data Masking in SQL Server. It allows user to limit the sensitive data by masking it to unauthorized users. User can hide the sensitive data without changing the data of the database, since masking is applied to the query result. It allows users to mask the sensitive column of a table present in SQL Server 2016 Database. Dynamic Data Masking in SQL Server Database is useful for regulatory compliance, as protection of sensitive data may be demanded by the government or by any other industrial body.

For example, a support person may identify their customers by their credit card number or by social security number. However, those numbers are exposed partially to the support person in case of Data Masking.MS SQL Server Dynamic data masking can be applied here to mask all the numbers except the last 4 digits. For example an executive can view the credit card number as XXXX-XXXX-XXXX-1234.

How to define Dynamic Data Masking in SQL Server?

You can define the Dynamic data masking rule on a column to protect your sensitive data. There are mainly four types of mask that are available for SQL Server 2016. They are; Default, Custom String, Email and Random.

Function

Description

Default

You can apply full mask according to the data type of fields.

  • XXXX is used for string data type. i.e, char, varchar, text etc.
  • A ‘0’ value is used for numeric data types such as int, decimal, float etc.
  • ASCII value0 is used for binary data types.
  • 01.01.2000 can be used for date and time datatypes

Email

All the letters of emails are masked except the first letter,@ and the suffix .com. For example sxxxxxxxxxxxx@xxxxx.com

Custom String

Expose first and last letters and masking is performed on middle part. For example Prefix, MASK, Suffix

Random

Random masking can be used for any numeric type, to mask the real value with random values.

Limitations of MS SQL Server Dynamic Data Masking

Dynamic data masking rule cannot be applied on following column types:

  • COLUMN_SET
  • FILESTREM
  • Encrypted columns

Setting Up Dynamic Data Masking in MS SQL Server Database

Step 1: Create a new database Masking Demo. If masking Demo database already exists then drop the database and create it again.

Drop Database

Create new database

Create Database

Step 2: Create Table with different data types of columns.

Create Table

Step 3: Insert data into row for testing

Insert Row

Step 4: Apply masking

Apply Masking

Step 5: Create a test user and test permissions to the table

Create New User

How to Change or Remove a Mask?

To change the Mask definition of an existing column, run the alter column command.

Alter Table MyContacts Alter Column Credit Card
Add Mask With (Function =’partial(1,XXX-XXXX-XXXX-XXX,1)’);

To remove the applied mask, use Drop Masked

Alter Table MyContacts Alter Column Credit Card Drop Masked;

Hence, Dynamic Data Masking in SQL Server allows users to protect their sensitive data according to their requirement by concealing the confidential data present in table of SQL Server. However, you should also keep in mind that data masking is not the true encryption and it will not protect your data in all scenarios.