Cloud sqlazure

Published on March 1st, 2015 | by Warner Chaves

2

Security in Azure SQL Database: Dynamic Data Masking

The good thing about working with cloud platforms is that the development teams can add new features and release them more frequently than the major release of on-premise products. For example, the Azure SQL database has just released a new V12 version that has features that are NOT YET on the on-premise version of SQL Server. This is very exciting because it also gives us a glimpse of what the future of the on-premise SQL Server might hold.

Today I’m going to do an overview of one of these features. This one is focused 100% on security and it is Dynamic Data Masking.

What is it?

It’s a security policy tool that allows an administrator to specify masking of specific fields in the database for end users. You enable it on the database, connect through a secure endpoint and after that the values will be masked transparently in the results according to the policy.

This feature is available on ALL TIERS of Azure SQL Database: Basic, Standard and Premium.

When should you use it?

For example, this is very useful for displaying sensitive information like credit card numbers, social security numbers, addresses, etc. Many times the application will do the masking but then it also means the data has been transmitted in clear text until it reached the application. With Dynamic Data Masking, the real data stays in the database and anything transmitted is already masked.

Let’s see an example!

First pre-requisite is that this feature works with the V12 release of Azure SQL. So your server must be in this version to activate this feature at all.

If your server is on the V12 version then here’s one way to get to the feature in the new portal:

Browse – SQL databases – Pick the database – Dynamic Data Masking (preview)

In this example I have a database called ‘LastFmDataSet and here’s the option:

Capture1

Now once you go into the Dynamic Data Masking these are the settings you can activate:

Enabled / Disabled: obviously you’ll have to Enable the feature if you want to use it.

Connection strings: clicking this will open a blade where you can see the Security Enabled connection strings that you need to use to connect to the database once you activate Dynamic Data Masking.

Security Enabled Access: in a production environment you have to set this to be REQUIRED so that no one can circumvent the masking.

– Privileged Logins: these are server logins that have privileges to see the real data and bypass the mask policy.

Here’s a screenshot on how it looks:

Capture2

Make sure to press Save and once Dynamic Data Masking is activated you can add a specific mask to the policy.

These are the fields to configure:

Mask by Table and Column: if you pick Table and Column then you need to specify the specific Table and Column values that will get the mask applied.

Mask by Alias: if you select Alias then you need to specify an Alias name and any query where a field has that name or alias will get the mask applied.

– Select how to mask: with this setting you can specify what is the format of the mask you want to apply. There are different formats of masks and rules depending on what the source data looks like and how much information you want to keep in the masked value.

See the Microsoft documentation for a full explanation of the different masking formats.

Here’s a screenshot of an example policy I set on my database. I’m masking the table ArtistRates and the column Rate. So for example, users of my application won’t be able to see the rate of a specific artist, only the application administrator will. I have specified the Default value masking format which will replace any numeric value with a big fat 0.

Capture3

 

After hitting Save, the feature is active and we can test it out connecting with a regular user and a privileged login. Don’t forget that you need to use the secure connection string to connect to the database and get the masking policy to take effect.

For example, here’s a side by side screenshot of a query done against the table in my database. Note how the appAdmin user has full access to the values while the appUser user gets all the values as ZERO.

Capture4

Final thoughts

Like I mentioned at the beginning of the post, all these new features released cloud-first are very exciting. I invite everybody to fire up an Azure subscription (or get a trial one) and start to get familiar with these new features that are not yet on the on-premise SQL Server. Not only will you be able to start seeing the advantages of the cloud service but it will also help you be ready when these features hit the on-premises version of the products.

As usual, any comments or questions, write them down below!

Tags: , , , ,


About the Author

SQL Server Certified Master and MVP. DBA for MS technologies for over 10 years.



2 Responses to Security in Azure SQL Database: Dynamic Data Masking

  1. amit.cs03@gmail.com' amit says:

    Hi Warner…I tried this but the data does not get masked. I’m trying with SSMS. How do I use the secured conn string using SSMS?

    • Warner Chaves says:

      Hi Amit! You need to use the tcp address on SSMS from the secure connection string, you can get it from the Azure portal like I show on the 2nd screenshot.

      In general, the normal Azure db urls are usually servername.database.windows.net and the secure one is servername.database.secure.windows.net.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Back to Top ↑
  • Sign Up For Our Newsletter

    Stay up to date on the latest from SQL Server products and Features.

    Sign Up Now

    Sign up for mailing list and receive new posts directly to your inbox.

    We promise never to share or sell any of your personal information.

  • Recent Posts

  • Categories

  • Archives

  • Tags

  • Topics

  • Subscribe to our RSS Feed


    sqlturbo.com A SQL Server Web Blog
    Canada, Ontario, Ottawa.
    http://sqlturbo.com

    The Authors