New Dynamic Data Masking Feature in SQL Server 2016


Microsoft SQL Server 2016 has introduced many new features and Dynamic Data Masking is one such feature useful in some cases. Dynamic data masking main purpose is to limit the exposure of sensitive data by masking it to non-privileged or low privileged users. Dynamic data masking feature helps in preventing unauthorized access to sensitive data by enabling users to only view the sensitive data required with minimal impact on the application layer. This basically hides the data from the user trying to query the data, instead zeros or other symbols are returned instead of actual data. The underlying data does not change, but only the results are not visible to under privileged users. This Dynamic data masking feature is very easy to implement with the existing applications, since masking rules are applied only in the query results. Many applications can mask sensitive data without modifying existing queries.

It is important to understand that this Dynamic Data Masking Feature in SQL Server 2016 is not at all replacement of any of the security features like TDE, Cell-level encryption, SSL, etc, because in Dynamic Data Masking, the underlying data is not converted into another form, rather it is saved in the same format and can be viewed by users with appropriate permissions without any issues or without any requirement of decryption or performing any transformations. The use case of this feature is more for obfuscation of personally identifiable information (PII) from lesser-privileged users. This can also be be used to apply the masking constraints to development or test environments where this information should not be visible to the developers or testers. In the initial CTP releases of this Dynamic Data Masking feature, there were many bugs identified by the community where some tricks could easily reveal the sensitive data to under privileged users, like using alias or temp tables or variables could reveal the sensitive data. Later versions of CTP releases had fixes released to counter the bugs, but still this feature is only has limited usability, but strictly should not be considered as replacement for encryption of data.

Dynamic Data Masking feature supports different types of masks to some or all of the columns in a table which helps in protecting the privacy of the data from low-privileged/normal (non-sysadmin) users. There are four different types of masks currently supported Dynamic Data Masking in SQL Server 2016.
default() – This is for strings to show x for each character up to 4, for numeric types it will show 0, and for dates this will show 2000-01-01.
email() – This only shows the first character, then replaces the remaining characters with
partial() – With this option, we can define a custom string to represent the mask, including how many leading and trailing characters to show from the original string.
random – A random masking function for use on any numeric type to mask the original value with a random value within a specified range.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2016

SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings


Leave a Reply

Your email address will not be published. Required fields are marked *