I took a call from a customer earlier this week complaining that they were unable execute ANY of their SSRS reports. Replicating the issue gave me the ever helpful (not) generic error message below:
Both of the Web Servers running the SSRS Service Application were available, as were the SQL instances where the Content and Report Databases resided. This error was also applicable against multiple reports which were all using different data connections and data sources.
The SSRS environment, configured in SharePoint Integrated mode (SQL 2012), is leveraging Kerberos Constrained Delegation and thus, relies on the ‘Claims to Windows Token’ Service. Again, this service was available, I knew as much as Performance Point (which also uses Kerberos Constrained Delegation) was unaffected
So where was the bottleneck?
I have seen SSRS issues previously where for no apparent reason, the underlying data connection had somehow managed to ‘disable itself. In fact, this is something I have raised with Microsoft via Premier Support, although as it was totally intermittent and we were unable to replicate the issue, scoping the call was pretty much a non-starter.
On this occasion however, when I tried to open any SharePoint SSRS data connection I received the following error:
The correlation ID in question was linked to exception below in the ULS:
Notified the load balancer and raising RecoverableException for exception: System.ServiceModel.ServerTooBusyException: The HTTP service located at http://sharepointserver1:32843/a897fc88a99340e3b177754c4216ea4d/ReportingWebService.svc is too busy. —> System.Net.WebException: The remote server returned an error: (503) Server Unavailable.
This error highlighted that the failing component was directly applicable to the SSRS layer. The culprit was the SSRS application pool(s) which were stopped.
However, upon restarting the app pool, the issue immediately returned once I tried to open any data connection. The problem lay with the identity of the application pool, namely its domain account identity.
A quick dive into Active Directory showed that the password for the SSRS domain/service account had a password which had indeed expired. Doh! Basics! Resetting this via the ‘Managed Accounts’ setting in SharePoint Central Administration meant we was up and running!
Lesson to be learnt: Always ensure your service accounts have the attribute ‘Password Never Expires’ associated to them…..