I need to send a database backup to a vendor for an upgrade, and somehow need to mask several columns containing PII. Was looking into static data masking, but this seems to change the data permanently. Dynamic data masking seems better as it hides it as it is accessed by users without unmask permissions. But once this database is backed up and sent off for the vendor to restore, could the vendor not grant themselves higher level permissions to see the data anyway? They will likely need db_owner anyway to make all the structural changes needed for the upgrade. Is there a better (hopefully low-effort) way to mask certain columns without permanently altering data for a backup?
YES, they will read the data in clear text!
You have 2 options (+ a bonus) to archive what you want:
Dynamic Data Masking
Dynamic Data Masking: which is only applied to your SQL Server instance. As soon as you take a backup of it to a
.bak file and you send it to the vendor the Dynamic Data Masking doesn't even comes with it. The vendor doesn't even has to create a new user o change the ownership. His own user will already have full access.
The way you should use Dynamic Data Masking is this: you keep the database on your side and you create a user for the vendor:
CREATE USER DDMUser WITHOUT LOGIN; GRANT SELECT ON Employee_Financial TO DDMUser;
you then mask a column:
ALTER TABLE Employee_Financial ALTER COLUMN EMP_Last_Name varchar(10) MASKED WITH (FUNCTION = 'default()');
You then select that column as the user to see what they will see:
EXECUTE AS USER = 'DDMUser'; SELECT * FROM Employee_Financial; REVERT;
And as you are selecting those data as the user
DDMUser you will not see that specific column.
You now open a port for the vendor and you can allow them to connect to your database because they will not see the data you have masked.
Static Data Masking
Static Data Masking is only available in preview in SQL Server 2019 and Azure SQL Database.
In that case you can mask the database and send it to the vendor.
Lately I have extensively wrote on my blog about how to setup a static data anonymization for SQL Server.
After a few months I have to admit it: it's a pool of blood. I'm the first person who tells you: don't go this way and keep this option as the very last chance. Focus on Dynamic or Static data masking provided by Microsoft out-of-the-box.