September 28, 2017 at 8:48 am
We are starting to utilize Always On Encryption, and use the Azure Key Vault. We have one system team that has run into an issue with an application that uses triggers extensively to move data around. A few of those triggers take action after examining the contents of a field that will now be encrypted. Obviously, that is problematic in TSQL.
One of the proposed solutions (untested so far) is to have the Trigger call a CLR function to decrypt the field, extract the necessary data, and pass it back to the trigger. I'm aware that
1) This might not work
2) It is going to make an already complex system more complex
3) There could be performance issues with this solution.
However, my biggest question right now is how big a security hole (if any) would be added by an architecture like that? I don't understand everything about Always Encrypted, but it seems like this is going against the idea of the encryption happening at the application level , and the database not knowing how to decrypt the data.
Thanks for any opinions or input!
October 10, 2017 at 9:10 am
Big hole. The idea with Always Encrypted is that the server is untrusted, the clients are. If you want to implement something, I'd suggest that you use an external application, not CLR (inside SQL process) that will be able to read the certificate and decrypt data. If you have this cert on the server, then you are compromising the security of AE. This needs to be on another system.
You may not care about the encryption in this way, which is fine. I'm not sure I'd want a trigger going to a CLR, exiting the SQL process, reading a cert, performing decryption, etc. That's a lot to ask of a process inside a trigger. I would suggest this is better handled by rearchitecting this to be a side process. If you see data change in an encrypted field (you can use a hash for a quick check), then queue up a process to run through the decrypt, check, move data process in an async fashion.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply