Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Configure and Customize SQL Azure Dynamic Data Masking


By:   |   Last Updated: 2016-03-17   |   Comments (2)   |   Related Tips: More > Amazon AWS

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.

Customer_data_tables

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:

login to Azure

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

data masking

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

Azure portal

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

apply_the_masking


Add Mask

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.

Azure_database

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

Customer_data_tables

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?

SQL_users_excluded

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='[email protected]'
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.

SQL Azure database

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

masking rules

SQL Azure Database Dynamic Data Masking Rules

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

Masking method

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.

Click_add_mask

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.

update and save

Now let's see how the data looks:

see_the_data

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

Dynamic_data


Azure
Next Steps


Last Updated: 2016-03-17


get scripts

next tip button



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

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, March 23, 2016 - 3:19:47 AM - rajendra Back To Top

Hi Azim

 

Yes there may be different way to treat how sensitive information can be prevented to unauthorize use, it depends how we design the database. this features provides easy to implement and modify the requirements. thanks for the thought.


Tuesday, March 22, 2016 - 2:08:12 PM - AZJim Back To Top

 Raj ... there was something that I was wondering about.  While what you are suggesting is a very strong solution, perhaps another way would be to design a database with both the actual sensitive information as well as some "internal alias" information.  Let me explain. 

A credit card number with a person's other personal information is restricted.  But rather than storing the actual credit card number in the table, have an alias (or dummy) internal number.  Also have a lookup table containing the real credit card number.  Keep the lookup table restricted, but the table with the alias credit card number can be more accessible.  It can be partitioned on keys ranges and tested on test systems because it doesn't have the same level of sensitivity.  This could work for social security numbers, HIPAA medical restricted information, and other regulatory compliance type data.  Just a thought. 

 


Learn more about SQL Server tools