Dynamic Data Masking with SQL 2016 and Dynamics GP 2016

This has been a big year for Microsoft.  We’ve seen the release of Microsoft SQL Server 2016 and the release of Microsoft Dynamics GP 2016.  These products come with a huge list of amazing improvements.

Today I want to talk about Dynamic Data Masking inside of SQL 2016 and GP 2016.  You  can find the full technical guide on MSDN right here.  Let me first start this off by saying this feature only masks the data and shouldn’t be used as an alternative for security measures… Just don’t!



So what is Dynamic Data Masking (DDM) and what does it do to help simplify your life? Well it modifies the results of a query to show data that you have defined a mask on in fashion that hides the actual values.  This is really handy when you have really sensitive data that you don’t want to accidently expose to users who should not see it.  Think in terms of SSN & Credit Cards.  How it works is you would first define what table and columns you want to mask.

The next thing to do is determine which form of masking you are going to use.  Microsoft has four masking options.

    • Default
    • Email
    • Random
    • Custom String

Each of these four items are fully detailed in the above MSDN link so I won’t bore you to death with the details of each.  Now on to how Dynamics GP 2016 is involved in all of this.  Lets say that we have admin clerks in HR who should see some HR information but the critically sensitive stuff like SSN and salary should not be visible to them, how would you go about accomplishing this?  Well if we are talking about a simple SSRS report we could simply have two versions of the object where the admins can see the sensitive data and then a locked down version of the report with the sensitive data being left off the report.  Well that’s fine but if you don’t have to maintain two reports why would you continue to do so?

In the next part of this blog series we will walk through the process of applying a DDM against the UPR00100 which is the Payroll Master table in Dynamics.  We will mask the following columns and see how the data looks inside Dynamics GP 2016 in the inquiry screen as well as SmartList.  We will also see how this works on a SSRS report.

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.