Extraction of Dynamic Data Masking Recommendations

Question

We are investigating the Dynamic Data Masking feature of Azure SQL Database (General Purpose, Serverless). We have several hundred recommended fields to mask when we select the Home->Azure SQL -> Db-Name ->Security -> Dynamic Data Masking Option from the Azure portal..

a) How can we extract these recommendations from the Azure Portal to a file?

b) How can we use SQL to directly query the database for these recommendations?

c) We have attempted using the following SQL, but receive an error - Invalid object name sys.recommended_columns

SELECT schema_name(t.schema_id) AS schema_name,

   t.name AS table_name,

   c.name AS column_name,

   r.masking_function

FROM sys.recommended_columns AS r

JOIN sys.columns AS c ON r.column_id = c.column_id AND r.object_id = c.object_id

JOIN sys.tables AS t ON c.object_id = t.object_id

LEFT JOIN sys.masked_columns AS m ON c.column_id = m.column_id AND c.object_id = m.object_id

WHERE m.column_id IS NULL;

Thank you.

Answer 1

I looked at this in the portal and it is quite simple. First go through the list and click Add Mask for all of them. Note that the only thing that happens here is that columns are moved to the upper part of the screen. Nothing is saved until you press the Save icon (which you should not.)

One you have done this, place the cursor to the upper left of the table and select all lines. Copy and paste into Excel. Once you are in Excel there are multiple ways to go. What I did was to save the data has a UTF-8 csv file.

Then I created this table:

CREATE TABLE guest.MaskingRecommendations(schema_name sysname NOT NULL,
                                          table_name  sysname NOT NULL,
                                          colum_name  sysname NOT NULL,
                                          mask        nvarchar(200) NOT NULL)

I used the guest schema to separate it from other table, but you can do as you like.

Then I loaded the file with BCP:

bcp guest.MaskingRecommendations in MaskingRules.csv -c -t; -C 65001 -S MyServer.databases.windows.net -d MyDatabase -U MyLogin -P MyPassword

My separator is semicolon, since that is the European standard. Yours may be comma.

On first attempt it failed with a NOT NULL error, because there were blank lines in the Excel file which resulted in lines with only semicolons. I used a text editor to remove these lines, and I was able to load the table.

I'm uncertain about the mask field. I appears that it only says Default value (0, xxxx, 01-01-1900), but maybe that is my database.

You could use the table to generate the ALTER TABLE ALTER COLUMN commands.

You may also prefer to stay in Excel to make a manual review of which columns to mask and how to mask them, and only when you are done load it into the database.

.