Dynamic Data Masking with SQL 2016 and Dynamics GP 2016 – 3

When I started this topic I honestly didn’t think it would become a multiple part series, but alas here we are.  This post focuses on some questions that were asked by various users who’ve read the previous posts.

Q. Can a user update data that has been masked?

A. In the case of Dynamics GP the user is able to update a record that has masked data, but they can not change the values inside of the masked field as seen by the images below.


Changing the XXX to 555-55-5555


When hitting the save button you are greeted by the following error message, and notice your data was not updated.


Unfortunately I don’t have any other applications in my dev environment to test this against, but I believe the above behavior will be the same regardless of the application.  The only thing different may be the error that you get.  The really interesting part is that a user with access to SSMS could write an update statement against the column and change the data in that manner.  But I suspect that is very very very unlikely.


Q. Is there a max number of columns in a table that could be masked?

A.Nope! Side note here though..  If you are going to mask all the columns in a table for a user why not just deny access to the table?

Q. Can a column have more than type of mask applied to it for various users?  Meaning can I use the default masking for test1 and then custom string for test2?

A. The answer is no it can not. If you have already defined a masking type for a column and then add another type of mask to that table the original mask is dropped and replaced with this.  NOTE SSMS does not alert you to this fact! You will want to check existing columns before adding a new mask to a column.  

Q. Is there any way to see which columns have masking in a table?

A. Yes Microsoft has provided the sys.masked_columns DMV for you to examine.


Closing Time

So in closing we see that Dynamic Data Masking has positive and negative attributes.  If you work with Dynamics GP and are looking for an extra layer of comfort for your client DDM is absolutely something you should bring to the table.

Now would this be considered a security feature?  The answer is not because the underlying data is still accessible in various fashions, DDM simply just alters the results of the query.  Microsoft outlines this in their MSDN article here.  Now I have other odd scenarios that I could throw up such as how DDM looks when accessing SQL data via PowerShell, how DDM works with AD accounts but I haven’t had time to sit down and test them.  When I get the results back I’ll simply add them as comments on this post.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.