I am looking for an alternative to symmetric key encryption to savely store sensitive data in a Microsoft SQL database. The reason for this is a few days ago I had an error during the night (at 3 am) where the database responded my status call, which is used for health checks of the backend, with an error
A severe error occurred on the current command. The results, if any, should be discarded.
(The call I am using for health check is only calling my rest api - going through the web service to the database, does a select count(*) from Member and returns the count.)
After that error every api call which used sensitive data from the database returned
Please create a master key in the database or open the master key in the session before performing this operation.
My monitor service said that the backend was up again after 2 minutes automatically but the master key was not working anymore. I fixed it with the following command
open master key decryption by password = 'password'
alter master key add encryption by service master key
the morning after but in the meantime the backend was not working correctly. So the failover didn't really did its job (because I had to do something manually to get everything working again).
So I am trying to achieve to store sensitive data easily in the database (must be able to decrypt it again) and to have a working failover without doing anything manually too.
Thanks for your input!
What i think I'm reading is that you have some sort of HA technology in play (e.g. availability groups). If that's the case, care needs to be taken to ensure that both sides of the topology can open the database master key.
Fortunately, it's fairly easy to do that. You can backup and restore service master keys (SMK). So, you'll backup the SMK from the secondary node and restore it to the primary node. SQL Server will decrypt anything currently encrypted with the old key and re-encrypt with the new.