As with all things, I turned to PowerShell for the answer and fortunately I found it: – basically I can get existing DDM configurations and set new configurations for columns directly from my Azure SQL DB using PowerShell.

However when we potentially have a lot of columns or DBs to configure masks for this is really going to get very old very fast. So when a customer asked this week if it was possible to configure Dynamic Data Masking from SQL Data Catalog ( because they’d seen the “Treatment Intent” category and the tag that clearly states “Dynamic Data Masking”), just like we’re able to configure Static Data Masking, well now that was a challenge I couldn’t turn down!ĭDM in the Azure Portal for the DMDatabase_Dev, with masks configured on customer_firstname and customer_email The more you do the easier it is to prove you’re doing something and the more likely you are to BE protected.“ Whilst a lot of people like to pick up on some of the well known downsides of DDM, it’s not like you’re entrusting the entire security of an environment to it alone – there are a ton of measures we can put in place and DDM is just one like an ex-colleague of mine (someone very wise whom I admired greatly and am still sad to this day I no longer get to work with them) used to say: “ It’s about building a defensible position. As far as customers see they have access to their data through our site or application no issues, but if anyone else needs to query that data, or different people need to see different results when querying environments, DDM has been their way to go. Dynamic Data Masking (DDM)ĭDM is a method of masking the data based on your access rights to the data. To tackle this then, many people I work with turn to Dynamic Data Masking. Note: I actually produced an end-to-end video showing the process of Cataloging, Masking and Cloning in under an hour here: – if you watch/try it let me know how you get on!įunnily enough though we wouldn’t want to use SDM for Production (and potentially Staging) environments though – as it irreversibly changes the data, it’s just going to completely mess up all of our Prod data.

I often work with teams to implement SDM and one of the fastest routes to successfully generating your masking sets for cloned environments is, of course, SQL Data Catalog (or cataloging solution of your choice) – you’ve already put the effort in to classify your columns and figure out where sensitive information exists within your databases and instances… so doesn’t it make sense that we can just use THAT as a source of truth and generate masking from there? I talk about it a LOT, but I’m always talking about Static Data Masking (SDM), which is the process of masking the data at the file level itself so it is irreversibly altered – this is fabulous for non-Production environments like Dev and Test, especially when you pair it with a good cloning technology.īut what about Staging / Production environments? I spend a great deal of my time talking about Data Masking, don’t believe me? Checkout here, here, here and even here. The most obvious thing to do is to re-scan our DB so Catalog is up to date and then fetch all of our columns first into a collection: (You can find the full documentation on the PowerShell module and a cmdlet reference here in the documentation.) The Process As we’ve seen from the many, many blog posts before this the answer is pretty straight forward – SQL Data Catalog’s PowerShell module once again saves the day! The easiest thing to do in this instance is an automated check – write once, run many times. But because this isn’t technically a schema change and we can’t necessarily rely on the developers to know when this change will happen – how do we catch it? What happens over time however, is that some tables which we originally weren’t using suddenly start to populate with data, and others which were in-scope now suddenly aren’t. Now empty tables are a common occurrence and most people I speak to have a bunch of them, for a multitude of reasons they’re using an ERP/CRM and aren’t using some of the modules, some of them are older and are gradually being phased out etc. What I mean is we have a fantastic process in place: we have completely classified our entire environments, developers move changes to classification up-stream just like schema changes and we have an automatic process to check and update classifications as part of our pipeline.īut as part of this process we “descoped” everything that was non-sensitive, including (crucially) empty tables. “The surprise is that you continue to be surprised.”īut what surprise am I alluding to? Of course it is the surprise that “all of a sudden” you have sensitive data in tables where there ought not be!

Funny blurry filter photo gif