How to do application-level data encryption in Azure SQL with ASP.NET?

4
votes

A couple of other answers (for instance: How to encrypt data in SQL Azure?) tell that the normal data encryption mechanisms of SQL Server (transparent data encryption, asymmetric keys, symmetric keys, etc.) are not supported in Azure SQL, thus an application-level approach has to be used.

Our only requirement is that some of the data should not be stored in plain text, but should be encrypted in the database (and it should be hard to decrypt by an attacker, of course). What is the best way to do that? Which encryption scheme should be used? I have googled quite a lot but couldn't find any definite answer.

Should a symmetric or asymmetric encryption method be used? How should we store the keys needed to encrypt and decrypt the data?

1
c#asp.netazureencryptionazure-sql-database
Although a good question that deserves its attention, it is primarily opinion based, thus not fitting for StackOverflow. - astaykov
@MarkVincze What route did you end up taking? - karol
@karol We only needed encryption for a handful of columns, so we ended up encrypting them manually in C# code with a symmetric encryption algorithm. And on the data classes we created some properties which did the encryption/decryption in their getters and setters. (And in the DB table we used varbinary as the data type for these columns.) - Mark Vincze

1 Answers

1
votes

OK - so while certain elements of this answer can be construed as opinion - there's certainly something specific that can be contributed to this...

[FWIW: we're currently going through this process - so I've kinda walked part of this path]

For both of your questions, I would recommend reading the Jericho Forum Commandments on de-perimeterisation and cloud computing. In addition, the Information Commissioner's Office guilelines on Cloud Computing are indicative in general (yes, I'm from the UK).

From this you'll need to ensure that the encryption mechanism at the application level, i.e. certificate, is not held with the data or within the application (i.e. like an embedded resource). Storing the key with the data (even in Azure BlobStorage) is a kin to taping the key to the actual door. You can use other cloud services file storage or use a ready built solution like Entrust (disclaimer: I don't work for them and there are others). The real goal is that should your data be comprised, you'll have control over the decryption mechanism and thus control of the data.

The choice of symmetric or asymmetric encryption is one of choice - but the ability for certificates to expire is a major advantage is my eyes...