2

We want to provide developers in our organization masked data from production to help troubleshoot production issues. What would be the best way to approach it?
I've read this article https://docs.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking?view=sql-server-ver15

But it seems to take a different approach from what I've envisioned. My idea was to have a separate database server that is replicated from the production database, and somehow mask the data during replication so that the real data will never reach the replicated database server. That way we don't have to have special security considerations about how to store and treat the replicated database server.

Is it a reasonable approach?

Improve this question
0
1

Is it a reasonable approach?

Yes. That's what the (removed) "Static Data Masking" component in SSMS did. A simple way to implement this is to copy data from production using an identity for which Dynamic Data Masking is enforced on the production database, using the Copy Database Wizard or a custom script.

Improve this answer
3
  • The problem is that when I try to create a publisher and subscriber, both the users that connect to the publisher and to the distributor must have a db_owner permission and data that is queried by a db_owner user is not masked.
    – areller
    Apr 3 '20 at 2:52
  • Yes. The built-in Transnational Replication won't work.
    – David Browne - Microsoft
    Apr 3 '20 at 3:16
  • So can I do something else? Can I do online replication with copying the database (maybe at a constant interval)?
    – areller
    Apr 3 '20 at 3:31
1

You will not be able to do this using built-in replication. There are no hooks in the replication chain that allow you to manipulate the data as it passes by.

What has worked for me in the past is a copy, mask, publish approach. Take a backup of the production DB and restore it as a new DB. Since this still contains sensitive data I would keep it in a production environment. Run your masking routines against this newly-restored DB then take a backup of this. Itis this second backup, containing masked data, which is restored to development. The cycle can be repeated as often as neede, though ask yourself how fresh do you really need dev data to be.

I looked at putting the masking in a continuously running ETL job, but the extra work wasn't worth it for our use case.

Improve this answer
0

Taking a backup copy, running custom scripts, then moving this backup to a lower environment is both fragile and can be very time consuming when working with a large database.

There are several 3rd party tools available that will mask (or even create synthetic versions of your data) inflight as it's transferred to a lower environment. You can hook it into a CI/CD pipeline (or just scheduled on a cron job). (full disclosure I work for a company that makes one of these tools, tonic.ai, so I am a bit biased)

Regarding refresh frequency, depending on how fast your development cycle is, having an up-to-date dev environment can be very important. Slow moving enterprises may get away with a monthly refresh cadence, but if you're pushing code weekly or daily, you should be updating your development environment to the latest production schema and refreshing data as often as possible.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.