Super short version of my question:
For a MSSQL 2012 DB is it possible to update the current certificate with a new expiry date and new PW (or create new certificate with same name)? Or some other option I have not thought of that would be quick/easy to implement?
Now the details (there is probably simple answer I am just not able to find online):
We have a SQL 2012 DB and in lower environments (single server only for each) and our production which has failover server (so whatever my solution needs to include the ability to work with primary and failover server).
First two notes:
The key/certificate I know is outdated/not best way and we are working on redesiging our encryption.
We are working on upgrading this MS 2012 DB to 2019 soon, but not before I need to make this update/change.
The code for select (but we also do update/inserts) is used like this in SP’s (and also a trigger):
OPEN SYMMETRIC KEY KeyNameHere DECRYPTION BY CERTIFICATE CertificateNameHere Select DecryptByKey(EncryptedColumn) From dbo.TableName CLOSE SYMMETRIC KEY KeyNameHere;
I thought I had a solution (and using these steps testing it does work):
Backups existing certificate
Creates new certificate (and then back it up)
Updates all SP’s using the certificate to use a new certificate name in code like:
Existing: OPEN SYMMETRIC KEY KeyNameHere DECRYPTION BY CERTIFICATE CertificateNameHere
New: OPEN SYMMETRIC KEY KeyNameHere DECRYPTION BY CERTIFICATE NewCertificateNameHere
- Drops old certificate
As I said the above does work in testing on dummy DB but there are a LOT more SP’s than I was told and I can update them all to use new certificate name (as above) but I would prefer to find a way to not have to update all these SP’s code, but I can if needed.
Key question/concern is around the certificate in production (primary and failover servers) and what/how to ensure the update is completed on both and will work if/when a failover occurs.
If there is anything else I am missing or not considering please let me know.