search

microsoft / DacFx

DacFx, SqlPackage, and other SQL development libraries enable declarative database development and database portability across SQL versions and environments. Share feedback here on dacpacs, bacpacs, and SQL projects.
https://aka.ms/sqlpackage-ref
MIT License
308 stars 18 forks source link

Non-default dynamic data masking operations not supported for date #476

Open Thorium opened 1 month ago

Thorium commented 1 month ago

I'm having a database table with data-masking of day so that data-analyst can see person's age but not private information like full date of birth. ("XX May 2024")

Specification: https://learn.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking?view=sql-server-ver16#define-a-dynamic-data-mask

This standard says it should be datetime("D") and it does exist and works in SQL-server, but compiling that with DacFx crashes.

Steps to Reproduce:

  1. Have any table with such masking function, for example:
CREATE TABLE [Person] (
[DateOfBirth]  DATE MASKED WITH (FUNCTION = 'datetime("D")') NOT NULL
)
  1. Try to run the tool. It fails for:

error SQL70630: The data masking function provided for column BirthDate is invalid.

Did this occur in prior versions? Yes, not a regression issue.

ErikEJ commented 1 month ago

@Thorium Have you tried the latest DacFX version? 162.3

Thorium commented 1 month ago

I just have what comes with VS2022 / dotnet latest release.

ErikEJ commented 1 month ago

@Thorium OK, I misunderstood "Try to run the tool." - what exactly do you mean by that then?

Thorium commented 1 month ago

I try to build the MSBuild.Sdk.SqlProj with VS2022 the dacpac, it calls the SqlPackage behind the scenes.