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.