Applies to
Summary
This article explains how to create a single script per table to mask SQL Server data.
Description
This database masking tool provides an opportunity to mask all sensitive data from one database. Those sensitive data can be replaced with automatically generated data by using the static type masking or can be hidden from non -privileged users by using the dynamic type masking without changing the original data from the database.
When columns with sensitive data are selected and have chosen the corresponding type of mask for masking or hiding that data, ApexSQL Mask offers next three processes for masking that sensitive data:
- Update database – the sensitive data in the chosen database will be masked by directly updating directly database
- Create a single script – a masking script will be created and can be used later to mask the sensitive data in the selected database
- Create a script per table – the mask script will be created for each table in the selected database
In case of creating a single script, if the database is large, the following issues can arise:
- The newly created script will take up a lot of space. Which is not suitable for sharing between teams
- System.Out.Of.Memory error may occur
The option to create a single script per table will help to overcome the swept issues.
In the article the procedure to create a masking script for every chosen table to mask SQL Server data will be explained.
Establishing database connection
On the application start the Connect to SQL Server dialog will be open. The SQL Server instance, the type of the Authentication, and the desired Database should be chosen in this dialog. If all is set, click on the Connect button:
Choosing appropriate method to mask SQL Server data
The view will go to the main window of ApexSQL Mask, which shows automatically detected sensitive columns with their automatically assigned masks in the main grid:
Beside automatically assigned masks, ApexSQL Mask offers the possibility of creating new masks for masking and hiding sensitive data. The process of creating a new mask will be started with a click on the Manage button in the Home tab:
The Manage masks window will appear, offering the ability to create:
- Static data masks
- Dynamic data masks
Static data masks
Static data masks can be created, edited, and deleted in the Replace original data tab. From the Replace original data tab can be chosen two types of static data masks:
- Substitution – this type of masks uses specified generators to create data that will be used to replace sensitive data in tables. Detailed description about this type of masking can be found in the following article: How to mask SQL Server data with substitution mask
- Redaction – this type of mask is using custom values to replace sensitive data in selected columns, and more about this type of static mask can be found in the How to mask sensitive SQL Server data with redaction mask
Dynamic data masks
To create, edit, and delete a dynamic data mask, click on the Hide original data tab. The settings in this tab allow users to create a dynamic type of data mask. By creating this mask type, sensitive data will be hidden from the non-privileged users, without changing that data in the database. More about dynamic data masking feature can be found in the Mask SQL Server data using dynamic data masking article:
Creating mask script per each table
When masks are created and applied to the selected columns, the next step is to run the mask process. Click on the Process button on the Home tab, will start the masking process:
The Process mask window will appear, whereby click on the drop-down list can be chosen the type of process. For purposes of this article will be chosen the Create script per table process:
This action will create a separate script to mask SQL Server data for every table that contains the sensitive data that needs to be masked. With running those scripts, users can mask sensitive data for every table separately thus reducing the risk of encountering errors due to manipulation of large amount of data in a single execution.
