September 5, 2023 at 1:37 pm
Hello!
I would like to have any feedback on using AKV to store and manage a column master key for Always Encrypted for on-premise applications only. Not consistent solution, regulatory issue, availability/connectivity issue, account setup difficulties? For several raisons, we don't want to use a local windows certificate store.
Thank you!
September 5, 2023 at 4:44 pm
Looking at the docs, it sounds like there should be no reason that this shouldn't work as long as AKV supports one of the methods that SQL Server supports.
That being said, Microsoft recommends the following (from https://learn.microsoft.com/en-us/sql/t-sql/statements/create-column-master-key-transact-sql?view=sql-server-ver16):
It is recommended you use tools, such as SQL Server Management Studio (SSMS) or PowerShell to manage column master keys. Such tools generate signatures (if you are using Always Encrypted with secure enclaves) and automatically issue CREATE COLUMN MASTER KEY statements to create column encryption key metadata objects.
What I would advise is to set it up on a test system and see if it works for you. I see no reason why you'd have any account setup difficulties or availability/connectivity issues. Regulatory issues depend on where you live so we can't really talk on that, and not sure what you mean by "not consistent solution". I mean, you would run some risks if you ever decided to scrap AKV for another solution as it may be painful to switch without data loss (for example). The ONLY availability/connectivity issues I can think of are if there is a bug in the AKV driver (current or future) and SQL can't decrypt the keys, or the keystore gets corrupted/lost then you may have some issues. But that would apply to any keystore tool you decide to use; and once the keystore is restored, you should be back up and running without issues.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
September 5, 2023 at 7:27 pm
Hi,
Thank you for replying! By "non consistent solution" I mean that all solution ( web, database,...) is hosted on premise infrastructure excepts an interaction with AKV for Always Encrypted (AE).
For the moment we won't use the secure enclave. The key could be pushed to AKV by HSM.
For data encryption we use built-in encryption functions but for new applications we consider to use AE feature.
Thank you!
September 5, 2023 at 9:58 pm
One thing I would be careful of is that using AKV means your SQL instance relies on a cloud service to decrypt its data. This is important for 2 big reasons and 1 smaller reason- first, if the network connection is interrupted (bad firewall config for example, but could be service restarts, internet dropped out, service interruption in Azure, etc), you lose the ability to decrypt the data. Second, you have to have some firewall ports open so your SQL instance can talk to the cloud. You can restrict this pretty well in your firewall to make it secure, but I prefer my SQL boxes to have no internet access. The third smaller issue is if Microsoft decides to change how AKV encrypts the data, it MAY be that older SQL instances are no longer supported and you may be required to do an upgrade. That last one is a smaller risk because Microsoft likely isn't going to be making any major changes to AKV as it would impact a lot of different systems and I'm sure it would upset a lot of admins. And even if they did decide to change it, they would give a lot of notice before changing it.
I suppose a forth issue is if someone hacks your Azure, they could get copies of your keys. Getting the keys could be complex like someone hacking Microsoft, or it could be a simple thing like a current admin has an easy to guess password and MFA turned off. It could be malicious intent by an admin, but that is really tricky to prevent.
Outside of SQL you also have the same problems. Anything that relies on AKV would need some form of consistent internet access to get the keys and you'd need firewall ports open to ensure that things keep running happily. One nice thing of having your keys on-site is you can keep things running without internet.
Just my 2 cents.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply