I’m working with a SQL Server 2012 instance that had a service master key, other secondary keys, and a SSRS key. The instance was uninstalled and reinstalled with new service accounts, and the master database was restored. No encryption keys were backed up. The original master key, now inaccessible, was left as is and the SSRS key was regenerated and backed up.
Now we need to open or regenerate the master key for SSIS use. If the SQL engine service is reset to the original login account, can the master key be opened? If so, via backup with a newly created password? If not, and the master key is regenerated, can the SSRS key be restored using its backup?
My google-fu is failing me and I want to accomplish this with the least amount of loss possible. Any pointers or assistance is greatly appreciated.
Best Answer
For anyone who comes across this and has a similar issue...
I was able to successfully change the SQL Server Service login to the original account used when the service master key was created, then successfully back up the key. Therefore I did not need to regenerate the service master key and restore the SSRS key. I still don't have an answer - if the saved SSRS key could have been restored following the service master key regeneration.
If anyone learns the answer to that - when you have backups of secondary keys, but not the service master key - will you have to regenerate the secondary keys also, or be able to use your backups.... please post the answer here for future reference.