t sql – Dynamic Data Masking Issue when Concatenating Fields


You can reproduce the issue here:

CREATE TABLE (dbo).(EmployeeDataMasking)(
    (RowId) (int) IDENTITY(1,1) NOT NULL,
    (EmployeeId) (int) NULL,
    (LastName) (varchar)(50) MASKED WITH (FUNCTION = 'partial(2, "XXXX", 2)') NOT NULL,
    (FirstName) (varchar)(50) MASKED WITH (FUNCTION = 'partial(2, "XXXX", 2)') NOT NULL,
 CONSTRAINT (PK_EmployeeDataMasking) PRIMARY KEY CLUSTERED 
(
    (RowId) ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON (PRIMARY),
) ON (PRIMARY)
GO

Insert Into dbo.EmployeeDataMasking (EmployeeId, LastName, FirstName)
VALUES( 1,'Smithsonian','Daniel'),( 2,'Templeton','Ronald')

Select  
    EmployeeId,
    LastName,
    FirstName,
    LastName + ', ' + FirstName
From dbo.EmployeeDataMasking

enter image description here

Notice the LastName and FirstName fields are partially masked (as expected). However, the combined name field contains the default mask. I don’t know if this is considered a bug. However, I would think the combined field would retain the mask of the two fields it comprises. At least that’s what I would prefer, since I don’t know how to provide a mask for the combined field.