Static Data Masking with SSMS 18

     I have been developing web application for long time. When it's time for customers or sponsors to test the web application in non-production environments, Sponsors or customer always want to see production data in the test environments. For security reasons, you should not move data from production to non-production environments. I agree with this rule, but I understand how frustrating it can be for sponsors or customers to not see real data when they test the application too. At the end, they are the one who pays the bill and they deserve to see how application is going to look like with the real data. Imagine you are buying a car, you are going to spend good amount of money for it but car dealer tells you that you cannot test drive the car you want to buy because of policies.

   There are ways to move data from the production to non-production environments, you will spend good amount of time to manipulate the prod data and move it to the non-production area. This way works but it takes a lot of time of DBA or Developers. Also, SQL Server 2016 has Data Masking options which masks the data in real time too, but Dynamic Data-Masking has its own issues which I am not going to cover here.  I am sure there are some third-party products does that for you too and I am sure they are not free either.

    SQL Server Security team came with a great feature named Static Data Masking which handles the problem I just explained. Static Data Masking feature is available in SQL Server Management Studio 18 preview 5 and above. You need to have SQL Server 2012 or later version for Static Data Masking to work. You can reach to Static Data Masking by right clicking on the database you like to mask. Then select Tasks and click on Mask Database

   You should see the following window after that.

    You will see the available tables of your database in this window. Expand any table by clicking on the down arrow button. You have the following options to use for masking the data for any column.

   

SHUFFLE MASKING

     By selecting this option, SQL Server shuffles all the values of selected column to new rows. SQL Server does not generate new values, it just shuffles them. If you like, you can maintain the NULL values too. To do that, you can click on configure link and select the Maintain NULL positions checkbox.

GROUP SHUFFLE MASKING

     This works just like Shuffle Masking option. Only difference is you can group the columns and they will shuffle together. To group two columns, pick group shuffle for the columns you want to mask and give them the same group name by clicking on Configure link in each column.

NULL MASKING

     It replaces column's value with NULL. You want to be sure that column is a NULLABLE column to be able to use this option.

SINGLE VALUE

     SQL Server replaces the column's values with a value you define with this option. You can specify the value by clicking Configure button.

STRING COMPOSITE

     This option generates random strings for masking the column you select. Application lets you use RegEx to format the values. In the following example, I am using the SSN Regex to generate random SSN numbers.

    When you are done generating the masking rules, you can save the rules to use them later, so you don't need to generate the rules again later. To finish the process, you need to give the path and the name of the new database. Management Studio will use the rules to mask all data and generate a new backup database for you. When it's completed you can restore the database and use it for testing!

LIMITATIONS

    In this version, you cannot use static data masking with the followings:

• Temporal Tables
• Memory-Optimized Tables
• Computed Columns and Identity columns cannot be masked.
• Geometry and Geography data types cannot be masked.