SQL Server 2016 – Security: Dynamic Data Masking – Other Data Types

This blog is a part of the series “SQL Server 2016 – Security: Dynamic Data Masking“.
I remember you my different logins used to connect to AdventureWorks:

• sql login u1 with the db_owner role –> No masking
• sql login u2 with the db_datareader role –> masking

The Data Types used are: xml, date, datetime and varbinary.

I run this query to have all columns with the tested data type in the clause WHERE:

SELECT  T.NAME AS [TABLE NAME], C.NAME AS [COLUMN NAME], P.NAME AS [DATA TYPE], P.MAX_LENGTH AS[SIZE]
FROM SYS.OBJECTS AS T JOIN SYS.COLUMNS AS C
ON T.OBJECT_ID=C.OBJECT_ID
JOIN SYS.TYPES AS P
ON C.SYSTEM_TYPE_ID=P.SYSTEM_TYPE_ID
WHERE T.TYPE_DESC='USER_TABLE' and T.NAME<>'sysdiagrams' and P.NAME='xml' ORDER BY T.NAME,C.NAME

XML Data Type

It is one column XmlEvent in the table DatabaseLog.
For fun, I will test the 3 masking modes:

• Default:
  • Query:

    ALTER TABLE [DatabaseLog] ALTER COLUMN [XmlEvent] ADD MASKED WITH (FUNCTION = 'default()')

  • Result: Command(s) completed successfully.
• Email:
  • Query:

    ALTER TABLE [DatabaseLog] ALTER COLUMN [XmlEvent] ADD MASKED WITH (FUNCTION = 'email()')

  • Result: Msg 16003, Level 16, State 0, Line 13
  • The data type of column ‘XmlEvent’ does not support data masking function ’email’.
• Custom String:
  • Query:

    ALTER TABLE [DatabaseLog] ALTER COLUMN [XmlEvent] ADD MASKED WITH (FUNCTION = 'partial(1,"XXXXXX",0)')

  • Result: Msg 16003, Level 16, State 0, Line 14
  • The data type of column ‘XmlEvent’ does not support data masking function ‘partial’.

For the data type XML, only default mode is available.

And the question is, what the user see insteadof the real data…

With u1, I see all xml data and 112 rows

With u2, I see for all 112 rows but with a value <masked\>

Date and DateTime Data Types

With u1, I see all different values.

With u2, I see for all rows with a value of 2000-01-01 for date and 2000-01-01 00:00:00.000 for datetime.

Varbinary Data Type

I run my SELECT:

Like Xml, I will test the 3 masking modes:
For Default, no problem but for email & custom String, we have the error:
Msg 16003, Level 16, State 0, Line 106
The data type of column ‘….’ does not support data masking function ‘partial’.
Only Default is available for Varbinary

With u1, I see all different values.

With u2, I see for all rows with a value of 0x30 for varbinary.

Core Message

• For XML data type, only the default masking mode is enabled and the mask value is <masked\>
• For date and Datetime datatype, the default masking is the only one available with the date 01.01.2000 with 00:00:00 for the time
• For varbinary data type, only the default masking mode is enabled and the mask value is 0x30