"Free testing tips and tools"
    Best Freeware Download  Gear Download

      Windows 7 Downloads
Download MSSQL.DataMask
Author: WinTestGear
Version Number: - Release Notes - Wish List
Revision Date: April 28, 2010 
Byte Size: 2,431KB  (251KB zipped)
License: Freeware, Warranty Disclaimer
Cost: Free
Requirements: .NET Framework 3.5 installed; Microsoft SQL Server 2005 or higher
Installation: No installation; simply copy & paste the single EXE file where you need it.
Screen Shots: Main Form, Select Mask Dialog, Generate Script, Open Connection Dialog,
   Execute Masks Status Dialog, Never Run Against Production Warning, Freeware License
   Help About Dialog

Description: Do you need rich test data to develop, test, or outsource your project?  If so, you've probably
  tried either generating from scratch, or cloning production data.  Auto-generating the test data is
  difficult and error prone for all but the simplest databases due to complex variances, frequencies, and data
  interdependencies. Business and legal obligations such as HIPAA require that production data clones
  be thoroughly sanitized (masked) of personal indentifiable information (PII) and/or protected health
  information (PHI).  Data masking is an effective test data generation approach.
  MSSQL.DataMask is a free, simple tool that quickly sanitizes a clone of your production database into
  a safe, secure test database.  Once built, the process is easily repeatable to refresh your test data from
  production. You can either load and re-run a set of data masks from the application, or generate a fully doc-
  umented tSQL script to modify, run, or schedule as your needs dictate. 

Why Mask Your Test Data?
    Data Masking Beats Auto-Generating: According to Forrester Research Analyst Noel Yuhanna,
     "90 percent of organizations prefer to mask data...[because creating test data]...is more complex and
     doesn't always represent actual business scenarios."
    But My Test Data is Safe Because Only Internally Accessed: According to the Ponemon Institute,
     "88% of all data breaches come from within;" thus, protecting your dev and test data is critically

Competitors: If MSSQL.DataMask does not fulfill your needs, then consider trying one of these more
  sophisticated products offered by other vendors:
  1. The Data Masker
  2. JumbleDB
  3. DataMasking
  4. Grid Tools
  5. Dynamic Data Masking / ActiveBase Security
  6. Dataguise dgMasker
  7. CalSQL (tSQL script)
  8. Hexaware Akiva
  9. IBM Magen

Notes / Articles:
    Good reminder on the risks of anonymizing data (be thorough when scrubbing)
    Good overview article on data sanitization reasons and techniques

Keywords: Personal Identifiable Information, Protected Health Information, Scrub Data, Substitute Data,
  Transform Data, Data Masking, Data Sanitization, Data Obfuscation, Data Security, Data Cleansing,
  Data Hiding, Hide Data, Disguise Data, Sanitize Data, Data Privacy, Health Insurance Portability and
  Accountability Act (HIPAA)

Available Mask Types:

MSSQL.DataMask offers the sanitization techniques (mask types) listed below. 

Click the blue mask name link to view a sample of the auto-generated SQL script in a new window.

    Scrub: Overwrite all rows of a column with the "same value"
       Scrub.Null: Overwrite with null
       Scrub.EmptyString: Overwrite with empty string ("")
       Scrub.FixedString: Overwrite with a text you enter
       Scrub.Zero: Overwrite with zero (0)
       Scrub.FixedNumber: Overwrite with a number you enter
       Scrub.FixedDate: Overwrite with a date/time you enter

    Substitute: Overwrite all rows of a column with a "unique value"
       Substitute.LoremGibberish: Overwrite with a random lorem ipsum gibberish text (retains original size)
       Substitute.GUID: Overwrite with a random GUID (16-character text only)
       Substitute.RandomNumber: Overwrite with a random number between min/max points you specify
       Substitute.RowNumber: Overwrite with a row number (and text you specify if n/varchar)
       Substitute.FromList: Overwrite with a random selection from value list you specify

    Transform: Overwrite all rows of a column with an "obsfucated original value"
       Transform.Scramble: Overwrite with scrambled original value (encrypt, then truncate at initial length)
       Transform.Hash: Overwrite with hashed original value (MD5, always 16-bytes)
       Transform.Prefix/Suffix: Prefix and suffix original value (with text you specify, can leave blank)
       Transform.Find/Replace: Find & Replace text within original value (you enter find/replace text)
       Transform.AsteriskMask: Overwrite first (n) characters with asterisks (like credit card statements)
       Transform.ShuffleRecords: Randomly shuffle all rows within column (my favorite for most test data)
       Transform.NumericVariance: Randomly de/increment original numeric value by percent you select
       Transform.DateVariance: Randomly de/increment original date value by number days you select
       Transform.DateTimeVariance: Randomly de/increment original date & time values by your selections
       Transform.Truncate: Cutoff original value at character length you specify

Screenshot #1 - Main Form:

Using MSSQL.Datamask is simple.  Run the executable.  Click connect to open a database.  Select a table in the tree control on the left.  Click the column mask type drop down to open a mask selection dialog (shown below).  As you assign data mask rules to table columns, they become bold in the grid.

Screenshot #2 - Select Mask Type Dialog:

There are currently 21 types of data mask that can be applied to columns.  The list of mask types is filtered based on the column's data type, length, whether or not it is nullable, whether or not it has constraints, etc.  As you select different maskt types in the drop down list, the dialog re-paints itself with detailed instructions on using the mask type and reconfigures the parameter text boxes / drop down boxes. 

In the sample screenshot below, the Substitute value randomly from a list of possible values mask is depicted.  Note that you can simply paste in a long list of names, addresses, zipcodes, phone numbers or whatever else you need (CRLF delimited).  MSSQL.DataMask will then randomly substitute from this list to replace the actual values in your test database (cloned from production).  In the example below, the [LastName] field is going to be randomly overwritten with values I keyed into the text box (John, Jack, Jim, etc.).

Screenshot #3 - Generate Scripts:

MSSQL.Datamask automatically generates thoroughly commented tSQL for you if you'd rather not run your data masks from the tool's interface.  Reasons you might choose to script include automating in a SQL Agent job, modifying the script yourself (to bypass limits MSSQL.DataMask enforces such as no masks on primary keys when you know it will be safe, or re-wiring to a large lookup table to bypass the 8,000 character limit on the "Substitute from List" mask, etc.).

Screenshot #4 - Execute Masks Status Dialog:

A screenshot of the status dialog box results after executing a set of masks against a test database.

eXTReMe Tracker