Design practice for securing data inside Azure SQL
- by Sid
Update: I'm looking for a specific design practice as we try to build-our-own database encryption.
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 using AesCryptoServiceProvider to encrypt/decrypt data to/from the database. This solves the immediate issue (no cleartext in db) but poses other problems like
Key rotation (we have to roll our own code for this, walking through the db converting old cipher text into new cipher text)
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?
