Masking SQL Server data with specific value generator

Applies to

ApexSQL Mask


This article explains how to mask SQL Server data using a specific value generator.


ApexSQL Mask is a data classification and masking tool, that easily masks and replaces sensitive SQL Server data. For masking and replacing data, ApexSQL Mask uses two types of masks:

  • Substitution
  • Redaction

The Substitution type of mask uses generators to create data that will be used for masking and replacing sensitive data. Among these generators, there is a Specific value generator which usage will be explained in this article.

Mask SQL Server data

When connected to the selected database, this database masking tool will present the classified columns automatically in the main grid window, by default. These columns are with sensitive data in them and therefore automatically assigned masks to.

To easily distinguish classified columns from regular ones, classified columns have a red dot in front of their name:

The main window of ApexSQL Mask

Besides already classified sensitive columns, ApexSQL Mask can mask any other column from the selected database.

To mask a column from the selected database, click on the All columns button under the Home tab:

All columns in the Home tab

This action will list all columns in the grid panel. Tick the checkbox in the front of the name of the column to select columns for masking, and the red dot will appear.

For this article, the AddressLine2 column from the Person.Address table in the AdventureWorks2019 sample database will be used:

The All columns in the grid panel

To add already predefined mask, right-click on the selected column, and from the context menu choose the Add mask command. This will open a submenu where type and name of a mask can be chosen that will be applied:

When mask SQL server data, on specify column can be applied already created mask

Also, for the chosen columns, the newly created mask can be applied to.

To create a new mask, click on the Manage button under the Home tab:

The Manage button, by clicking on that button will be started mask SQL server data

The Manage mask window will be opened with all applied masks to the selected database:

All applied masks, which are using to mask SQL server data

To create the Substitution type of mask, tick the Substitution checkbox under the Type section, and then click on the New button:

The New button in the Manage masks window

The Create substitution mask window will appear, wherein the Level section, the Column is checked by default. In the Name text box, it is required to enter the name of the new mask, while the Description text box is optional and used to put a description of the new mask:

The Create substitution mask window

When the above is set, the next step is to choose the type of generator from the Type drop-down list.

For this article, a Specific value generator will be chosen:

The Type drop down list of generators

Specific value generator

When the Specific value generator is selected as an option to mask SQL Server data, two options will appear in the section below:

  • Null
  • Value

A Specific value generator section

If the Null option is selected, then only the nullable columns can be masked. All data from that nullable columns will be masked with the null value.

If the Value option is checked, the data from the selected columns will be replaced with the value that is typed in the Value text box. For this article, all data will be replaced with xxxxxxx value:

The Value text box

When all is set, click on the Apply button in the Create substitution mask window:

The Apply button in the Create substitution mask window

The Apply to columns window will be opened. From the Available grid, columns can be chosen that will be transferred to the Applied grid by using the right blue arrow in the middle.

In this case, the AddressLine2 column will be moved. By clicking on the OK button, the newly created mask will be applied to the selected column:

The Apply to columns window

The focus will go back to the Create substitution mask window, whereby clicking on the OK button will create a new mask.

That newly created mask will now be present in the Manage mask window among all other masks. To close the window, just click the OK button:

The newly created mask in the Manage masks window, that will be used to mask SQL server data

The main window of ApexSQL mask will be shown. By clicking on the column that is masked with the Special value generator, in the Preview dialog on the right side of the main window, the final result can be seen as shown below:

The preview window