MS SQL Anonymise

Question

Hi,

I have a below requirement

We are going to restore production database into UAT for testing few use cases. We should anonymise required data's (Ex: SSN, Credit Card etc) from all types of users ( including sysadmin, db owner )

No one should not able to get real records.

Also Application/UI layer not going to implement any changes ( Ex: To decrypt or unmask ) because of time line

I have tried below option but not able to achieve my use case

1) Dynamic Masking - Admin Can able to view

2) Always Encrypted - It encrypts records (not mask)

3) Static Mask - This override actual data and will miss real record

We have to anonymise the data from all users with out missing actual records please let me know the best option to achieve this

Regards,

Prabhu.R

Answer 1

The only way to not have ANYONE have access to that data, is to change the data in the database to invalid values.

Answer 2

This is by no means a trivial task. I know, because I implemented this for a system I had worked with for many years. I was surprised how many columns there were which had sensitive data.

You must apply some form of static data masking. That is, if the SSN is 123456789 in production, it must be something else, for instance 456789123 in UAT. If the value appears in many tables, you probably want the same replacing value all over the place. This also means that test cases need to use the new values.

You object "This override actual data and will miss real record" But that is exactly the point. If you don't want sensitive data in UAT, you must destroy the source data. You cannot both have the cake and eat it.

As for how to do it, there are some products on the market. Red Gate have their Data Masker which does come with a price tag. You can also go do dbatools.io. In their collection of PowerShell script, there is an anonymiser. As everything else in dbatools.io, it is free.