Select Page

Keeping Your Secrets: Dynamic Data Masking in SQL Server 2016

Santosh Bhosale | | February 20, 2019

What is dynamic data masking (DDM)?

Dynamic data masking is a data protection feature that masks, or hides, sensitive data and personally identifiable information. DDM applies a mask to the result set of a query over certain database fields.

The term “dynamic” means that the information is masked on the fly when a user queries the database; the data itself does not change. In addition, users with the appropriate level of permissions can still see the unmasked data on a need-to-know basis.

DDM is a built-in feature for both Microsoft SQL Server 2016 and Microsoft Azure SQL Database. By giving you more control over how data appears in the result set, DDM helps you protect sensitive and confidential information.

The benefits of dynamic data masking

  • DDM makes it easier to prevent the unauthorized disclosure of sensitive data. Administrators can allow privileged users or roles to have access to this data, while restricting access to others.
  • DDM is very simple to configure and use with Transact-SQL commands, and does not require changes in the application code.
  • DDM includes functions for both full masking and partial mask, as well as a random mask to be used on numeric data.
  • DDM can be coordinated via a centralized data masking policy.

The limitations of dynamic data masking

DDM is not a perfect solution for database security. By itself, DDM can’t prevent database users from exposing sensitive data by continually querying the database.

In addition, DDM is not a method for physical data encryption. DDM is intended to be used in conjunction with other data security features in SQL Server, such as encryptionauditing, and Row-Level Security.

Finally, certain actions are not possible with DDM. For example, users can’t define masking rules for Always Encrypted columns, FILESTREAM, or COLUMN_SET. In addition, you can’t use a column with data masking as the key for a FULLTEXT index.

Dynamic data masking functions

DDM includes four types of masking functions that you can use to conceal your sensitive data: default, email, random, and custom.

Default
The default data mask function is applied according to the type of data field:

  • String: Masked as XXXX
  • Date/time: Masked as 01.01.1900 00:00:00.0000000
  • Numeric, binary, or image: Masked as 0

 

Email
The email data mask function displays only the first character of an email address and hides the rest of the address (e.g. “aXXX@XXXX.com”).

 

Random
The random data mask function is used to hide the value of a numeric data type by randomly selecting a numeric value between a specified minimum and maximum.

 

Custom
The custom data mask function lets you create a mask of your own choosing for string data. The first and last letters of the string will be displayed, and you can add a custom padding string in the middle.

Dynamic data masking permissions

  • UNMASK: Users must have this permission in order to reveal the original unmasked data.
  • ALTER ANY MASK: This permission allows users to add, replace, and remove masks on existing columns.
  • CONTROL: This permission includes both the UNMASK and the ALTER ANY MASK permissions.

How to use dynamic data masking

To start using DDM with your database, first decide which columns you want to mask. Select the masking function (default, email, random, or custom) that best fits your needs for each column.

Next, alter the columns in the database to apply the desired masking rules. Remember that the underlying data will not be affected by these changes.

Finally, designate which people should have what level of access to this masked data. Configure the appropriate permissions (UNMASK, ALTER ANY MASK, CONTROL) for the right users and roles.

Read This Next

SQL Server 2017

Want even more functionality in SQL Server? Download our white paper to learn why companies like yours are upgrading to SQL Server 2017.

12c Upgrade Bug with SQL Tuning Advisor

This blog post outlines steps to take on Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017
sharepoint ideas

9 Awesome Things You Can Do with SharePoint

This blog post discusses out-of-the-box uses for SharePoint that you can execute in just a few clicks without using any code.

Amol Gharat | May 23, 2017

Tips for Upgrading From SQL 2008 to 2012 or 2014

It’s 2015 and you can now establish totally respectable MS SQL DBA credibility just by mentioning you have been in the game since SQL Server version 9. You may even get the same gasps of shock from some colleagues that used to be reserved for the version 6 veterans.

Andy McDermid | April 8, 2015

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS