Shaun Xu

During the Chinese New Year holiday, Microsoft had just announced a new feature in V12 SQL Database named Dynamic Data Masking. This feature limits sensitive data exposure by masking it to non-privileged users.

We often have similar requirement in our project, that for some users they cannot view some of the data by displaying masked value. For example, email address may need to be displayed as j******@gmail.com in user profile page for normal visitor. In this case what we need to do is to implement the masking logic in our code. But this not very secured, and adds effort in our application layer.

SQL Database Dynamic Data Masking helps us preventing unauthorized access to sensitive data. Since it's inside SQL Database, there is almost no impact on the application layer.

Enable Dynamic Data Masking

To enabled this feature just open the SQL Database in azure new preview portal, open Dynamic Data Masking icon and enable it.

Please ensure your SQL Database supports V12 and latest updates. This is general available in some regions, but may still be in public preview stage in others. For example it is in preview stage in East Asia so you have to check the item below.

And make sure the pricing tier you selected supports this feature.

Now everything is OK. We can create our tables and insert data records into this new SQL Database. Assuming we have a table named Contacts with several columns:

1, ID: Integer, no need to protect.

2, Name: String, user name, no need to protect.

3, Email: String, need to be masked for normal user.

4, Credit Card Number: String, need to be masked for normal user.

5, Password Hint: String, need to be masked for normal user.

Configure Masking Policy

Even though we have data in tables and columns, we can add masking policies without data modification. Just configure the policy in azure portal by opening the Dynamic Data Masking icon.

First we need to define which SQL Server Logins have the permission to view unmasked data, which is called Privileged Login. In this case I already have two logins in my SQL Database Server: super_user and normal_user. I added super_user to the privileged logins.

Then specify the table and column name as well as the masking policy. For example for the Email column I was using build-in email masking policy.

I can add more masking policies for columns I'd like to protect as below.

View Data from ADO.NET Client

Below I created a simple console application in C# and connect to the database I've just created. In order to make the dynamic data masking feature work, I need to use security enabled connection string rather than the original one.

The console application source code is very simple. Note that I'm using security enabled connection string with the super_user login.

   1: using System;

   2: using System.Collections.Generic;

   3: using System.Data.SqlClient;

   4: using System.Linq;

   5: using System.Text;

   6: using System.Threading.Tasks;

   7:  

   8: namespace shx_maskingdatademo

   9: {

  10:     class Program

  11:     {

  12:         static void Main(string[] args)

  13:         {

  14:             var connectionString = ""

  15:                 + "Server=tcp:insider.database.secure.windows.net,1433;"

  16:                 + "Database=shx-maskingdatademo;"

  17:                 + "User ID=superuser@insider;"

  18:                 + "Password={xxxxxxxxx};"

  19:                 + "Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";

  20:             var builder = new SqlConnectionStringBuilder(connectionString);

  21:             using (var conn = new SqlConnection(connectionString))

  22:             {

  23:                 using (var cmd = conn.CreateCommand())

  24:                 {

  25:                     cmd.CommandText = "SELECT * FROM Contacts";

  26:                     conn.Open();

  27:                     Console.WriteLine("Server: '{0}'", builder.DataSource);

  28:                     Console.WriteLine("Login : '{0}'", builder.UserID);

  29:                     using (var reader = cmd.ExecuteReader())

  30:                     {

  31:                         while (reader.Read())

  32:                         {

  33:                             Console.WriteLine("{0}\t{1}\t{2}\t{3}\t{4}", reader[0], reader[1], reader[2], reader[3], reader[4]);

  34:                         }

  35:                     }

  36:                 }

  37:             }

  38:  

  39:             Console.WriteLine("Press any key to exit.");

  40:             Console.ReadKey();

  41:         }

  42:     }

  43: }

I can view all data without masking.

But when I switched to normal_use.

   1: using System;

   2: using System.Collections.Generic;

   3: using System.Data.SqlClient;

   4: using System.Linq;

   5: using System.Text;

   6: using System.Threading.Tasks;

   7:  

   8: namespace shx_maskingdatademo

   9: {

  10:     class Program

  11:     {

  12:         static void Main(string[] args)

  13:         {

  14:             var connectionString = ""

  15:                 + "Server=tcp:insider.database.secure.windows.net,1433;"

  16:                 + "Database=shx-maskingdatademo;"

  17:                 + "User ID=normaluser@insider;"

  18:                 + "Password={xxxxxxxxx};"

  19:                 + "Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";

  20:             var builder = new SqlConnectionStringBuilder(connectionString);

  21:             using (var conn = new SqlConnection(connectionString))

  22:             {

  23:                 ... ...

  24:             }

  25:  

  26:             Console.WriteLine("Press any key to exit.");

  27:             Console.ReadKey();

  28:         }

  29:     }

  30: }

All sensitive data were masked automatically.

Security Connection String Only

In order to make my masking policy enabled I need to connect to my database though the security enabled connection string. If I was using the original connection string you will find all sensitive data were returned as it is even though I was using normal_user login.

In order to protect my data in all cases, I will back to azure portal to switch the Security Enable Access from "optional" to "required". This means my database only allows security enabled connection string.

Now if I tried to connect to my database through the original connection string, I will receive an exception.

Summary

SQL Database Dynamic Data Masking limits sensitive data exposure by masking it to non-privileged users. Dynamic data masking is in preview for Basic, Standard, and Premium service tiers in the V12 version of Azure SQL Database. It’s a policy-based security feature that hides the sensitive data in the result set of a query over designated database fields, while the data in the database is not changed. This means we can have those kind of data protected, upgrade the pricing tier and enabled V12 without migrate them to another database, and almost without any code changes.

Hope this helps,

Shaun

• Share This Post:
• 
• 
• 
•   Short Url: http://wblo.gs/g1j

Posted On Friday, February 20, 2015 2:38 PM
Filed Under [ Microsoft Azure ]