Azure SQL Dynamic Data Masking

In my last blog post I showed how to implement dynamic data masking(DDM). With Azure it is even easier. Azure has a built in security setting to implement DDM.

Just launch the Azure portal and go to the database you want to mask. In this case I created a database just for this purpose called DDMTest.

I created table with sensitive data. The same table I used in the last demo. Launch SSMS and connect to your Azure database.

CREATE TABLE DDMTest.dbo.PersonalInfo (
	PersonalInfoID int NOT NULL IDENTITY(1,1),
	Fname varchar(50) NOT NULL,
	Lname varchar(50) NOT NULL,
	PhoneNumber char(12),
	email varchar(80),
	SSN char(11) NOT NULL,
	CONSTRAINT PK_PersonalInfoID PRIMARY KEY (PersonalInfoID),
	CONSTRAINT UC_SSN UNIQUE (SSN)
) 

INSERT INTO DDMTest.dbo.PersonalInfo
(Fname, Lname, PhoneNumber, email, SSN)
VALUES('Janet', 'Smith', '320-409-1065', 'Jsmith@hatmail.com', '870-71-6633');

INSERT INTO DDMTest.dbo.PersonalInfo
(Fname, Lname, PhoneNumber, email, SSN)
VALUES('Ron', 'Foo', '555-123-6633', 'FooBar@yakhoo.com', '921-66-2211');

INSERT INTO DDMTest.dbo.PersonalInfo
(Fname, Lname, PhoneNumber, email, SSN)
VALUES('Andrea', 'Erchel', '521-671-3322', 'AErchle@Geemail.com', '521-32-4328');

 

Go back to your Azure portal and go to the database security settings and select dynamic data masking.

Azure identifies potentially sensitive fields and recommends fields to mask. This time Azure found all my sensitive fields! You can pick other fields, and decide on the masking functions you want. Here I just went with the recommended fields and used the recommended masking functions.

hit OK and Save

Now I will create a low privileged user with SELECT permission on the table and run a query as that user.

Select permissions is not enough to see the real data. We can grant UNMASK on the database for the user to see the real data.