How To Implement SQL Server Dynamic Data Masking?

👤 Naveen Sharma | Modified: May 5th, 2023|SQL | 3 Minutes Reading

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.

Create new database

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

Step 3: Insert data into row for testing

Step 4: Apply masking

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

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.