Using SQL Server Dynamic Data Masking with the Continuous Data Engine (KBA10533)
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 Data Engine
This article discusses the implications of using the SQL Server Dynamic Data Masking feature on Source Databases and VDBs used with the Continuous Data Engine.
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 dSources and Compression
When using the Dynamic Data Masking feature, dynamic masking rules are stored as part of the table definition. The use of this feature does not affect how the data is stored on disk.
As a result, when using this feature:
- Unmasked data is still stored unencrypted on the Source Database server, unless the separate Transparent Data Encryption feature is used.
- The Continuous Data Engine will also store an unencrypted, unmasked copy of the Source Database when taking dSource snapshots, unless the Source Database was encrypted using Transparent Data Encryption.
Adding or removing Dynamic Data Masking rules should not affect the dSource compression ratios achievable by the Continuous Data Engine nor the size of dSource Snapshots.
The Continuous Data Engine does not interrogate the schema of databases and does not change any behavior depending on whether the SQL Server Dynamic Data Masking feature is in use or not.
Impact of Dynamic Data Masking on VDBs
Because SQL Server Dynamic Data Masking rules are stored as table metadata, virtual databases (VDBs) provisioned from dSources that use this feature will also contain the Dynamic Data Masking rules.
Related Articles
The following articles may provide more information or related information to this article:
- Dynamic data masking
- Setting Up SQL Server Transparent Data Encryption (TDE) For Use With The Delphix Engine (KBA1401)