Masking and External Tables

Question

All,

The script below works fine on SQLServer 2019:

CREATE TABLE DefaultMask
(
ID INT   IDENTITY (1,1) PRIMARY KEY NOT NULL
,Name VARCHAR(255)  NULL
,BirthDate     DATE     NOT NULL
,Social_Security  BIGINT NOT NULL
);
GO

But the below script below gives an error. I need to setup an external table definition with data masking.

Msg 103010, Level 16, State 1, Line 1
Parse error at line: 4, column: 20: Incorrect syntax near 'MASKED'.

CREATE EXTERNAL TABLE DefaultMask
(
ID             INT              NOT NULL
,Name VARCHAR(255)  MASKED WITH (FUNCTION = 'default()') NULL
,BirthDate     DATE     MASKED WITH (FUNCTION = 'default()') NOT NULL
,Social_Security  BIGINT        MASKED WITH (FUNCTION = 'default()') NOT NULL
)WITH (DATA_SOURCE = [PolyRDM],LOCATION = N'[DataMart].[dbo].[DefaultMask]')
GO

For External Tables, are data masking not supported?

Thanks,
grajee

Answer 1

No, and it is not really surprising since external tables are quite different beasts.

You could submit a suggestion on https://feedback.azure.com/forums/908035-sql-server. I strongly recommend to state your use case - Microsoft evaluates the suggestions from business needs.

Answer 2

Hi @grajee ,

In an external table, data masking is not be supported. And there have some other limitations for data masking, please refer to Dynamic Data Masking to get the details. And SQL Server provide different ways to protect data, you can use another way to protect sensitive data, please refer to Protect your data at every layer to get more information.

Best regards,
Carrin

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.