Today I am going to discuss about an uncommon requirement and that is to find out SSRS Encryption Key backup information. In other words if there is any backup taken for the key and if yes then what is the location and backup date.
Now the question is very legitimate as they wanted to know if the reports can be restored in case situation demands. Not being a BI expert started my research from scratch.
Tried to query the “Keys” table in ReportServer database:
As you can see no information about location of keys or at least last backup information.
A wild guess to check “backup_devices” view but no luck as expected:
Could not find any information in registry keys as well.
As per discussion with my colleague I installed SSRS on my personal laptop and then started a Process Monitor trace right before backing up the encryption key. During the process, a RSA machine key is created in C:\Users\ReportServer\AppData\Roaming\Microsoft\Crypto\RSA\.
This was like the light at the end of the tunnel and decided to dig further to discover something which would make me feel like a champion 😉
The below screen shot shows the generated machine key and path:
Initiated Encryption Key backup and checked in the process monitor:
I could see that the path showing the key backup as shown in the above screen shot.
Next tried to search if any information regarding the machine key has been logged:
That is searching for the path C:\Users\ReportServer\AppData\Roaming\Microsoft\Crypto\RSA\
This “Cannot find string” message shattered my hope 🙁
To understand how the machine key got generated, I uninstalled SSRS and checked the same path and the key was still lying there. Did a reboot of the system but it was still there.
I manually deleted the key, reinstalled and configured SSRS and the key got created. So the machine key gets generated while configuring SSRS.
So my assumption:
Machine key gets generated while taking SSRS Encryption Key Backup was wrong.
Conclusion:
I have checked with many experts in different forums and the most fruitful one was https://www.sqlservercentral.com.
What we can conclude from all these is as of now SQL Server does not record the information of SSRS encryption key backup path. It make sense because the .SNK file could be moved to another location manually.
But I expect Microsoft to at least record the information about the successful backup just the way they record database backup information.
Work Around:
The only work around is to have a company policy of some sort on where to backup these keys. Like you may want to suggest to have a policy/documentation to do the backup every x months on x path.
Or use default location for the backup of the instance there are some things you could do using the registry.
And then use PowerShell to find the LastWriteTime of the .SNK file.
Here I have explained every way I tried and failed to find the required information. Sometimes it is important to know what all ways does not work so that you can think in other direction.
Well if you find the solution please let me know and that is why I request you to share this with your friends/network using the below social share icons.
You may also like to know about:
Myth around Remote Query Timeout Option
Delete Tempdb data file without restarting SQL Server
Fact about Dedicated Administrator Connection (DAC)
