DataMasker 3.0.2

DataMasker

A free data masking/anonymizer library for Sql Server written in .NET

If you've ever needed to pull down databases from a live environment to stage or even dev you'll need to think about masking any personal information. There are options out there paid and free, however the free ones I've found do not provide genuine data and the paid options are too pricey when it's only a few tables.

Data generation is provided by https://github.com/bchavez/Bogus

Bogus

Hello. I'm your host Brian Chavez (twitter). Bogus is a simple and sane fake data generator for .NET languages like C#, F# and VB.NET. Bogus is fundamentally a C# port of faker.js and inspired by FluentValidation's syntax sugar.

Bogus will help you load databases, UI and apps with fake data for your testing needs. If you like Bogus star ⭐️ the repository and show your friends! 😄

Upgrading to 3.0

Version 3.0 is a breaking release. Before upgrading, note the following:

• .NET 10 is required. The library and the CLI now target net10.0. .NET 8 is no longer supported.
• Microsoft.Data.SqlClient replaces System.Data.SqlClient. If you construct a SqlDataProvider yourself, pass a Microsoft.Data.SqlClient.SqlConnection. Microsoft.Data.SqlClient encrypts connections by default (Encrypt=True), so a server without a trusted certificate will now fail to connect unless your connection string includes TrustServerCertificate=True or Encrypt=False. The old client did not enforce this.
• Releases are automatic. Every push to master publishes a new 3.0.x version to NuGet and GitHub Releases.
• IDataSource and IDataMasker have grown. IDataSource gains GetDistinctValues(tableConfig, columnName) and IDataMasker gains SeedUniqueValues(...). If you implement either interface yourself you will need to add these. If you use the built-in DataMasker directly, call dataMasker.SeedUniqueValues(dataSource, tableConfig) before masking a table so that unique columns never collide with values already in the table.
• Rows are read in pages and updated in bulk. SqlDataSource.GetData streams the table in primary key order, one batch at a time, instead of loading it all into memory. Updates against SQL Server are bulk copied into a temp table and applied with a single UPDATE ... JOIN per batch. The primary key must be a single sortable column (it already had to be for updates).
• updateBatchSize defaults to 1000 instead of the whole table, so a large table is no longer one enormous transaction. A new commandTimeout setting (seconds, default 600, 0 for no limit) applies to every query, including sqlStatements.
• The CLI returns a non-zero exit code on failure (1 when masking fails, 2 for bad arguments) and always writes the error to stderr, even with --no-output.

Nuget

DataMasker is available as a library over on nuget.org should you need to customize any part of the process. Alternatively just ask for a new feature and I'll see what I can do.

install-package DataMasker

Configuration

All configuration is done via a .json file, of which an example can be seen below.

Example Config

{
  "dataSource": {
    "type": "SqlServer",
    "config": {
       "name": "databasename",
       "userName": "databaseUserName",
       "password": "databasePassword",
       "server": "databaseServer"
    }   
  },  
  "tables": [    
    {
      "name": "Users",
      "schema": "dbo",
      "primaryKeyColumn": "UserId",
      "columns": [
        {
          "name": "FirstName",
          "type": "FirstName",
          "useGenderColumn": "Gender"
        },
        {
          "name": "LastName",
          "type": "LastName",
          "retainEmptyStringValues": true,
          "useGlobalValueMappings": true,
          "valueMappings": {
            "oldValue": "Jones",
            "newValue": "Smith"
          }
        },
        {
          "name": "Password",
          "type": "None",
          "retainNullValues": false,
          "useValue": "PasswordForDevEnvironment"
        },
        {
          "name": "DOB",
          "type": "DateOfBirth",
          "min" :"1901-01-01",
          "max": "2000-0101"
        },
        {
          "name": "EmailAddress",
          "type": "Bogus",
          "unique": true,
          "stringFormatPattern": "{{internet.email}}"
        },
        {
          "name": "Gender",
          "ignore": true,
          "type": "None"
        },
        {
          "name": "Address",
          "type": "Bogus",
          "retainNullValues": false,
          "stringFormatPattern": "{{address.fullAddress}}"
        },
        {
          "name": "ContactNumber",
          "type": "PhoneNumber",
          "retainNullValues": false,
          "stringFormatPattern": "+447#########"
        },
        {
          "name": "CompanyName",
          "type": "Sql",
          "sqlValue": {
            "query":"SELECT Name FROM SampleCompanyNames WHERE UserId = @UserId",
            "valueHandling": "Null"
          }
        }
      ]
    }
  ]
}

Table configuration is supplied by either the "tables" property or "tablesConfigPath" property.

If you want to keep your table configuration external to the main .json file this can be done by supplying "tablesConfigPath" with a file path. An example of this can be found in the example-configs directory in the example project.

External table config json

{
    "dataSource": {
    "type": "SqlServer",
    "config": {
       "name": "databasename",
       "userName": "databaseUserName",
       "password": "databasePassword",
       "server": "databaseServer"
    }   
  }, 
  "tablesConfigPath": "example-configs\\config-example2-table-config.json",
}

or embedded

{
    "dataSource": {
    "type": "SqlServer",
    "config": {
       "name": "databasename",
       "userName": "databaseUserName",
       "password": "databasePassword",
       "server": "databaseServer"
    }   
  }, 
  "tables": [... insert config here ...],
}

If both tables and tablesConfigPath is supplied then tablesConfigPath wins.

Column Configuration

Sql Value Configuration

When using data type Sql this allows you to get values from other tables within the same database. The configuration object is made up of the following properties

Data types

None

To use None you must specify either valueMappings, useValue, or useList, no data will be generated for this type. If you specify only valueMappings and the target value is not found, an error will be thrown.

{
  "name": "Title",
  "type": "None",
  "valueMappings": {
    "Mr": "Master"
  },
  "ignore":"true/false",
  "useValue": "Miss",
  "retainNullValues": "true/false",
  "retainEmptyStringValues": "true/false",
  "useLocalValueMappings": "true/false",
  "useGlobalValueMappings": "true/false"
}

useList

Randomly selects a value from a predefined list for each row. Works with any column type and supports integers, strings, or any JSON-compatible value.

{
  "name": "ProductTypeId",
  "type": "None",
  "useList": [10, 20, 56, 80],
  "retainNullValues": false
}

With strings:

{
  "name": "Status",
  "type": "None",
  "useList": ["Active", "Inactive", "Pending"],
  "retainNullValues": false
}

Bogus

Bogus is a type that when specified requires the stringFormatPattern option, which is passed directly to the Bogus API, see here for available options

{
  "name": "PhoneNumber",
  "type": "Bogus",
  "valueMappings": {    
    "+555-555-555": "+444-555-555-55"
  },
  "ignore":"true/false",
  "useValue": "+50559-5-5-555",
  "retainNullValues": "true/false",
  "retainEmptyStringValues": "true/false",
  "useLocalValueMappings": "true/false",
  "useGlobalValueMappings": "true/false",
  "stringFormatPattern": "{{phonenumbers.phonenumber}}"
}

FirstName

{
  "name": "FirstName",
  "type": "FirstName",
  "valueMappings": {    
    "James": "Bob"
  },
  "ignore":"true/false",
  "useValue": "Steve",
  "retainNullValues": "true/false",
  "retainEmptyStringValues": "true/false",
  "useLocalValueMappings": "true/false",
  "useGlobalValueMappings": "true/false",
  "useGenderColumn": "Gender"
}

LastName

{
  "name": "Surname",
  "type": "LastName",
  "valueMappings": {    
    "Smith": "Jojnes"
  },
  "ignore":"true/false",
  "useValue": "Timms",
  "retainNullValues": "true/false",
  "retainEmptyStringValues": "true/false",
  "useLocalValueMappings": "true/false",
  "useGlobalValueMappings": "true/false"
}

DateOfBirth

• min will default to 1901-01-01
• max will default to current date
• date format is fullyear-month-day

{
  "name": "DOB",
  "type": "DateOfBirth",
  "valueMappings": {    
    "1990-01-02": "1990-02-02"
  },
  "ignore":"true/false",
  "useValue": "1940-02-02",
  "retainNullValues": "true/false",
  "retainEmptyStringValues": "true/false",
  "useLocalValueMappings": "true/false",
  "useGlobalValueMappings": "true/false",
  "min": "1901-12-25",
  "max": "2000-11-20"
}

Rant

• max will default to 25

{
  "name": "Comments",
  "type": "Rant",
  "valueMappings": {    
    "A comment": "Becomes this"
  },
  "ignore":"true/false",
  "useValue": "A really important comment",
  "retainNullValues": "true/false",
  "retainEmptyStringValues": "true/false",
  "useLocalValueMappings": "true/false",
  "useGlobalValueMappings": "true/false",
  "max": 15
}

Lorem

• min will default to 5
• max will default to 30

{
  "name": "Comments",
  "type": "Lorem",
  "valueMappings": {    
    "A comment": "Becomes this"
  },
  "ignore":"true/false",
  "useValue": "A really important comment",
  "retainNullValues": "true/false",
  "retainEmptyStringValues": "true/false",
  "useLocalValueMappings": "true/false",
  "useGlobalValueMappings": "true/false",
  "min": 5,
  "max": 15
}

StringFormat

Check out the Bogus API for supported values

{
  "name": "Comments",
  "type": "StringFormat",
  "valueMappings": {    
    "A comment": "Becomes this"
  },
  "ignore":"true/false",
  "useValue": "A really important comment",
  "retainNullValues": "true/false",
  "retainEmptyStringValues": "true/false",
  "useLocalValueMappings": "true/false",
  "useGlobalValueMappings": "true/false",
  "stringFormatPattern": "#####****?????"
}

FullAddress

{
  "name": "Address",
  "type": "FullAddress",
  "valueMappings": {    
    "55 Long Name Street, Long Name Village, Long Name Town...": "Becomes this"
  },
  "ignore":"true/false",
  "useValue": "55 Long Name Street, Long Name Village, Long Name Town...",
  "retainNullValues": "true/false",
  "retainEmptyStringValues": "true/false",
  "useLocalValueMappings": "true/false",
  "useGlobalValueMappings": "true/false",
}

PhoneNumber

{
  "name": "PhoneNumber",
  "type": "PhoneNumber",
  "valueMappings": {    
    "+555-555-555": "+444-555-555-55"
  },
  "ignore":"true/false",
  "useValue": "+50559-5-5-555",
  "retainNullValues": "true/false",
  "retainEmptyStringValues": "true/false",
  "useLocalValueMappings": "true/false",
  "useGlobalValueMappings": "true/false",
  "stringFormatPattern": "+1 ########-#-###-#"
}

Sql

The current row is passed in as parameters and can be accessed using @ColumnName

{
  "name":"CompanyName",
  "type": "Sql",
  "sqlValue": {
    "query":"SELECT Name FROM SampleCompanyNames WHERE UserId = @UserId",
    "valueHandling": "Null/KeepValue"
  }
}

name & type are required everything else is optional unless specified

Most data can be generated perfectly fine just by using the Bogus or StringFormat data types.

Min & max

Only some data types currently use the min/max properties on the column configurations.

• Lorem, Rant & DOB

Example Usage

//load our configuration
Config config = Config.Load($"example-configs\\config-example1.json")
// you also can pass the JSON content directly:
// Config config = Config.LoadFromString(....);

//create a data masker
IDataMasker dataMasker = new DataMasker(new DataGenerator(config.DataGeneration));

//grab our dataSource from the config, note: you could just ignore the config.DataSource.Type
//and initialize your own instance
IDataSource dataSource = DataSourceProvider.Provide(config.DataSource.Type, config.DataSource);

//Enumerable all our tables and begin masking the data
foreach (TableConfig tableConfig in config.Tables)
{
    //load the data, this needs optimizing for large tables
    IEnumerable<IDictionary<string, object>> rows = dataSource.GetData(tableConfig);
    foreach (IDictionary<string, object> row in rows)
    {
        //mask each row
        dataMasker.Mask(row, tableConfig);

        //update per row, or see below,
        //dataSource.UpdateRow(row, tableConfig);
    }

    //update all rows, in batches of 100
    dataSource.UpdateRows(rows, tableConfig, 100);
}

All of the objects/datatypes from Bogus are supported, you can use the type "Bogus" in combination with "stringFormatPattern" to acheive any valueMappings

{
  "name": "Address",
  "type": "Bogus",
  "stringFormatPattern": "{{address.fullAddress}}"
}

You can combine multiple objects to generate complex data

{
  "name": "Name",
  "type": "Bogus",
  "stringFormatPattern": "{{name.prefix}} {{name.firstName}} {{name.lastName}}"
}

Data Sources

There are only two DataSources available at the moment

• InMemoryFake - is there only for the examples
• SqlServer - can pull and push data to SQL Server

There is some additional configuration required when using SqlServer, on the dataSource object a dynamic config property is available, you'll need to supply the name, server, userName & password for the connection or a connection string.

N.B. if the "connectionString" value is set name, server, userName & password will be ignored

{
  "dataSource":{
    "config":{
      "name": "xxx",
      "server": "xxx",
      "userName": "xxx",
      "password": "xxx",
      "connectionString": "Data Source=(localdb)\\mssqllocaldb;Initial Catalog=Clients;Integrated Security=SSPI;"
    }
  }
}

The dataSource object also accepts the following optional settings:

{
  "dataSource":{
    "type": "SqlServer",
    "dryRun": false,
    "updateBatchSize": 5000,
    "commandTimeout": 1800,
    "config":{ "connectionString": "..." }
  }
}

SqlServer Data Source

Rows are read in pages ordered by the primary key, so memory use is bounded by updateBatchSize rather than the size of the table. Each batch is bulk copied into a temp table and applied with one UPDATE ... JOIN, which is far quicker than a statement per row on large tables.

Dry run is supported. A transaction is created, the batch is applied and then the transaction is rolled back.

Gender

To ensure the new data is more accurate you may want to take gender into consideration when generating certain data types such as names. This can be achieved with a small amount of additional configuration. If no gender is specified then non gender specific names are generated.

You must define the gender column and then tell your target column to use this when generating data.

Here we are saying, use the column "Gender" when generating data for "FirstName". We then include the "Gender" column but tell it to be ignored by the IDataMasker, it is purley there as a dependency to "FirstName".

"columns": [
  {
    "name": "FirstName",
    "type": "FirstName",
    "useGenderColumn": "Gender"
  },
  {
    "name": "Gender",
    "ignore": true,
    "type": "None"
  },  
]

Locale

By default the locale is "en", the locale.

The locale is used by Bogus to generate data, the locale can be changedby setting a property on the dataGeneration object

{
  "dataGeneration": {
      "locale": "en"
    }  
}

Check out the Bogus page for a list of supported locales

DataMasker.Runner (CLI)

The latest version can be found at https://github.com/Steveiwonder/DataMasker/releases

The CLI is published as a self-contained Windows x64 build, so no separate .NET install is needed to run it.

This is a CLI interface for the data masking tool. You might want to use this as part of your continuous integration if you backup/restore your live environments back to stage/dev after a release

The exit code is 0 on success, 1 if masking fails and 2 if the arguments could not be parsed. Errors are written to stderr even when --no-output is set, so a CI pipeline can rely on the exit code.

The options are as follows

-c, --config-file Required. the json configuration to be

-d, --dry-run (Default: false) if set, performs a dry run (changes are rolled back), only supported by some data sources

-l, --locale set the locale

-u, --update-batchsize batch size to use when upating records

--print-options (Default: false) prints the arguments passed into this tool in a json format with executing anything else

--no-output (Default: false) if set, no output to the console will be written

--help Display this help screen.

--version Display version information.