Dynamic Data Masking Protects the View, Not the Model

Microsoft’s documentation for dynamic data masking in SQL Server contains a sentence that most write-ups about the feature omit. The purpose is to limit exposure of sensitive data, it says, and dynamic data masking does not aim to prevent database users from connecting directly to the database and running exhaustive queries that expose pieces of the sensitive data.

That is the vendor telling you the threat model in plain language, and the same page supplies the example: a masked salary column displays as zero while WHERE Salary > 99999 AND Salary < 100001 still returns the matching rows. The predicate runs against the real value. Joe Obbish’s Dynamic Data Unmasking on Simple Talk in 2020 takes a table of masked values apart on exactly that basis, and the Unmasking SQL Server Dynamic Data Masking series covers the format-constrained version, where knowing the shape of a phone number or a national ID collapses the search space.

Microsoft documents this behaviour as the design of the feature. In SQL Server, dynamic data masking rewrites the result set while the predicate still evaluates the stored value.

Masking is a display control. Whoever builds your training set either has permission to receive cleartext, in which case the mask never applied, or lacks it, in which case the extract can carry mask outputs instead of data. Neither path, on its own, gives the trained model a privacy property.

What it is, and where it sits

In SQL Server and Azure SQL, dynamic data masking rewrites query results at read time. The stored data is untouched and the rule is defined on the column. Applications do not need to change, because the masking happens between the query and the response. Principals with UNMASK see cleartext, and db_owner and sysadmin hold CONTROL, which includes it.

Implementations differ and the difference matters. Oracle Data Redaction carries the same predicate exposure and documents it. Snowflake takes another route, applying the masking policy wherever the protected column appears, including joins and predicates, which closes the inference channel this article opens with. So the scope of the claim is narrower than “masking leaks”. Dynamic masking is a query-time access and presentation policy, and whatever it does at the query boundary, it confers nothing on a model trained from the data behind it.

Static masking is the other half of the family and it is a different tool. It transforms the data itself, permanently, on the way into a non-production environment. Dynamic masking protects a view of production. Static masking creates a sanitised copy.

Held to what it actually claims, dynamic masking is a decent control. It stops a support agent seeing a full card number on screen. It keeps developers troubleshooting against production from reading customer records they have no reason to read. Configuration costs a column-level rule and leaves application code untouched. Those are real benefits, and dynamic masking delivers them.

The problem starts when someone points it at a machine learning pipeline.

The two doors into your training set

A training set gets built by someone running a query. There are two possibilities and both defeat the purpose.

The pipeline runs as a privileged principal. In many deployments the data engineering service account needs broad read access to do its job, so it holds UNMASK or belongs to a role that bypasses masking. The extract contains cleartext, the mask never applied, and the model trains on unmasked personal data. Masking is enforced against analysts and irrelevant to the pipeline that trains the model.

The pipeline runs unprivileged. Now the masking applies, to the wrong thing. SELECT INTO and INSERT INTO from a masked column persist the mask output into the destination table, and SQL Server’s Import and Export produces an exported file carrying the masks. A native BACKUP DATABASE copies pages of the stored data and therefore contains cleartext, because masking never touched what is on disk. What the training set gets, depending on the rule, is zeroes and XXXX where the signal was, so the model learns mask artefacts rather than the source distribution. That is a data quality failure presenting as a modelling problem, and it can take a long time to diagnose because nothing errors.

Neither branch produces what people assume the feature gives them, which is a training set with the sensitive fields protected and the useful signal intact. Getting that requires deliberate transformation of the extract: static masking, tokenisation or synthesis, and a decision about what the model actually needs.

What masking never touches

Set the pipeline question aside and assume you solved it. Masking still says nothing about the attacks this site is actually about, because every one of them targets the model rather than the database.

Models can memorise their training data, and large language models have been shown to reproduce verbatim training sequences on demand. Model inversion reconstructs representative features or class exemplars from model access, and in some settings information about individual training inputs. Membership inference tells an attacker whether a specific person’s record was in the training set, which in a medical or financial context is disclosure on its own. An attacker running any of these never queries the protected column, so the masking rule does not apply.

The controls that address the model are different, and they act during training or on the computation instead of on the query. Differential privacy bounds what any single record can contribute to the trained parameters, which is a mathematical guarantee about the model rather than about a view. Homomorphic encryption keeps data encrypted through the computation. Masking is not in competition with these; it operates at the query boundary against a different threat.

The earlier version of this article claimed that cryptographically secure masking offers a mathematical guarantee of security, unlike simple obfuscation. It does not. Differential privacy has a formal guarantee with a parameter you can state. Masking has a policy and an access model. Confusing the two is the specific error that leads teams to believe a masked warehouse gives them a defensible privacy position for the models they train off it.

Where it does earn its place in a pipeline

Three uses hold up.

Sanitised environments, via static masking. If the requirement is a development, test or experimentation environment carrying realistic data without real people in it, static masking is the tool and it is a good one. Two properties get collapsed here. Format preservation keeps each value the right shape. Transforming related values consistently across tables is what preserves referential integrity, so joins still work. Naive redaction breaks both. Neither amounts to anonymisation, and re-identification risk still has to be assessed against whatever auxiliary data exists.

Analyst access to production, via dynamic masking, with the threat model stated. Fine against accidental exposure and casual viewing through predefined application and reporting paths. Not a boundary against someone with query access and motivation, per the vendor’s own documentation. Pair it with row-level security, least privilege and an audited path to cleartext, and be honest in your controls documentation about which of the three provides the boundary.

Feature extraction that never needs the raw value. The most underrated one. A model that needs an age bracket does not need a date of birth. Bucketing tenure into years and replacing a postcode with its region remove the identifying fields from the extract before any masking rule could apply to it. Deciding at feature-engineering time which fields the model genuinely requires removes more privacy risk than any masking rule applied afterwards, and it costs analysis rather than infrastructure.

What to do on Monday

Find out which principal builds your training extracts and whether it holds UNMASK. That question has a definite answer that someone can look up in an afternoon. If the pipeline service account bypasses masking, the policy does not cover the highest-risk read path in the organisation.

Check whether any training set was built by an unprivileged principal from masked columns. If so, some of your features are masks. Look for columns with implausibly low cardinality or suspiciously uniform numerics.

Stop counting masking toward model privacy in your control documentation. It covers a view. If an assessor asks how you protect individuals in the training data, masking is not the answer. Anyone who reads Microsoft’s documentation will say so.

Decide what the model needs before deciding how to protect what it does not. Fields excluded from the extract need no model-specific control in that pipeline, though they still need the source system’s ordinary access, retention and logging controls. It is usually the cheapest privacy reduction available.

The reason this article needed rewriting rather than updating is that the original treated masking as a security technology and asked how well it performs. The better question is which layer it operates in. It operates between a query and a screen. It does that job adequately within the limits Microsoft documents, and the machine learning privacy problem starts after the extract leaves the database.