Mask the data during restore

  • 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.

  • 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)

  • 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
  • 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]

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

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