Dynamic Data masking for the Transaction replication table

Question

We are using Transaction replication in our database. We need to mask a table which exists in both publisher and subscriber database.
When we are applying dynamic data masking on that table in both publisher and subscriber database, it get applied on publisher but on subscriber we can still see the unmasked data.
Could someone help me to conclude if Dynamic data masking is feasible in this scenario. Thanks

Answer 1

I tested it on my local SQL server with both publication and subscription databases on it and it worked. I guess your publisher and subscriber are on the different servers. Make sure you run REVOKE UNMASK TO 'UserName' on both servers.

Answer 2

Run this on the subscriber database:

EXECUTE AS USER = 'UserNameToWhomWeWantToShowMaskedData'
go
SELECT * FROM sys.user_token
go
REVERT

This will list all security tokens for this user on database level. If any of these tokens hold the UNMASK permission, the user will be able to see the data without masking.

Answer 3

Hi @Murlidhar Patil ,

Welcome to Microsoft Q&A!

I made test for your question, and also get same result that can see the unmasked data on subscriber. In addition, if the Subscriber republishes data, the only supported schema changes are adding and dropping a column. These changes should be made on the Publisher using sp_repladdcolumn (Transact-SQL) and sp_repldropcolumn (Transact-SQL) rather than ALTER TABLE DDL syntax. Please refer to Transactional Replication.

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.

Answer 4

Unfortunately, I don't have the time to test myself.

However, I like point that dynamic data masking is a feature that is applied at run-time. That is, the data is stored in the same way, no matter whether it is masked or now.

Thus, if you are seeing different results at different servers, I can think of two reasons:

1. Different permissions. That is, UNMASK permission in one database, and not in the other.
2. Different metadata. Easiest way may be to script the tables on both Publisher and Subscriber.

Answer 5

So I did test finally. I created a database on SQL 2017, and in this I created a single Orders table:

CREATE TABLE [dbo].[Orders](
    [OrderID] [int] NOT NULL,
    [CustomerID] [nchar](5) NULL,
    [EmployeeID] [int] NULL,
    [OrderDate] [datetime] NULL,
    [RequiredDate] [datetime] NULL,
    [ShippedDate] [datetime] NULL,
    [ShipVia] [int] NULL,
    [Freight] [money] NULL,
    [ShipName] [nvarchar](40) NULL,
    [ShipAddress] [nvarchar](60) MASKED WITH (FUNCTION = 'default()') NULL,
    [ShipCity] [nvarchar](15) NULL,
    [ShipRegion] [nvarchar](15) NULL,
    [ShipPostalCode] [nvarchar](10) NULL,
    [ShipCountry] [nvarchar](15) NULL,
 CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED 
(
    [OrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT Orders SELECT * FROM Northwind..Orders

I verified that data was masked with a non-privileged user:

CREATE USER nonpriv WITHOUT LOGIN
go
GRANT SELECT ON Orders TO nonpriv
go
EXECUTE AS USER = 'nonpriv'
go
SELECT * FROM Orders
go
REVERT

I then created a publication for the database, including the table. I used plain simple Transactional Replication. I created a subscriber on SQL 2019. Once data had been replicated, I created my nonpriv user on the subscriber database. The data in the ShipAddress column was masked. If I view the table as myself, I the column is not masked as I am sysadmin.