Share via


SQL Reporting Services Gotcha

I uncovered a situation with SQL Server 2005 Reporting Services that I did not encounter before and it is worth mentioning because it follows the premise of - verify SQL Reporting Services first before installing Operations Manager 2007 Reporting. 

Background:

Windows Server 2008 x64 hosting SQL Server 2005 Enterprise Edition x64 w/SP2 configured with two instances.  One instance is dedicated to the Operations Manager operational database (i.e. OperationsManager) and the second instance is dedicate to SSRS, Operations Manager data warehouse (i.e. OperationsManagerDW), and Operations Manager Reporting Services.  The default instance is not installed in this scenario.  All services are configured to run with a domain user account that has local administrative rights.  SSRS was installed with default settings during setup of the second instance of SQL Server.

Scenario:

When attempting to access SQL Report Manager - https://localhost/Reports with the browser to verify SSRS was functioning correctly, the browser would never render the home page of SSRS and it would eventually time out.   No error messages were displayed in the Application Event Log and the configuration settings were verified to be correct in SQL Reporting Services Configuration Tool.  My initial thoughts were that this was a possible permissions issue, however further investigation showed that not to be the case.  I started to review the logs that are written by SSRS under Program Files\Microsoft SQL Server\MSSQL.<x>\Reporting Services\LogFiles and saw some errors in the ReportServerWebApp_.log file, and thought based on those entries it was firewall or IIS configuration related.  However, those ideas were put to bed after doing some additional troubleshooting.  

Some of the errors in the log: 

System.Net.WebException: The underlying connection was closed: An unexpected error occurred on a receive. ---> System.IO.IOException: Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. ---> System.Net.Sockets.SocketException: An existing connection was forcibly closed by the remote host

at System.Net.Sockets.Socket.Receive(Byte[] buffer, Int32 offset, Int32 size, SocketFlags socketFlags)

at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)

--- End of inner exception stack trace ---

at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)

at System.Net.PooledStream.Read(Byte[] buffer, Int32 offset, Int32 size)

at System.Net.Connection.SyncRead(HttpWebRequest request, Boolean userRetrievedStream, Boolean probeRead)

--- End of inner exception stack trace ---

at Microsoft.SqlServer.ReportingServices2005.RSConnection.GetSecureMethods()

at Microsoft.ReportingServices.UI.Global.RSWebServiceWrapper.GetSecureMethods()

at Microsoft.SqlServer.ReportingServices2005.RSConnection.IsSecureMethod(String methodname)

at Microsoft.SqlServer.ReportingServices2005.RSConnection.ValidateConnection()

at Microsoft.ReportingServices.UI.Global.SecureAllAPI()

at Microsoft.ReportingServices.UI.ReportingPage.EnsureHttpsLevel(HttpsLevel level)

at Microsoft.ReportingServices.UI.ReportingPage.ReportingPage_Init(Object sender, EventArgs args)

at System.EventHandler.Invoke(Object sender, EventArgs e)

at System.Web.UI.Control.OnInit(EventArgs e)

at System.Web.UI.Page.OnInit(EventArgs e)

at System.Web.UI.Control.InitRecursive(Control namingContainer)

at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

w3wp!ui!a!2/11/2009-10:40:42:: e ERROR: Exception in ShowErrorPage: System.Threading.ThreadAbortException: Thread was being aborted.

at System.Threading.Thread.AbortInternal()

at System.Threading.Thread.Abort(Object stateInfo)

at System.Web.HttpResponse.End()

at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg) at at System.Threading.Thread.AbortInternal()

at System.Threading.Thread.Abort(Object stateInfo)

at System.Web.HttpResponse.End()

at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg)

w3wp!ui!9!2/11/2009-10:40:42:: e ERROR: Exception in ShowErrorPage: System.Threading.ThreadAbortException: Thread was being aborted.

at System.Threading.Thread.AbortInternal()

at System.Threading.Thread.Abort(Object stateInfo)

at System.Web.HttpResponse.End()

at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg) at at System.Threading.Thread.AbortInternal()

at System.Threading.Thread.Abort(Object stateInfo)

at System.Web.HttpResponse.End()

at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg)

w3wp!ui!6!2/11/2009-10:40:44:: e ERROR: Exception in ShowErrorPage: System.Threading.ThreadAbortException: Thread was being aborted.

at System.Threading.Thread.AbortInternal()

at System.Threading.Thread.Abort(Object stateInfo)

at System.Web.HttpResponse.End()

at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg) at at System.Threading.Thread.AbortInternal()

at System.Threading.Thread.Abort(Object stateInfo)

at System.Web.HttpResponse.End()

at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg)

With time being of the essence and I needed to focus on other critical tasks, I suggested that the customer open a case with CSS and they work through it with them. 

Solution:

The RSWebApplication.config configuration file for Report Manager was modifed, which is located in the following folder - SQLInstall\MSSQL.X\Reporting Services\ReportManager.  By default, Reporting Services is configured to reference the IIS virtual directory "ReportServer" as noted in the tag - ReportServerVirtualDirectory. The value was removed from that tag and instead, the URL for Report Manager was entered for the tag - ReportServerUrl.  Once the SQL Reporting Services service was restarted, accessing Report Manager from the browser was attempted and was successful. 

In retrospect, I should have tried the recommendations in this KB Article - 922656, which I just came across as I was writing this blog entry (funny because I did not get a hit for it when I was originally searching for possible solutions).  As Homer Simpson would have said, "How convenient."