Applies to
Summary
This article will explain how to mask SQL Server data with dynamic data masking.
Description
ApexSQL Mask is a data classification and masking tool. This database masking tool offers two solutions in data masking:
- Static data masking – where the sensitive data will be replaced entirely with masked data
- Dynamic data masking – where the sensitive data will be hidden from non-privileged users
Dynamic data masking will mask SQL Server data by hiding sensitive data, without changing that data in the production database. Dynamic data masking allows the privileged users to see the original sensitive data, but that sensitive data will be denied to the non-privileged users. This technology allows organizations to analyze, test, and protect a database as well as compliance with different requirements. More about this feature will be explained in the article below.
Establishing database connection
When ApexSQL Mask application is started, the Connect to SQL Server window will appear, in which the SQL Server Instance, type of Authentication and target Database needs to be set. When all is set up, click on the Connect button to establish the conniction with the selected database:
The focus will go to the main window of the ApexSQL Mask. On the left side of the main window in the grid panel all classified columns with automatically added masks will be presented, while on the left side the Preview window will appear, which presents how the original and masked sensitive data will look like:
Alongside automatically adding a built-in mask, ApexSQL Mask can also create new masks that can be used to mask SQL Server data. To start the process of creating a new mask in the Home tab click on the Manage button:
The Manage masks window will be opened, by default the Replace original data tab will be presented with all automatically created masks:
To start the process of creating dynamic data mask click on the Hide original data tab, where along with standard buttons, another, the User permissions button, will be present.
Click on the User permissions button to open the User permission dialog which lists all security principals associated with the connected database. In the dialog tick the checkbox next to the SQL database users that will have permission to see original data after the masking process is finished. When the user is selected click on the OK button:
The next step is to click on the New button in the Hide original data tab:
The Create new mask window will be opened. The name of the mask is required to put in the Name text box while the description of the mask is optional to put in the Description text box:
What is left there is to choose the type of data mask and apply it to desired columns.
Types of dynamic data masks
The next step in defining the custom mask is to choose the type of dynamic data mask. By clicking the Type drop-down list the following types of dynamic data mask will appear:
- Default
- Random numeric
- Custom text
The Default type
The default dynamic data masking type is full-field data masking using mask values predefined by the SQL Server itself. Predefined masking values are different and depending on the data types. Based on the column data type, the default dynamic data masking values are:
- For the string data types (char, nchar, varchar, nvarchar, text, ntext), the default type will use “XXXX” (or fewer “X”s if the size of the field is less than 4 characters), to mask SQL Server data
- For the numeric data types (bigint, bit, decimal, int, money, numeric, smallint, smallmoney, tinyint, float, real), the default type will use the zero value “0” for data mask:
- For the date and time data types (date, datetime2, datetime, datetimeoffset, smalldatetime, time), the default type will use “01.01.1900 00:00:00.0000000” value to mask data:
The Random numeric type
This dynamic data masking function is used on any numeric data type, and will mask SQL Server data with random value within a specified range. When the Random numeric type is selected, below the Type drop-down list the following fields will appear:
- Start – combo box where will be set the minimal numeric value
- End – combo box where will be set the maximal numeric value
The range of masking values for the random numeric function will be set in the Start and the End combo boxes. For example, if in the Start combo box the0 value is set and in the End combo box the 9 value sets up, the masking values will be in the range from zero to nine:
The Custom text type
The Custom text is the dynamic data masking function that exposes the first and last letters and adds a custom padding string in the middle. When the Custom text is chosen below the Type drop-down list the section will appear that contains next options:
- Prefix – the number of characters at the begining that will not be masked
- Suffix – the number of characters at the end that will not be masked
- Padding – masking value that will replace characters between prefix and suffix
For example, if in Prefix and Suffix combo box set up the value 2, and in the Padding text box type the masking value, in this case, aaaa, the Preview window will present the masking results illustrated in the picture below:
Email type
Email is the dynamic data masking function that exposes the first letter of an email address and adds the constant suffix “com”, in a form of an email address aXXX@XXXX.com:
Applying dynamic data mask to the selected column
When the data masking type is set, the final step is to apply the newly created mask to the desired columns. By clicking on the Apply button in the Create new mask window the Apply to columns window will open. In this window from the Available grid click on the right blue arrow to transfer the selected column to the Applied grid. Click on the OK button to confirm the settings:
The Create new mask window will appear again, click on the OK button to close it and the view will switch back to the Manage masks window where the newly created mask will be shown in the Hide original tab. Click on the OK button will close the Manage masks window:
The main window of this database masking tool will be shown, wherein the Preview window the results of dynamic data masking can be seen. In the picture example below, the Default type of dynamic data masking wwas used:
Using the dynamic method to mask SQL Server data will preserve original data and thus sometimes eliminate the need to for cumbersome database replication for provisioning purposes.
