Masking Data

  • crcoracle

    Right there with Babe

    Points: 718

    Hi,

    We have a production datbase with sensitive data, andwe are looking for a procedure to create new environments (test, dev) with masked data. This is my first time masking data in SQL Server and I don't mind if we have to pay for a third party

    Question:  Is it a good option to choose the 'Static Data Masking' or it is much better to use third party apps like "Red Gate DataMasker"

    Regards

  • Grant Fritchey

    SSC Guru

    Points: 395316

    So, I work for Redgate. Bias may play a factor here.

    Static data masking may be OK in production environments where you have a degree of control. However, it won't work well, at all, in non-production environments where you have less, or no, control of the system. If someone is SA, they can bypass static data masking.

    Most people don't use our tool (darn it). Instead, they have custom built scripts that clean the data and/or create data. This is a preferred approach. Permanently changing the data is far safer for non-production environments.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • crcoracle

    Right there with Babe

    Points: 718

    May Thanks Grant !!

    But I've just read that 'Static Data Masking' is not going to be supported/developed anyway, because does not meet customer’s expectations.

    Therefore, our possibilites are reduced to 2:

    • Custom built scripts
    • Third Partys.

    If someone has experience with third parties, I'll be glad to hear them.

    Regards

  • Grant Fritchey

    SSC Guru

    Points: 395316

    My vote is for Redgate Data Masker. It's an amazing tool. Also, great integration with SQL Clone. Together, they make a provisioning tool for non-production environments.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • nova

    Newbie

    Points: 5

    Have you looked at Dynamic Data Masking in SQL Server?

    https://docs.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking?view=sql-server-2016

    Not sure what you mean by "custom built scripts". You don't need to write any extra scripts to use data masking.

  • Grant Fritchey

    SSC Guru

    Points: 395316

    nova wrote:

    Have you looked at Dynamic Data Masking in SQL Server? https://docs.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking?view=sql-server-2016 Not sure what you mean by "custom built scripts". You don't need to write any extra scripts to use data masking.

    As soon as you put a database on to a developers laptop who has 'sa' privileges (as they should), all the data "masked" will be exposed. This is only useful in a production environment.

    Custom scripts are used to mask data. It's very common. Update the data prior to releasing it to non-production environments.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • BrownCoat42

    SSC Journeyman

    Points: 85

    This is a problem I am going to have to deal with soon. In most of our dev data, it doesn't need to be realistic data and our options are a bit easier.

    The tricky part for me will be the data that needs to be realistic for testing performance. I think it wouldn't be that hard to come up with rules to consistently randomize data so that original data patterns survive, but doing that so that there is not enough of the original data to reconstruct I think will be difficult.

     

  • Grant Fritchey

    SSC Guru

    Points: 395316

    BrownCoat42 wrote:

    This is a problem I am going to have to deal with soon. In most of our dev data, it doesn't need to be realistic data and our options are a bit easier. The tricky part for me will be the data that needs to be realistic for testing performance. I think it wouldn't be that hard to come up with rules to consistently randomize data so that original data patterns survive, but doing that so that there is not enough of the original data to reconstruct I think will be difficult.  

    Yeah. It's not easy even with a tool like SQL Data Masker. Here's an article I wrote about trying to get realistic distribution of credit card values.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply