Using SQL Server Dynamic Data Masking with the Continuous Compliance Engine (KBA10534)
KBA
KBA#Applicable Delphix Versions
- Click here to view the versions of the Delphix engine to which this article applies
-
Date Release Oct 18, 2023 16.0.0.0 Sep 21, 2023 15.0.0.0 Aug 24, 2023 14.0.0.0 Jul 24, 2023 13.0.0.0 Jun 21, 2023 12.0.0.0 May 25, 2023 11.0.0.0 Apr 13, 2023 10.0.0.0 | 10.0.0.1 Mar 13, 2023 | Mar 20, 2023 9.0.0.0 | 9.0.0.1 Feb 13, 2023 8.0.0.0 Jan 12, 2023 7.0.0.0 Releases Prior to 2023 Major Release All Sub Releases 6.0 6.0.0.0, 6.0.1.0, 6.0.1.1, 6.0.2.0, 6.0.2.1, 6.0.3.0, 6.0.3.1, 6.0.4.0, 6.0.4.1, 6.0.4.2, 6.0.5.0, 6.0.6.0, 6.0.6.1, 6.0.7.0, 6.0.8.0, 6.0.8.1, 6.0.9.0, 6.0.10.0, 6.0.10.1, 6.0.11.0, 6.0.12.0, 6.0.12.1, 6.0.13.0, 6.0.13.1, 6.0.14.0, 6.0.15.0, 6.0.16.0, 6.0.17.0, 6.0.17.1, 6.0.17.2
5.3
5.3.0.0, 5.3.0.1, 5.3.0.2, 5.3.0.3, 5.3.1.0, 5.3.1.1, 5.3.1.2, 5.3.2.0, 5.3.3.0, 5.3.3.1, 5.3.4.0, 5.3.5.0, 5.3.6.0, 5.3.7.0, 5.3.7.1, 5.3.8.0, 5.3.8.1, 5.3.9.0 5.2
5.2.2.0, 5.2.2.1, 5.2.3.0, 5.2.4.0, 5.2.5.0, 5.2.5.1, 5.2.6.0, 5.2.6.1
5.1
5.1.0.0, 5.1.1.0, 5.1.2.0, 5.1.3.0, 5.1.4.0, 5.1.5.0, 5.1.5.1, 5.1.6.0, 5.1.7.0, 5.1.8.0, 5.1.8.1, 5.1.9.0, 5.1.10.0
5.0
5.0.1.0, 5.0.1.1, 5.0.2.0, 5.0.2.1, 5.0.2.2, 5.0.2.3, 5.0.3.0, 5.0.3.1, 5.0.4.0, 5.0.4.1, 5.0.5.0, 5.0.5.1, 5.0.5.2, 5.0.5.3, 5.0.5.4
Using SQL Server Dynamic Data Masking with the Continuous Compliance Engine
This article discusses the impact of the SQL Server Dynamic Data Masking feature on Continuous Compliance Engine Masking Jobs.
Overview of the SQL Server Dynamic Data Masking feature
Dynamic Data Masking is a SQL Server feature that can be applied at the schema level to a column in a SQL Server database table.
Data from that table is presented in a masked format to any users that do not have the UNMASK privilege on the table or database.
For example, after applying a masking pattern to the Person table in Microsoft's AdventureWorks sample database:
ALTER TABLE Person.Person ALTER COLUMN LastName ADD MASKED WITH (FUNCTION = 'partial(2,"xxxx",0)');
A user with SELECT permissions on the Person table may see the following masked data when querying the Person table:
| BusinessEntityID | FirstName | LastName |
| 1 | Ken | Sáxxxx |
| 2 | Terri | Duxxxx |
A user with both SELECT and UNMASK permissions on the Person table would see the original data instead:
| BusinessEntityID | FirstName | LastName |
| 1 | Ken | Sánchez |
| 2 | Terri | Duffy |
This feature is offered by Microsoft in SQL Server 2016 and later, and is described further by Microsoft in their document Dynamic Data Masking.
Impact of Dynamic Data Masking on Profiling jobs
Database users used as part of Profiling jobs should have the UNMASK privilege at the database level or on all tables being profiled.
If the database user does not have the UNMASK privilege, and the SQL Server Dynamic Data Masking feature is in use, Profiling jobs may be impacted in the following ways:
- Profiling jobs that use Type Expressions, including the Standard Profile Set, may fail to correctly classify masked data.
- Profiling jobs that use the Automated Sensitive Data Discovery (ASDD) Profile Set may fail to correctly classify masked data.
Profiling jobs that use Legacy Profile Sets do not attempt to classify data and will be unaffected.
Impact of Dynamic Data Masking on Masking Jobs
Database users used as part of Masking jobs should have the UNMASK privilege at the database level or on all tables being masked.
If the database user does not have the UNMASK privilege, and the SQL Server Dynamic Data Masking feature is in use, Masking jobs may be affected in the following ways:
- If the Dynamic Masking Rule creates identical data for two inputs (masking "Ken" and "Karen" as "K****"), a Masking Algorithm may mask both values to the same output. This will create more duplicate values than expected and may violate uniqueness constraints.
- If the Dynamic Masking Rule randomizes data, a Masking Algorithm may unexpectedly create different masked output each time it is run.
- If the Dynamic Masking Rule changes the way data is presented (masking "12345" as "12***"), some Masking Algorithms may attempt to write unexpected characters back to the database, resulting in data type violations or unexpected results when using the masked data.
Impact of Dynamic Data Masking on masked databases
SQL Server Dynamic Masking Rules are not modified by the Continuous Compliance Engine and will still be present on databases after a Masking Job is completed. Any data returned by these rules will be based on the data modified by the Masking Job, instead of the original unmasked values.
Once sensitive columns has been masked by a Masking Job, Dynamic Masking Rules which affect these columns may no longer be required. These rules can be removed, depending on your business requirements for the masked database.
In many cases, it will be desirable to keep these Dynamic Masking Rules in place, so that application and database behavior is consistent between the original and masked copies of the database.
Related Articles
The following articles may provide more information or related information to this article: