SQL 2016 + Dynamic Data Masking = Exposure
Starting with SQL 2016, Microsoft has included a new “security” feature called Dynamic Data Masking, or DDM for short. This new feature is supposed to allow you to secure data by masking it to people who should not have access. For example, if you are a DBA and you have a database with PCI (Credit Card info) in it, you could use DDM to mask the CC numbers and not allow users without admin rights to view the real data…
… in theory.
In reality, there are more holes in this feature than a pair of 80’s denim pants. Here is an example of how to bypass it:
Say you have a table with a sensitive field; we will pretend it is a social security number. If you mask that field, and then run a SELECT ssn FROM tblTable, you will get a bunch of results that just show “xxxx” in the field.
Now… what if we apply the same concepts as a comparative SQL injection attack…
You start by creating either an array, table, or temp table and then running a test against each character of the social security number:
What do you suppose happens? Yep, data returns unmasked.
So, how do you prevent this?
In order to keep this from happening, you have to only allow access to views or stored procedures. This will force the use of the masking query and prevent direct query against the table containing the sensitive data.
At least until Microsoft enhances the DDM features, I would recommend not using it at all and actually scrambling data on DEV and QA instances. As for PROD… if you’re doing your job, no one should have access to it. Web applications by nature will need to be unmasked in PROD since users can access their own information and the execution of the query is under the context of the account running the web application.