How to configure Dynamic Data Masking?

Microsoft introduced with SQL Server 2016 new feature - Dynamic Data Masking. This feature can be useful if you don't want someone to see sensitive data. Let's say that your company hires DBA contractor for 3 months, but IT Security guy doesn't want to he couldn't see a number of credit cards of our customer. For this purpose, we can use this masking function, be aware that DDM don't use encryption method but masking method.

---

Dynamic Data Masking supports four types of mask:

1 ) Default - The masking depends on data types.

• Binary data types - binary, varbinary and image

• Numeric data types - int, money, numeric, float, real and etc...

• Time and date data types - date, datetime2, datetimeoffset, , time, etc..

• String data types - char/nchar, varchar/nvarchar, text/ntext

2) Custom string - This type show you only first and last letter and add random string in the middle.

3) Random - This method masks random value within specified range of numeric type.

4) Email - It allows display for example first letter, at(@) and .com, such as hXXXX@XXXX.com

DDM doesn't support following:

• Always Encrypted columns

• Filestream data

• Computed columns

• You cannot FULLTEXT index where DDM is implemented.

• Etc...

Implementation of Dynamic Data Masking via T-SQL looks like this.

Now look at Email column before and after configure DDM.

As you can see for non-privileged user named Pigeon are data masked in Email column!

For more info visit official documentation on MSDN.

See you later Alligator.