SQL Reporting Services Gotcha

Article
02/22/2009

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)