Friday, November 22, 2024
Latest:

SQLChamp

Let's learn together!

Azure Data PlatformSQLAzureSQLServerSQLServer on Azure VM

Must know facts before configuring Dynamic Data Masking

Sarabpreet Singh Anand

Dynamic data masking is yet another feature of Microsoft to ease your life and provide you out of the box capabilities which decrease the overall time to market for your applications. You don’t have to code the way you had to do it earlier to achieve it. But there are things you must know before configuring this amazing feature. Everything has its own limitations, you must know facts before configuring Dynamic data masking correctly. Configuring without this knowledge may put your data on risk & it’s not advisable. Do read more about security here.

 

Must know facts before configuring Dynamic Data Masking

  • Masking doesn’t restrict DML operation on columns with sensitive data: Do consider this carefully, you may consider this enough that the users don’t get to see sensitive data in plain text but if the users have rights to issue DML operation for those columns, it will happen just like any other table. The user can easily change the data. Make sure you put other security measures in place like auditing, providing granular access, restrict the use of DML to only authorized users or may be only via application.

 

  • Dynamic data masking (DDM) is just a method of masking the data in the result set. The actual data never gets encrypted and if your user has direct access to your database (access to connect to database using SSMS, query window SQLCMD etc.) the user can do some guess work to defeat the purpose. Hence, you must apply other kind of mechanisms also if the data is so much super critical and user has direct access. For example here is how the user get masked data.

 

Must know facts before configuring Dynamic Data Masking
Must know facts before configuring Dynamic Data Masking

 

And here is how he can easily guess the actual CreditRating which is masked by a random range between 50 and 60. So there are columns for which user can issue such statements to guess the actual value.

Must know facts before configuring Dynamic Data Masking
Must know facts before configuring Dynamic Data Masking

 

  •  Please be aware that the Unmask permission is a global permission applicable for the entire database, so once you provide that unmask permission to a user, the user will be able to unmask everything for that database. You may don’t want to do it especially when users are not using any application and have direct query control over database. The UNMASK feature is not granular at all and cannot be granted for only a set of columns\tables.

 

  • Dynamic Data Masking is applied when running SQL Server Import and Export. A database containing masked columns will result in a backup file with masked data (assuming it is exported by a user without UNMASK privileges), and the imported database will contain statically masked data.

 

  • If you are using a “Down level Client” (that is clients using TDS 7.3 and below) for accessing the data, you must   use a modified connection string from  Servername.database.windows.net to servername.secure.windows.net.

 

  • A Masking rule cannot be created for the below listed column types also:
    • Encrypted Columns (Columns where Always Encrypted is configured)
    • FILESTREAM
    • COLUMN_SET or a sparse column that is part of a column set.
    • Computed columns, but if the computed column depends on a column with a MASK, then the computed column will return masked data.
    • Column with data masking cannot be a key for a FULLTEXT index.
    • For users without the UNMASK permission, the deprecated READTEXT, UPDATETEXT, and WRITETEXT statements do not function properly on a column configured for Dynamic Data Masking.
    • Adding a dynamic data mask is implemented as a schema change on the underlying table, and therefor cannot be performed on a column with dependencies. To work around this restriction, you can first remove the dependency, then add the dynamic data mask and then re-create the dependency. For example, if the dependency is due to an index dependent on that column, you can drop the index, then add the mask, and then re-create the dependent index.

You see these are very important Must know facts before configuring Dynamic Data Masking and since now you understand these you can go-ahead and configure DDM using Azure Portal or Configure DDM using T-SQL.
Hope you got answer to your questions! Happy Learning!

feel free to leave a comment. 🙂

Thanks,
Sarabpreet Singh Anand
Subscribe now to get latest Tips\blog posts in your Inbox