Mask SQL Server data with substitution type on the row level

Applies to

ApexSQL Mask


This article explains how to mask SQL server data with the substitution type of mask on the row level.


ApexSQL Mask is a tool that masks sensitive SQL Server data. This database masking tool offers two types of rules for mask SQL Server data:

  • Substitution, where is used a certain number of generators to change entirely sensitive data with the masked one
  • Redaction, where are used values or part of values for masking sensitive data

The Row level of masking allows users to mask a particular row or selection of rows in the targeted column, further in the article will be explained this option.

By starting the application, the Connect to SQL Server window will appear. In the Connection to SQL Server window the SQL Server, type of Authentication, and a Database is needed to be set. When all is set, click on the Connect button:

The Connect to SQL Server window

The main window of ApexSQL Mask will appear, where the automatically classified columns will be presented by default in the grid panel on the left side of the primary window:

The grid panel with classified sensitive columns

Besides automatically classified columns, the ApexSQL Mask provides the possibility to mask SQL Server data from the whole selected database. To choose any column from the selected database, in the Home tab, click on the All columns button:

All columns button in the Home tab

For this article the LastName column from the Person. Person table will be chosen. By checking the LastName column, the red dot will appear in the front the name of the column, and on that selected column can be applied already created or newly created mask:

all columns from the database in the panel grid

When the column is selected, to create a new mask in the Home tab, click on the Manage button:

The Manage button which used for starting process of creating new mask that will be later used for mask SQL Server data

The Manage masks window will open. By default, the Substitution mask is selected. Click on the New button to start the creation of a new substitution mask:

The Manage mask window

The Create substitution mask window will be shown. By default, the Column radio button is checked.

Mask SQL Server data in the Row-level

If there is need to mask special row or to exclude some rows from masks, those can be done by checking the Row radio button in the Level section:

Selected Row level to mask SQL server data

The Change target column dialog will appear, with all classified columns. Choose the columns from the Available grid, in this case, the LastName column and click on the OK button:

Change target column dialog

It will back to the Create substitution window, where for the Row level masking the following should be set:

  • Name – in this text box, enter a name for a new substitution mask (e.g. Row mask)

The Name of the Substitution mask

  • Description – this text box is optional, and it will contain the description of the newly created mask
  • Source column – the drop-down list that includes the targeted column, also and all columns from the same table. The value of the specific row(s) from the source column will be used to find the related row(s) in the targeted column, which data will be masked. For this article, the value in the row of the BusinesEntityID column, that is specified as a source column, will be used to find the related row (s) in the targeted column, the LastName column
  • Preview grid – shows the row’s data from the selected source column

  • Find values – shows the pattern that will be created with Wildcard editor. That pattern will be used for finding the specific row or rows in the source column. The Wildcard editor dialog will be opened by click on the ellipse button next to the Combo box:

    Ellipse button in Find values section

    • Wildcard editor – dialog in which can be added pattern for searching row in the source column. By using specific characters that can be found in the Syntax drop-down list, will be made a pattern for searching. A list of characters represents any single character syntax with seven number pattern [7] will be used for searching a row in this article. Click OK:

      Wildcard editor dialog

  • Exclude – by selecting this option the matching values will be excluded from substitution
  • Include nulls – when this option is checked null values will be replaced with a substitution
  • Type – the type of generators that will be used for substitution:
    • Predefined
    • Use original
    • Random
    • Specific value
    • Regular expression
    • SQL script
    • External source

Type of Generators that will be used to mask SQL Server data

For this article will be used the Specific value generator. The Value option will be checked and that value will be REPLACE:

The Specific value section

When all options above are set, click the OK button:

The Create substitution mask window for row level

The Manage masks window with the newly created mask will appear. Click the OK button to close the Manage mask window :

Newly created substitution mask in the Manage masks window that will be used for mask SQL server data

In the grid panel will be presented classified columns along with the LastName column in the Person. Person table. When clicking on the LastName column, in the Preview window the seventh row will be masked with the REPLACE value while other values will not be masked.

The Preview window with original and mask SQL Server data