As round up of the new security-focused features coming in the latest update to Azure SQL Database I also wanted to cover another feature the team has been working on and one I really love, Dynamic Data Masking (DDM).

Introducing Dynamic Data Masking

Here is Microsoft’s description of this feature…

Dynamic data masking helps prevent unauthorized access to sensitive data by enabling customers to designate how much of the sensitive data to reveal with minimal impact on the application layer. 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.

We’ve all seen (or built) applications which does this sort of thing in the past. In the apps where I’ve done this before the masking is done client-side. The difference (or improvement) here is this all happens server side. Best of all, configuring DDM is really easy and can all be done in the portal.

Implementing DDM

First, open the Azure Portal, http://portal.azure.com and provision a new Azure SQL Database v12.

Create a new server to host the database and make sure you leave “Create a V12 server” checked as yes. The Preview for DDM is available on every Azure SQL DB price tier so feel free to select a Basic pricing tier if you’re following along. You can select defaults for the other settings, then click Create to provision a new database and server.

After the database has been provisioned we’ll create a new table called Customers and we’ll put some “sensitive” data in there we want to protect.

Next click on the “Open in Visual Studio” icon at the top of the database “blade” in the Azure Portal. This will open the Visual Studio blade. Before clicking on the big blue button we need to open the firewall so we can connect to the database. Click on the “Configure your firewall” link just below it.

From the Firewall Settings click on the “Add Client IP” then click Ok. Return to the Open in Visual Studio blade, then click on the big blue, Open in Visual Studio button.

When Visual Studio opens you’ll be prompted to connect to the database. Enter the credentials to the database you just created and click Connect. In the SQL Server Object Explorer, open the Database and the Tables folder. Then right click the Tables folder and select, “Create new Table”

From here create columns for Name, Email, SSN, CreditCard and Telephone. In the T-SQL window below change the table name to Customers. At the top left of the design window click Update. Then in the next dialog click “Update Database”.

Now let’s add some data. Next refresh the Tables folder in the SQL Server Object Explorer. Our new Customers table should now appear. Right click it and select “View Data”.

Next enter some sample data into each of the columns. Be sure you enter appropriate data that the format expected for this data.

With our data entered let’s go back to the portal and configure DDM for this table. Open the Azure Portal and return to the Database blade. From there click, All Settings, Dynamic Data Masking (Preview), then click Add Mask to open up the Add Masking Rule blade.

From here select the Customers table, then the Email column, then the Email mask. Finally click Save. Repeat these steps for the SSN and CreditCard columns. For the Telephone column, select the Custom string format. Enter 0 for the Exposed Prefix, then enter XXX-XXX- for the Padding String, and 4 for the Exposed Suffix. Click Save for the Add Masking Rule, then click Save again on the Dynamic Data Masking (Preview) blade to save all of the new masking rules.

Next, return to Visual Studio and close the Customers table if it is still open. Then right click the database and select Refresh and wait a moment while the connection is refreshed to the SQL Database.

Note: You may have noticed that the Customers table has disappeared. This is a bug in the SQL Server Object Explorer Visual Studio and applies to both Visual Studio 2013 as well as Visual Studio 2015 (but not SQL Server Management Studio). This should be fixed very soon and I’ll update this blog post when it is.

Even though we can’t see the table, it is still there. Right click on the database and select, New Query. In the query window type “select * from dbo.Customers” then click the green arrow to execute the query. Notice the data in the Results. It’s all been masked. Very cool. Best of all its ALL server-side. No changes to your app are required!!!

Some other handy things to know about DDM.

Privileged Logins

DDM has a Privileged Logins feature that allows you to add SQL logins for people who are excluded from getting masked results. Just add their logins (semi-colon delimited) to exclude them from getting masked results.

Down-level Clients

If you have applications that are built on .NET 4.0 or earlier you need to change your connection strings. You may also need to do this if you are using Node, PHP or Java. The new format is [mysqlserver].database.secure.windows.net. Notice the “secure” in the connection string now. If you are not using .NET 4.5 or higher read this article on DDM and down level clients.

Next Steps

If you’d like to learn more and get started with this feature you can start here, Get Started with Dynamic Data Masking. Note that this article is actively being updated so it may not quite match what I showed here but the information in this blog post is accurate.

That’s it. I hope you get some value out of this new feature.

It seems like the news is plastered with a never-ending rash of hacks against companies with hackers stealing their data. The range of companies is pretty diverse too. Ranging from “dating” websites like Ashley Madison all the way up to the US Government’s Office of Personnel Management. These days it doesn’t matter what kind of company you are or what country you live in. If you are storing data, it is valuable to somebody. The more sensitive the data, the more valuable.

As I peruse the list of hacks against these companies and governments there is something I notice. It appears they are, at best, only partially protecting their data once you get into their network. In other words, if you get in to their network and possibly past some other protection or rudimentary policies, you basically have access to their data.

If you’ve ever spent any time thinking or implementing security strategies you’ve heard of the Defense in Depth strategy. This strategy says that you should implement multiple, independent methods for securing your assets. As is usually the case, the ultimate assets is your data. For most companies, this is stored in one or more databases.

The Azure Data Platform Team has been doing some great work on bringing some of the security-focused features in SQL Server to Azure SQL Database v12. I am especially happy to see them release Transparent Data Encryption and Dynamic Data Masking into Preview for customers to start exploring (in fact I’ll write about Dynamic Data Masking in another blog post here soon)

Going through these new features in Azure SQL DB and as I read the ongoing deluge of stories of companies like Ashley Madison, the US Government, etc. the question I pondered was, if any of them had implemented encryption on their data at rest, would they have fared better from these attacks? While I can’t be absolutely sure, the likely answer is yes. The question you may be asking is whether you should implement it. The answer there is a resounding YES! I’ll show you how in the balance of this post.

Introducing Transparent Data Encryption

New to Azure SQL Database is a feature (now in Preview) called, Transparent Data Encryption (TDE). In Microsoft’s own words,

Transparent Data Encryption protects against the threat of malicious activity by performing real-time encryption and decryption of the database, associated backups, and transaction log files at rest without requiring changes to the application.

In other words, TDE encrypts the data in your database on the physical disk. Meaning that even if an intruder got past your perimeter and network security protocols and were able to access the physical storage for the database, they would still not have access to your databases because they are encrypted. In addition, TDE is implemented with no changes to your application’s code, or any other security measure you have already implemented, meaning this strategy is independent of any other Defense in Depth strategy, a key tenant to a well executed Defense in Depth effort.

How it works

The encryption itself is done using a symmetric key, AES-256. In addition the key is changed every 90 days which is a nice touch. But given it would take billions of years and nearly unlimited super-computing power to crack AES-256, not completely necessary. As your data is written to disk, Azure encrypts the data using Intel’s AES-NI hardware support. This helps ensure really good performance and also reduces the overhead in DTU’s needed to encrypt and decrypt the data. In fact, I can’t see a difference when I test query performance. Another benefit, and one that you get by using TDE in Azure versus on-premises SQL Server, is that you don’t have to manage the keys. Azure manages them for you.

How do I implement it?

Implementing a database with TDE is pretty simple and there are multiple ways you can enable TDE on a database using T-SQL, PowerShell or the Azure Portal. We’ll explore how to do it using the Azure Portal method in this post.

First, open the Azure Portal, http://portal.azure.com and provision a new Azure SQL Database v12.

Create a new server to host the database and make sure you leave “Create a V12 server) checked as yes. TDE is available on every Azure SQL DB price tier so feel free to select a Basic pricing tier if you’re following along. You can select defaults for the other settings, then click Create to provision a new database.

After the database has been provisioned you can restore an existing database into this one or create some tables with some data you want to encrypt if you want to hook it up to a client application and run it. This isn’t necessary though because you will not notice any difference in your applications.

Now that we have some data to protect, let’s go encrypt it. Return to our database blade in the Azure Portal. Next click on the All Settings link in the Essentials section of the blade.

From there, click on the Preview Terms, check the box and click Ok. Then enable Data encryption by clicking On, then click Save at the top of the blade. The Encryption status will then change to show a percentage complete to mark the progress as it encrypts your database.

After a few moments this will change and turn green. The database and all the data within it is now encrypted.

That’s it! Pretty easy. The best part is, you don’t have to modify your applications to enjoy having your data encrypted at rest in Azure. You can even write queries that do joins on encrypted columns.

So back to my question. Would this have helped Ashley Madison? Well since the feature is only just recently released to Preview then I would say no. They certainly should have had some encryption at rest strategy. TDE is a part of obtaining PCI-DSS compliance after all so you’d at least need it on the financial data. But if you’re an engineer for a company that stores their data in the cloud I would definitely take a look and explore this feature and if you manage on-premises database in SQL Server I would definitely recommend using this feature as it is too simple not to.