Applies to
Summary
This article explains how to use the application to partially mask SQL Server data using redaction rule and replace a certain amount of original data with the masked one.
Description
Using the redaction rule and certain additional options in it, which will be shown further in the article, it can partially mask SQL Server data in any SQL database. In this case, the original data from the database will be partially masked in the way where the first two and the last one value or character will be replaced with the desired wildcard where the part of that original information will be hidden for the public use.
Starting the application on the first New project window, the necessary fields must be fulfilled, such as SQL Server, type of the Authentication and SQL database. In this case, SQL Server 2019 with the Windows authentication and the Suppliers database will be used:
When everything is set, click the Connect button, and it will load the SQL database with already automatically scanned sensitive data in it. On the right side, when the column in the object is selected, it will show in the Preview panel suggested masked SQL data. For this example, the Address column is selected from the dbo.Customers table:
For this case, to partially mask SQL Server data, columns Address and Phone from the dbo.Customers SQL table will be used, and the first two and last one original value will be replaced with the wildcard “X”. To do that, please select the Manage button from the Home tab:
When the Manage masks window is opened, it will show the Substitution and Redaction masks. Here select the Redaction mask and click the New button:
It will open the Create redaction mask window where parameters will be set in order to partially mask SQL Server data from the SQL database. Name and Description are set by choice, to be recognized when the redaction mask is assigned to the desired columns. Use wildcard has to be checked and for the partially masking check the Partial masking, First and Last checkboxes. Under the Replace with field enter the desired value which will replace original data, in this case, “X” will be used and the last step is to change the values from 0 to 2 on the First field and from 0 to 1 on the Last field which will replace the first two and last one value in the previously mentioned columns:
Click the OK button will save the redaction rule and will return to the Manage masks window where the saved rule can be seen. The Redaction masks can be saved for the further use by exporting them on the Export button and later can be used on another SQL database importing them by using Import button:
When everything is set, click the OK button, and it will go back to the main grid. In the main grid, as already seen, certain SQL tables and columns are, by default, automatically scanned and selected as a suggestion to mask the SQL data. Since only the columns Address and Phone from the dbo.Customers SQL table will be used, please uncheck everything else from the main grid and then select the mentioned columns, go on the right-click, navigate to Add mask, Redaction and it will show the redaction mask which is previously created and left click to assign to the checked columns:
When the redaction mask is assigned in the Preview panel will be shown how will mask the original data, and in this example, the original data is masked as mentioned previously. First two and the last one value will be replaced with the “X” the rest will remain from the original SQL data:
To start the masking process, please click the Process button from the Home tab:
Once the process is started, it will first show the Job summary where once again can be re-checked if the right amount of columns and masks is assigned. In this example, as mentioned, two columns with one redaction mask are assigned. The partially mask SQL Server data will be directly used on the SQL database and to do that proceed further by clicking the Update database button:
When the process is finished to confirm that process is correctly done and the SQL database is updated please check from the SSMS the dbo.Customers SQL table where Address and Phone columns can be seen:
