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?
3
-
3I wouldn't send the data to a vendor and this sounds highly unusual. The vendor should supply a script or program to upgrade the database that someone in your company runs, obviously perform a full backup first.– SteveCommented Feb 10, 2022 at 22:30
-
2Yes. They're going to have privileges to override dynamic data masking. That utility is not meant for stuff that you send out of your control. Static, read permanent, is what you need in this scenario. Sorry it's more work, but no one said the job was easy.– Grant FritcheyCommented Feb 11, 2022 at 13:44
-
Great, thank you. For context there was a chance that the vendor could not do the needed upgrade on our servers because they had some esoteric proprietary tools they generally used for that, which gave us discomfort. It was possible that they needed a backup sent to them (not a great option) which they would upgrade and send back to us (making static data masking unworkable and dynamic ineffective). We are pushing for them to find a way for them to connect to our servers and do the upgrade there with user accounts we provide. In any case, we'd make them adhere to some kind of privacy NDA.– BrianCCommented Feb 11, 2022 at 16:22
Add a comment
|
1 Answer
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.
Bonus
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.
answered Feb 11, 2022 at 15:28
