Mask the data during restore

Question

Mask the data during restore

nuth

SSC-Addicted

Points: 473
More actions
June 10, 2015 at 5:37 pm

#301396

How can we mask the sensitive data when I am restoring the production backup to DEV database
for ex: we have SSN details in prod, how can mask the sensitive data when we restore prod from backup.

Viewing 4 posts - 1 through 3 (of 3 total)

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

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 1

snuthalapati (6/10/2015)
How can we mask the sensitive data when I am restoring the production backup to DEV database
for ex: we have SSN details in prod, how can mask the sensitive data when we restore prod from backup.

Fix it in prod. SSN's should not be in clear text to begin with.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Your sensitive data should be encrypted in Production anyway. When that's the case it's easy, after restoring to dev you can deny access to the keys to everyone (as long as the devs aren't sysadmin, which they shouldn't be), or if you want to ensure that even a sysadmin on dev can't access them, delete the encryption keys after the restore, though I wouldn't recommend that route.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

tswiat SSC Journeyman Points: 77 More actions · Answer 3

Snuthalapati, your question is vague. I note that others have responded as if you had asked 'how can we secure the data' which is not the same as masking. Eg. It may be that developers need access to unencrypted SSNs as part of their development and testing.

So, will dynamic data masking meet your needs? If so, are you able to install SQL Server 2016 Preview on your Dev system? (it offers dynamic data masking capabilities)

If your developers really need access to SSNs in the clear, then consider:

- Do you need valid SSNs? (i.e. are the digits validated against SSN rules?)

- Does your system in some way cross-reference these?

- Do these need to be unique?

- If the answer to such questions are 'no' then you could just as easily replace all SSNs with a fictitious SSN.

Otherwise, this can be a significant undertaking to do properly. One approach to consider: Generate a range of fictitious SSNs, map them to the original SSNs and then replace throughout your DB. You will need to do this in a secure staging area. There are many data masking tools available that can do this and some offer a freeware license, such as DataVeil[/url]. Disclosure: I work for DataVeil.

Terry Swiatkiwsky DataVeil Data Masking[/url]