Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Azure SQL doesn't support many of the encryption features found in SQL Server (Table and Column encryption). We need to store some sensitive information that needs to be encrypted and we've rolled our own CryptoEngine helper code wrapped around AesCryptoServiceProvider to encrypt/decrypt entity members in the database like:

// Person is a DbSet derived object
...
Person.CreditCard.Number = CryptoEngine.Encrypt(cc.number);
...
AppDbContext.SaveChanges()

This solves the immediate issue (no cleartext in db) but poses other problems like

  1. Key rotation (we have to roll our own code for this, walking through the db converting old cipher text into new cipher text)
  2. metadata mapping of which tables and which columns are encrypted. This is simple when it's just couple of columns (send an email to all devs/document) but that quickly gets out of hand ...

So, what is the best practice for doing application level encryption into a database that doesn't support encryption? In particular, what is a good design to solve the above two bullet points? If you had specific schema additions would love it if you could give details ("Have a NVARCHAR(max) column to store the cipher metadata as JSON" or a SQL script/commands).

If someone would like to recommend a library, I'd be happy to stay away from "DIY" too.

Before going too deep - I assume there isn't any way I can add encryption support to Azure by creating a stored procedure, right?

share|improve this question
add comment

1 Answer

I think that your problem can be solved with the MS framework called "Trust Services". Buck Woody explains in this article how you can use it to solve problems of encrypted data with SQL Azure. From his article, a short description about how to use Trust Services:

"With the new Trust Services service, the basic process is that you use a Portal to create a Trust Server using policies and other controls. You place a X.509 Certificate you create or procure in that server. Using the Software development Kit (SDK), the developer has access to an Application Layer Encryption Framework to set fields of data they want to encrypt. From there, the data can be stored in SQL Azure as a standard field – only it is encrypted before it ever arrives."

Further reference:

share|improve this answer
    
It's not a very good fit because Trust Services adds another unique server to the topology. If there is something we can bolt onto the (Web)Application server or the SQL server that would stick out less as a sore thumb. Also, I think it doesn't address #2 above in the question –  DeepSpace101 Nov 21 '12 at 22:36
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.