Configure and Customize SQL Azure Dynamic Data Masking

By: Rajendra Gupta | Updated: 2016-03-17 | Comments (2) | Related: > Azure

Problem

It is very important for organizations to protect sensitive information such as customer details, credit card numbers, social security numbers, etc. Only authorized users should be able to see this data. SQL Server 2016 helps limit data exposure to only authorized personnel with Dynamic Data Masking in SQL Server 2016. In this tip we will be looking at how to use this feature with SQL Azure Database.

Solution

The Azure SQL Database supports Dynamic data masking in a very flexible and simple way by providing built-in masking rules and recommendations for which data should be masked. Dynamic Data Masking is supported in the V12 version of SQL Azure Database. Dynamic data masking can be easily applied and maintained using the Azure portal.

Sample Table and Data for the SQL Azure Database Dynamic Data Masking Example

To start, let's create sample data in our Azure SQL Database to demonstrate the feature.

--Sample table
CREATE TABLE [dbo].[Customer_data](
[ID] [int] primary key clustered,
[Name] [nvarchar](70)NULL,
[CreditCard] [varchar](30)NULL,
[ContactNo] [nvarchar](20)  NULL,
[Email] [nvarchar](60)NULL
)ON [PRIMARY]

-- Sample data into the table
Insert into dbo.customer_data values (1,'Peter mark','1545-6587-7747-3215','8785785471','[email protected]')
Insert into dbo.customer_data values (2,'Victor kens','8747-1214-6325-0000','7475984712','[email protected]')
Insert into dbo.customer_data values (3,'sart lpix','4444-7568-6254-1214','9565848521','[email protected]')

The dbo.customer_data tables holds sensitive data such as credit card, phone number and email address as shown below.

SQL Azure Dynamic Data Masking Configuration

Now we will login to Azure portal from https://portal.azure.com to see how to apply Dynamic Data Masking.

After we login, we can see the databases available in our Azure portal:

Now once we click on the database name, it shows different options. Select "Dynamic data masking" in the right pane.

Dynamic data masking is a very intelligent tool which automatically provides recommendations based upon the current data. In our example, here are the recommendations:

Now it is very simple to apply Dynamic Data Masking, just click on the Add Mask button and save the configuration.

Testing Dynamic Data Masking in SQL Azure Database

Now let's connect to the SQL Azure Database from SQL Server Management Studio to review the data in the table.

When we review the data we can see that the data is exactly the same.

Validating SQL Azure Dynamic Data Masking for a Non Administrative User

We cannot see the impact of applying the Dynamic Data Masking rules. Now the question is why?

If we look at the Dynamic data masking window, it shows SQL users excluded from masking ("administrator users are by default excluded"). Since we were connected to SSMS via a user account with SQL Server sysadmin rights, the data is not masked.

So let's create a SQL Server user with DB reader access and try again. To create the user and login follow the steps below.

CREATE LOGIN Azureread WITH password='password@1'
GO

CREATE USER [Azureread] FOR LOGIN [Azureread] WITH DEFAULT_SCHEMA=[dbo]
GO

Open a new query window in SSMS for the desired database and provide read only access to the user:

EXEC sp_addrolemember 'db_datareader', 'Azureread';
GO

Now let's connect the SQL Azure database with the user created above and see the results.

We can see the data with the Dynamic Data Masking rules applied.

SQL Azure Database Dynamic Data Masking Rules

Below are the default Dynamic Data Masking rules as shown in the portal:

Here are the basic rules:

The credit card masking method exposes the last four digits of the designated fields and adds a constant string as a prefix in the form of a credit card as XXXX-XXXX-XXXX-1234.
The social security number masking method exposes the last four digits of the designated fields and adds a constant string as a prefix in the form of an American social security number as XXX-XX-1234.
Email masking method which exposes the first letter and replaces the domain with XXX.com using a constant string prefix in the form of an email address as [email protected]

Custom Data Masking Rules for SQL Azure Database

Now we want to apply masking for the contactNo column which was not presented in the recommendations by Azure portal. To do so, click Add Mask-> Select the appropriate type-> Add and save.

Now if we don't want the default format for credit card data masking and want to change the format, we can click on the masking rule applied -> select Masking Field Format as custom string -> update and save.

Now let's see how the data looks:

We can also exclude users in addition to the admin user who should see all the data.

Next Steps

As you move to the cloud, be sure to keep this functionality in mind.
Check out other SQL Server 2016 tips
Read Use Dynamic Data Masking in SQL Server 2016 CTP 2.1
Read more about Dynamic data masking

About the author

Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2016-03-17