Encrypting a SQL Azure Database to comply with the EU data protection laws

Back in 2014, Microsoft’s president and chief legal officer Brad Smith wrote a note in the company’s blog (you can read it here: http://blogs.microsoft.com/blog/2014/04/10/privacy-authorities-across-europe-approve-microsofts-cloud-commitments/) stating that Azure was the only cloud provider meeting the renewed data protection regulations of the European Union. This award stemmed from policies that were already in place and some that Microsoft committed to implementing in the future.

It has to be noted that, by “data protection”, one does not refer only to possible hackers stealing customer data, but also, as Microsoft says, Protecting customer data from government snooping (read here: http://blogs.microsoft.com/blog/2013/12/04/protecting-customer-data-from-government-snooping/).

The European Commission, on October 15, 2015, ruled that the “Safe Harbor” decision of year 2000 (which affirmed that data were by definition protected when exchanged between EU countries and the US) is invalid. This new ruling followed a complaint of an Austrian Facebook user who affirms the company does not protect his data from the US authorities: http://curia.europa.eu/jcms/upload/docs/application/pdf/2015-10/cp150117en.pdf.

The data protection is thusly interpreted by Amazon as regards their very widely used Web Services cloud: https://aws.amazon.com/compliance/eu-data-protection/

There are also a lot of additional cloud service providers that have addressed the EU’s regulations; here is a good white paper by Cloudera: https://www.cloudera.com/content/dam/cloudera/Resources/PDF/solution-briefs/eu-data-protection-directive-compliance-solution.pdf

Here is how Rackspace comments the October 15th ruling http://blog.rackspace.com/eu-ruling-on-safe-harbor-rackspace-stands-prepared/

If your company is in a European country and you want to use cloud storage, chances are that you may want to ask your cloud provider to keep your data in the EU datacenters. Most providers allow you to choose where your database is located and replicated.

However, this is not enough. One of the requirements of data protection is encryption. Customer data should be encrypted not only when it leaves the EU for back up and geo-replication: you must assure a certain level of security if you allow your customers store personal data in a database.

Starting from October, 2015, if you have a SQL Azure database, you can take advantage of the transparent data encryption. What does it mean? It means your customer data is encrypted with a server-level cryptographic key but you don’t have to change your SQL queries.

I will try to show now how simple this is. I will replicate the same info you find here: https://msdn.microsoft.com/library/dn948096, but with a real-case scenario.

Before encrypting: Back up the DB

SQL Azure DBs are automatically backed up (frequency is set by you: watch out for Azure bandwidth costs!) but it is a good practice to back up your data before any important DDL operation. You can backup your DB to an Azure container.

To do so, from the Azure portal choose “SQL Server”, then select  the server that contains the DB you want to back up before encrypting.

Then, choose the “export” feature.

Exporting a SQL Azure DB to an Azure container

Exporting a SQL Azure DB to an Azure container


You have to choose the Azure “blob” where your backup file (.bacpac) will be stored. You also need to provide your server’s username and password (by server, I mean a DB server: being an “as a service” DB, actually, it is not a real server).


Configuring an Azure container to export a SQL Azure DB

Configuring an Azure container to export a SQL Azure DB


Encrypt the DB

The cool DDL command to encrypt the DB is:


If you are not cool and don’t like writing command lines (I absolutely don’t), you can achieve the same result via the portal, (see screenshots below):

  1. Select the Server
  2. Select the DB
  3. Choose “all settings”
  4. “Transparent data encryption”
  5. “ON”
  6. “Save”

7. Wait for some seconds (depending on the size of the DB, it could be also minutes)
8. You are done.

Keep on querying

Encryption is totally transparent. Keep on querying your DB!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s