Protecting Sensitive Data at Rest in SQL Azure

“Cloud computing is a model for enabling ubiquitous, convenient, on-demand network access to a shared pool of configurable computing resources (e.g., networks, servers, storage, applications, and services) that can be rapidly provisioned and released with minimal management effort or service provider interaction. This cloud model promotes availability and is composed of five essential characteristics, three service models, and four deployment models.”  – The NIST Definition of Cloud Computing (publication 800-145) U.S. Department of Commerce National Institute of Standards and Technology (NIST)

The five characteristics according to  NIST:

  1. On-demand self-service
  2. Broad network access
  3. Resource pooling
  4. Rapid elasticity
  5. Measured Service

The 3 service models:

  1. Software as a Service (SaaS)
  2. Platform as a Service (PaaS)
  3. Infrastructure as a Service (IaaS)

The 4 deployment models

  1. Private
  2. Community
  3. Public
  4. Hybrid

In December 2011 NIST also published  “Guidelines on Security and Privacy in Public Cloud Computing” (publication 800-144).  5 broad guidelines are expanded in some detail in this document.

  1. Carefully plan the security and privacy aspects of cloud computing solutions before
    engaging them.
  2. Understand the public cloud  computing  environment offered by the  cloud provider.
  3. Ensure that  a cloud computing solution satisfies  organizational security and privacy requirements.
  4. Ensure that the client-side computing environment meets organizational security and privacy requirements for cloud computing.
  5. Maintain accountability over the privacy and security of data  and applications implemented and deployed in public cloud computing environment.

Even at this highest level, the enormous distance that must be bridged to bring SQL Azure into the organizational security strategy should be obvious.

It can’t be that bad. Can it? Well maybe… at issue is the compatibility of internally developed custom code based on long standing organizational security decisions and a dynamic resource on a public network. The disparity is huge. The risk is even bigger. Sounds likes an interface with some new avenues of exploit for the malicious user base to me. Most of the holes should be filled in  few years. Just in time for the clouds to clear.

Consider that SQL Azure:

  • only knows SQL Authentication

Microsoft has long discouraged SQL Authentication because it is, well…, less secure. From the Books Online topic Security Considerations for a SQL Server Installation the security best practice recommendation is unequivocal, “Require Windows Authentication for connections to SQL Server.”

The SQL Azure documentation’s Development: How-to Topics includes Guidelines for Connecting to SQL Azure Database and several detailed documents for connecting to SQL Azure from a short list of Microsoft selected client application platforms. There is also a fairly detailed Technet document about “SQL Azure Connection String Security“.  Another very important item for SQL Azure is the .NET Protected Configuration Provider used to encrypt the login and password in connection configuration files – e.i. web.config, app.config, etc – on the client. (see “SQL Azure Connection Security” for more on Protected Configurations including a custom provider when the SQL Azure client is Windows Azure.)

The biggest concern I have with SQL Authentication is shared logins (see below) The second most concerning issue is password attacks. I do not know if SQL Azure will deal with a brute force password attack. I threw bad password at the connection from SSMS 10 times. The connection failed every time but it let me retry with no lockout.

  • allows only 1 SQL Authenticated server level principal

not that there is much the server level principal needs to do:  backups, firewall configuration, create databases and manage logins. Probably won’t be more than… oh… say 6 or 8 people in a typical organization with the keys to the car. Anybody that thinks that that is secure absolutely must speak with me about keeping their money in my bank account.

  • provides 2 Fixed Server Roles proxied through user roles in the master database

“Like the securityadmin role for an on-premise instance of SQL Server, the loginmanager role in SQL Azure Database is required in order to create logins.”

“The SQL Azure Database dbmanager role is similar to the dbcreator role for an on-premise instance of SQL Server.”

(I notice a slightly more exhaustive table that calls out some SQL Azure vs SQL Server differences in Managing Databases and Logins in SQL Azure.)

  • requires firewall configuration

Before the first user, the server principal, can access a SQL Azure instance, that user must open the IP address of that user’s connection in the SQL Azure Firewall – and any necessary steps to open the path from the user side of the connection must be completed.

SQL Azure is available only through port 1433 and only by TCP. (FWIW, this is enough to make ODBC happy.)

The MicrosoftServices firewall rule was already in place on my SQL Azure instance before I signed is as the server principal. I am able to add and remove this rule, however I am not able to ascertain the consequences of either action. Also, the documentation tells me to add a second application rule, “Allow Windows Azure” to use my SQL Azure instance with my Windows Azure services.

select * from sys.firewall_rules exec sp_set_firewall_rule N’MicrosoftServices’, ‘’, ‘’

exec sp_delete_firewall_rule N’MicrosoftServices’

exec sp_set_firewall_rule N’Allow Windows Azure’, ‘’, ‘’

exec sp_delete_firewall_rule N’Allow Windows Azure’

  • does not support distributed transactions
  • cannot be used in SQL Server Replication

SQL Azure does provide Sync services.

  • does not allow impersonation or provide a local task scheduler or Service Broker

Impersonation by manipulating a scheduled task or handing work to a background process has long been an accepted way to circumvent security configuration for the on-premise SQL Server. Lesser privileged users to modify data in some table, the agent or broker picks up the data and executes a task restricted to higher privileged users: start a backup, add a login, TRUNCATE a table, etc. None of this will work with SQL Azure. More recently stored procedures could be marked WITH EXECUTE AS  to allow code to impersonate a more entitled user. This implicit sharing of database users will work Unfortunately, explicitly sharing database users will also  work…

  • backup is un-like a sunny SQL Server backup

In early releases of Azure you had to use CREATE DATABASE. The options were to create a database copy on the local SQL Azure instance or on another SQL Azure instance. You still must copy the database during backup but things are a bit better with the recently introduced the .BACPAC methods that allow the copy to target a Windows Azure virtual file and can leverage SSMS or the Database Import/Export Service. The Import/Export Service is also useful to restore the Azure database into a local SQL Instance.

Use the sys.dm_database_copies view to see the currently active copies… er… I mean backups. OK, I have copy of my database. But how do I back it up? And what does the restore look like?

A well defined strategy is needed and you don’t get many building blocks with SQL Azure. Watch for some 3rd party cloud backup-ware.

  • Limited Cryptographic support

No Certificate store, no support for the SQL Server’s Encryption Hierarchy, no Transparent Data Encryption (TDE),  no Cryptographic Providers,  no EKM, and no CLR integration. Direct cryptographic support in SQL Azure looks pretty much limited to HASHBYTES and VARBINARY storage for already encrypted data.

You do have to load a key in order to access the instance, so some chance that key is used to protect data at rest, but I do not know is this is so or how it works.

Look ma, no cryptography!

The state of SQL Azure cryptography may actually be good where it is. Waiting until data reaches the database to encrypt is too long anyway. The longer encryption is postponed the less certainty that the data is not compromised. Furthermore, the more user friendly the encryption – to wit TDE – the easier to circumvent intended data protection or discombobulate expected recover-ability.

Further, TDE encryption is not fully protective. TDE offers no protection from SQL injection, cross-site scripting or in-flight attacks like man-in-the-middle. Even a TDE restore to a development environment will still inappropriately expose sensitive data and would compromise the Database Encryption Key. As long as a user can get a connection to the database instance, the data is transparently available in unencrypted form. From the opposite view, loss of data due to a missing or forgotten key export or key export password only adds to the woes of a data center during a calamity. Perhaps better to risk a breach than to risk a mis-step due to the added complexity regardless of the database weather conditions? (I don’t really believe that but it is worth thinking about.)

From another perspective, threats to cloud hosted data are notably different than threats to local network hosted data; not that either scenario should leave you with any feeling other than the creeps.  I really don’t know how a malicious cloud user behaves. Are there spiders crawling around the underlying infrastructure? Are there additional SQL Injection/cross site scripting benefits or risks. I do not know. I have a suspicion the cloud providers’ are building risk profiles from the by now most established trails as you read.

My SQL Azure instance doesn’t know what a certificate or a database master key is so is not likely that the SaaS service model would expose TDE any time soon. More likely the encryption requirement, and ultimately privacy compliance from the cloud would demand the Platform-as-a-Service Paas or Infrastructure-as-a-service Iaas service model or perhaps SQL Azure will become able to store certificates? A reasonable common sense alternative would be to encrypt the data where it originates – usually that will be in the application – and unencrypt the data in the locally hosted or Windows Azure application – only where it is needed as clear text. Store the encrypted bytes in an appropriate VARBINARY typed column in SQL Server or SQL Azure. End of problem.

Compliance Standards such as FIPS 140-2, the US standard for encryption certification or PCI-DSS, the credit card industry standard for protecting sensitive data include explicit requirements for protecting sensitive data at rest. Unfortunately, the standards may be ineffective. Sony’s ongoing spring 2011 breach (is it unstoppable?) and the RSA breach lost sensitive data from apparently compliant systems. Due diligence is in order. Consideration of the standards is prudent, but ultimately protecting the data must be the objective.

T-SQL  HASHBYTES() is a deterministic cryptographic hash function built from a block cipher – a randomly generated symmetric key. HASHBYTES() performs a a one way encryption or hash on the passed string parameter. The value is not intended to be decrypted. Block Ciphers are useful for authentication, data comparison and searching by digest (the hashed byte array output that provides a meaningless and 16 or 20 bytes values – depending on the hashing algorithm used). Using HASHBYTES() could eliminate much application logic that instead now uses the sensitive data directly.

Without a doubt the movement of sensitive data to public networks will increase demand for developers adept in current cryptography practices. The Technet article “SQL Azure Connection Security” includes best practices for writing secure applications and secure connection strings that use SQL Azure. There is quite a lot of information to be found concerning Windows Azure cryptography. The MSDN’s Security Resources for Windows Azure is great place to begin to dig deeper as is the MSDN Windows Development Security topic.

This entry was posted in Encryption Hierarchies, Secure Data. Bookmark the permalink.

Leave a Reply

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

You are commenting using your 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 )

Google+ photo

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

Connecting to %s