Dynamic Data Masking – SQL Server

Way back in earlier days of Azure SQL Database, Microsoft released Dynamic Data Masking (DDM) and then included it in the release of SQL Server 2016. Dynamic Data Masking allows for the masking of sensitive data in the result set. It does not require changing any of the actual data in the database, it simply applies a masking rule to the results of the query.

For deep technical details into DDM, you can visit the official page by Microsoft here. I’d like to share my experience and some high level points of interest, as well as a short demo of the feature.

First I should point out that in most cases, before you start on a project of securing and masking data, you should have an overall data classification process to identify where all sensitive data resides and who ultimately needs what level of access.

In order to mask data with DDM, you have to select a masking function. There are 5 mask types available.

Masking Types

Default Masking

When using the default masking function, masking will be applied based upon the data type. The numeric data type would be masked as 0 (zeros), those of string data type will be masked with X’s. Any date time data types would be masked to Jan 1st, 1900 00:00:00.

Email

If you are needing to mask email addresses, this works really well. It shows the first letter in the email address, then x’s @ x’s, then .com. For example tim.radney@wideworldimporters.net would be txxx@xxxx.com

Random

This masking function allows you to mask a numeric type to a random value.

Datetime

This allows you to mask parts of datetime, such as the year, hour, minute, or seconds of the day.

Custom String

This masking function exposes the first and last letter and allows you to insert a custom padding in the middle.

My Thoughts

I’ve implemented DDM with a lot of success for various clients. It is a fantastic way to mask data in reports or applications where sensitive data is exposed because of how a report or application was written. In most cases, much more data is exposed than the end user needs to have in order to do their job. With DDM, you can start limiting what data is exposed by masking it.

In the video below, I go through and show various demos on how DDM can help protect data, and show how it can also circumvented in some cases. The example I show is if you are masking a field that someone can query on, they can eventually figure things out. Although I couldn’t see the value, I was able to keep adjusting my search to narrow down the value. While there are ways to ‘exploit’ the masking, in most cases, that is not going to be an issue with the end users level of access. Where I’ve seen huge success with using DDM is on social security numbers, account numbers, email addresses, dates of birth, etc.