Dynamic Data Masking on Azure SQL Database has been in preview for a few months now and is about to be released for General Availability. We’ve on-boarded several customers and gotten some great feedback – and we’re glad to report that this feature is now more robust and easier than ever to configure for your database.
What is DDM?
Dynamic Data Masking (DDM) is used to limit access to sensitive data in the database by obfuscating it on-the-fly in query results.
Data privacy is a major concern today for any organization that manages sensitive data or Personally Identifiable Information (PII). Organizations are required to adhere to various compliance standards and regulations, which demand the protection of sensitive data in their systems. Additionally, incidents of compromised data systems and security breaches are prevalent threats, and best practices dictate that we reduce the risk of compromise by preventing exposure of potentially sensitive data.
Dynamic Data Masking provides a mechanism to limit the exposure of sensitive data, by controlling how the data appears in the output of database queries. Masking rules can be defined on particular columns, indicating how the data in those columns will appear when queried. There are no physical changes to the data in the database itself, the data remains intact and is fully available to authorized users or applications. Database operations remain unaffected, so DDM can be applied without making any changes to database procedures or application code.
DDM can be used to obfuscate data in an application, so that restricted data is not revealed. For instance, a service representative using a call center application to identify a customer’s payment information should only see the last 4 digits of the customer’s credit card number. A simple DDM rule can ensure that any time the credit card number field is queried by the application, the number will be masked out except the last 4 digits.
Another great use case for DDM is enabling engineers or operations personnel to access a production database for maintenance or troubleshooting purposes, without exposing the PII data in the database. A DDM policy can be enabled to obfuscate the PII data in real time for such a scenario, so that such tasks can be achieved without violating any privacy policies.
An additional way that customers use DDM is for the scenario of a data analyst who creates business reports on the production database. With a DDM policy defined to protect PII data, the analyst does not need to be concerned about being exposed to the sensitive data without a business need.
Dynamic Data Masking is complementary to other security features in SQL Database (e.g., auditing, encryption, RLS) and should be used as part of a comprehensive access control and data protection strategy.
Highlights and recent improvements
DDM has introduced several recent improvements to the service:
Remarkably simple to set up
- DDM now suggests recommended fields to mask from your database in the Azure Preview Portal. The DDM recommendations engine helps users easily find potentially sensitive fields that are good candidates for masking. When you accept a recommendation from this list, a mask can be enabled on that field in one click!
- Adding a mask on any column in your database in the Azure Preview Portal is now easier than ever. Your database structure is presented in dropdown lists – so you just need to select the desired schema, table, and column from the lists – and then select an appropriate masking function (the irrelevant masking functions are grayed out for you).
- There is no longer a need to modify any of your application connections to use a security-enabled connection string. Just enable the mask on your column, connect to the database as usual and the field will be masked.
- DDM is now more robust and supports more cases for masking. For instance, a query that performs a SELECT INTO from one table to another results in masked data in the target table, so that masked data cannot be revealed.
- DDM supports Azure Active Directory (AAD), so you can list an AAD identity or group as exempt from masking when using Azure Active Directory (AAD) authentication to connect to your SQL database.
Setting up a DDM policy for your database
We’ll present a demo to show how super-simple it is to configure DDM for your database.
To get started, navigate to your database settings in the Azure Preview Portal and click on Dynamic data masking to open the configuration blade.
Here, you’ll see the current masking rules you have defined, and a list of SQL users who are excluded from masking. Note that database administrators are always excluded and will see the original data without any mask.
Add Recommended Masking Rules
On the database configuration blade, you may see some of your columns which the recommendation engine has flagged as containing potentially sensitive data. These may be good candidates for applying Dynamic Data Masking.
To create a mask from the recommendations list, click ADD MASK for one or more fields and then click Save – and you’re done! Data masking will now be applied to these fields, based on the masking functions selected by default. To change the masking function, you can click on the masking rule and edit the masking field format as desired.
Add custom masking rules
You can also create a data mask on any field in the database by clicking Add Mask at the top of the blade, and create a custom mask on any applicable database field with the format you choose. Note that you just need to select the desired schema, table and column from the dropdown lists. Be sure to click Save to apply these settings.
Add SQL users excluded from masking
There are situations where it might be necessary to configure SQL users who are exempt from masking. You can do so by entering the SQL users into the SQL users excluded from masking list. This also works for users mapped to AD identities as I am showing below. These users will now have access to the original data in the database. Note that users with administrator privileges are always excluded from masking.
Be sure to click Save for the settings to take effect.
To see Dynamic Data Masking in action, you can now connect to your database and perform a query on the masked fields. You will see the results masked, according to the defined format. Below is a snapshot of a query on the database where I have accepted the recommendations to mask the LastName, EmailAddress and Phone fields.
Dynamic Data Masking is a useful tool to help protect sensitive data by limiting its exposure to users who don’t have a business need to access that data. It can easily be applied to your sensitive database fields, and does not require modifications to the database code and application stack. It’s about to be released for General Availability – so give it a try today!