locked
SQL Server Data Masking RRS feed

  • Question

  • Hello, we're looking into the need of SQL Server (preferably SQL 2014 but do also have SQL 2016 if needed/better) Data Masking and was wondering are there any tools or good places to start to facilitate implementing and getting more familiar with?

    Thanks in advance.

    Wednesday, April 15, 2020 8:23 PM

Answers

  • I realize now SQL 2014 does not have DDM.  Any other workarounds to do this if we still need to do within SQL 2014 (not SQL 2016) ?

    You would need to do it in the application layer.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, April 15, 2020 9:54 PM

All replies

  • What sort of masking? When people talk about data masking today, they talk about dynamic data masking and static data masking. Those are two very different things.

    SQL 2016 and later has Dynamic Data Masking as a bulit-in feature. It is an aide to the application, you can in the the database define rules for how data is to displayed for users who do not have permission to view unmasked data.

    Static Data Masking on the other hand is what you would use if you want to copy a production database to dev, but you don't want to expose sensitive data in dev. With static data masking, sensitive data is replaced with something else, and the original data cannot be retrieved again. (Thus don't do this on your production database!) Microsoft has nothing in this sphere, but Red Gate has their Data Masker. There is also a free tool available to dbatools.io.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, April 15, 2020 9:29 PM
  • I realize now SQL 2014 does not have DDM.  Any other workarounds to do this if we still need to do within SQL 2014 (not SQL 2016) ?
    Wednesday, April 15, 2020 9:29 PM
  • I guess it depends on what you mean by data masking.  You can start here: https://docs.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking?view=sql-server-2016

    Or - you can lookup Redgate's data masking tool.

    The first option masks the data on select...it does not change the data in the tables.  The second option allows you to change the data in the table.


    Jeff Williams

    Wednesday, April 15, 2020 9:36 PM
  • I realize now SQL 2014 does not have DDM.  Any other workarounds to do this if we still need to do within SQL 2014 (not SQL 2016) ?

    You would need to do it in the application layer.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, April 15, 2020 9:54 PM
  • Hi techresearch7777777,

    As Erland mention, dynamic data masking and static data masking  are usually used for Data masking on SQL server 2016 and later versions. Please check below links to get more detail information.
    Using Dynamic Data Masking in SQL Server 2016 to protect sensitive data
    SQL Server Static Data Masking Example

    Data masking on SQL server 2014, please check if below links could help you.
    Data Masking in SQL Server 2014
    An alternative to data masking

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, April 16, 2020 2:51 AM