2

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.

Improve this question
1
  • Can you not just rebuild the datasources connections inside SSRS and backup again with a new secure pw or are those not known to you. If they are not known, could you query a backup of the reportserver database and obtain them?
    – jstexasdba
    Commented Mar 27, 2017 at 22:51

2 Answers 2

Reset to default
1

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.

Improve this answer
0

The SQL Server Service Master Key is encrypted using Windows Data Protection API. The Machine Key and the Windows master key of the service account are used to protect the service master key. The only way to open the service master key without having a backup is to run the service on the same machine with the same service account. Note, if you reset the password in Active Directory for the service account, a new master key is generated by Active Directory for the service account, rendering any SQL Server Service Master Key generated previous to the password reset invalid.

I wrote a short blog post that talks about this, and shows how to perform key and certificate backups to avoid this problem at SQLServerScience.com.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.