Shaun Xu

The Sheep-Pen of the Shaun


News

logo

Shaun, the author of this blog is a semi-geek, clumsy developer, passionate speaker and incapable architect with about 10 years’ experience in .NET and JavaScript. He hopes to prove that software development is art rather than manufacturing. He's into cloud computing platform and technologies (Windows Azure, Amazon and Aliyun) and right now, Shaun is being attracted by JavaScript (Angular.js and Node.js) and he likes it.

Shaun is working at Worktile Inc. as the chief architect for overall design and develop worktile, a web-based collaboration and task management tool, and lesschat, a real-time communication aggregation tool.

MVP

My Stats

  • Posts - 122
  • Comments - 622
  • Trackbacks - 0

Tag Cloud


Recent Comments


Recent Posts


Article Categories


Archives


Post Categories


.NET



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.

imagePlease 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.

image

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

image

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.

image

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.

image

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

image

 

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.

image

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.

image

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.

image

 

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.

image

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.

image

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

image

 

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

All documents and related graphics, codes are provided "AS IS" without warranty of any kind.
Copyright © Shaun Ziyan Xu. This work is licensed under the Creative Commons License.

Comments

No comments posted yet.
Post A Comment
Title:
Name:
Email:
Comment:
Verification: