Tag: SSRS

Event id 133 from source report server cannot be found

<<Previous

This is about a fix for the error “Event id 133 from source report server cannot be found”. This is in continuation of SQL Server Reporting Services Not Starting.

If you have landed here directly and you are getting the exact error in the Application event log, you are in right place. In  summary I had explained the troubleshooting steps and showed the exact error message in the first article.

Further troubleshooting steps:

Once I had checked the Application Event log, next I checked the Reporting Services log. The location of the log files is your SSRS install directory. In case you have observed, the error in the application event log contains the install directory:

event id 133 from source report server cannot be found
Application Event Log Showing SSRS Install Directory

Hence you can find the SSRS logs in the following location: Considering the above example.
“C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\LogFiles”

Looked at the latest log file and could find the following error message:

Event id 133 from source report server cannot be found
SSRS Error log

The error indicates some issues with the SSRS Config file. Refer the first screen shot in this article to find the path of the file.

C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\RSReportServer.config”

The most compelling evidence is the following part  in the error message:

“configmanager!DefaultDomain!20!03/27/2020-04:21:32:: e ERROR: Error loading configuration file: The ‘SemanticQuery’ start tag on line 220 does not match the end tag of ‘Extension’. Line 251, position 6.”

With this in mind I opened the config file in Visual Studio and searched for “SemanticQuery” and  “Extension”.

Note: Please create a copy of the original RSReportServer.config file before making any changes.

Visual Studio View of RSReportServer.config file:
Event id 133 from source report server cannot be found
Visual Studio View of RSReportServer.config file

The arrow shows the <SemanticQuery> block. If you have observed, once I put the cursor in the </Extension> it showed the dialog box saying “Expecting end tag </SemanticQuery>.

The next step is to observe the difference of <Extension> blocks between “SemanticQuery” block. If you can see the red underlines does not have the ending ‘/’ except the one (second last red circle).

Where as the last <Extension> before the ending </SemanticQuery> has ‘/’. I removed the ‘/’ from the “Extension” block which was expecting end tag </SemanticQuery> and saved it.

Bingo! that worked and Reporting Services came online. This is how I fixed it, there may be multiple issues which you may need to fix.

If you have faced any other issues let me know by leaving your comments below.

{ 2 Comments }

SSRS Encryption Key Backup Information

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:

Image showing the keys table outputAs 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:

Image showing output of backup_device view
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:

Image showing the machine key path

Initiated Encryption Key backup and checked in the process monitor:

Image showing the key backup path in process monitorI 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\
Image of process monitor which does not have the Machine key creation information 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)

{ 1 Comment }