Troubleshooting SQL Server Reporting Services

In my last post Installing & Configuring SQL Server 2005 Reporting Services Grant O pointed out that I missed the troubleshooting section and same feedback came from my manager too; So I decided to add that section to the document.

But when I was writing the various things which can go wrong during reporting services installation, I realized that its better to note down other troubleshooting cases too. So I added all possible scenarios(I had faced and from my bookmarked links) and categorized them as per my understanding. Lets see whether its helpful to you all or not :)

Diagnosing reporting services issues

1. Reporting Services records information in a variety of log files. Use the following log files to discover information about server operations and report server applications that run on the local computer:

   · Report Server Execution Log

   · Report Server Service Trace Log

   · Report Server HTTP Log

   · Windows Application Log

   · Windows Performance logs

   · Setup log files

   Reporting Services Log Files - http://msdn.microsoft.com/en-us/library/ms157403.aspx

2. Check Reporting Services Trace Logs to find the issues and see detailed stack trace information; SSRS provides four trace log files, which are located at \Microsoft SQL Server\<SQL Server Instance>\Reporting Services\LogFiles
3. Enable Remote Errors (Reporting Services Configuration) so as to display additional information about error conditions that occur on remote servers –

   http://msdn.microsoft.com/en-us/library/aa337165.aspx

   http://www.kodyaz.com/articles/enable-remote-errors.aspx

4. Get more details about cause and resolution of Reporting Services Errors –

   http://msdn.microsoft.com/en-us/library/aa337312.aspx

   http://technet.microsoft.com/en-us/library/aa256058(sql.80).aspx

5. Get more information about report server events, recorded in the Microsoft Windows Application Log file

   Reporting Services Errors and Events –  http://msdn.microsoft.com/en-us/library/ms165307.aspx

6. Check this post for more help on how you can diagnose your problem –

   http://blogs.msdn.com/lukaszp/archive/2007/01/31/how-to-diagnose-issues-when-running-reports-in-the-report-server.aspx

Troubleshooting Reporting services installation –

1. Check the Setup Log files to find the cause -

   http://msdn.microsoft.akadns.net/en-us/library/ms143702.aspx

2. Make sure you have all SQL server service packs installed on your report server.

   SQL Server 2005 Service pack 2 - http://www.microsoft.com/downloads/details.aspx?FamilyId=d07219b2-1e23-49c8-8f0c-63fa18f26d3a&DisplayLang=en

3. Install latest Cumulative update package for SQL Server 2005 Service Pack 2 on your report server.

   The SQL Server 2005 builds that were released after SQL Server 2005 Service Pack 2 was released - http://support.microsoft.com/kb/937137/LN/

4. ASP.NET must be registered with Internet Information Services –
   1. Run aspnet_regiis -i from the %windir%\Microsoft.Net\Framework\v2.0.50110 folder.
   2. Run iisreset to restart IIS.

   ASP.NET IIS Registration Tool (Aspnet_regiis.exe) -  http://msdn.microsoft.com/en-us/library/k6h9cz8h(VS.80).aspx

5. Verify all services are running and features are enabled
   1. Click Start, point to All Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, click SQL Server Surface Area Configuration.
   2. In Surface Area Configuration for Services and Connections, verify that the Report Server Windows service is running. If the report server database is hosted on the local Database Engine instance, verify that the SQL Server (MSSQLSERVER) is also running.

      

   3. In Surface Area Configuration for Features, verify that Scheduled Events and Report Delivery is enabled. Verify that HTTP and Web Service Requests is enabled. Verify that Windows Integrated Security is enabled.

      

Troubleshooting Report Server Issues

1. Check Trace log for the Report Server Web service (i.e. \Microsoft SQL Server\<SQL Server Instance>\Reporting Services\LogFiles\ ReportServer_<timestamp>.log) to get more details.
2. Verify that the SQL Server (MSSQLSERVER) service is started. On the computer that hosts the instance of the Database Engine, click Start, click Administrative Tools, click Services, and scroll to SQL Server (MSSQLSERVER). If it is not started, right-click the service, select Properties, in Startup Type select Automatic, click Apply, click Start, and then click OK.
3. Make sure that Database Engine instance that hosts the report server database is configured for remote connections –

   http://technet.microsoft.com/en-us/library/ms365170(SQL.90).aspx

4. If Windows Firewall is turned on, make sure that the port report server is configured to use is not closed –

   http://technet.microsoft.com/en-us/library/bb934283(SQL.100).aspx

5. Verify that report server virtual directory properties are set correctly.
   1. Make sure that report server or Report Manager virtual directories are present and mapped to Microsoft .NET Framework version 2.0 or later
   2. Make sure proper authentication is configured for SSRS virtual directories in IIS(default is windows integrated security) –

       http://technet.microsoft.com/en-us/library/bb283249.aspx

      

   3. If you have reinstalled IIS 6.0 after SSRS was installed then you need to create SSRS virtual directories, use Reporting Services Configuration tool to recreate them.

      http://msdn.microsoft.com/en-us/library/ms181174.aspx

   4. You can reset the virtual directory properties to default by using Reporting Services Configuration tool to make sure correct configuration is there–

      

6. Verify that the URL you are specifying is correct for your deployment, if you installed Reporting Services as a named instance, the default virtual directory might include the instance name. To verify you can try browsing directly from IIS.
7. Make sure that required features are turned on in configuration files – http://technet.microsoft.com/en-us/library/bb677363(SQL.100).aspx

Troubleshooting Report Manager Issues

1. Check Trace log for the Report Manager (i.e. \Microsoft SQL Server\<SQL Server Instance>\Reporting Services\LogFiles\ ReportServerWebApp_<timestamp>.log) to get more details
2. On a new installation, only local administrators have sufficient permissions to work with content and settings so make sure user account from which you are trying to access report manager is having sufficient permissions –

   http://technet.microsoft.com/en-us/library/ms156034(SQL.100).aspx

3. Your report server must run in native mode. You cannot use Report Manager with a report server that is configured for SharePoint integrated mode -

   http://technet.microsoft.com/en-us/library/bb326407.aspx

Troubleshooting Issues related to Web application Integration

1. Enable impersonation in your Web application. Modify the Web.config file for your client app. Simply add the following line within the <system.web> configuration element to do this:

   <identity impersonate="true" />

2. If your reporting services and data are on different servers then you must configure your network to allow Kerberos delegation, so that you don’t face “double hop" issue with IIS.

   How to use Kerberos authentication in SQL Server - http://support.microsoft.com/kb/319723/en-us?spid=2855&sid=global

3. If you are using report viewer controls in your application then make sure that report viewer Redistributable is installed on your web server:

   Microsoft Report Viewer Redistributable 2005 SP1 (Full Installation) -

   http://www.microsoft.com/downloads/details.aspx?familyid=E7D661BA-DC95-4EB3-8916-3E31340DDC2C&displaylang=en

4. Check whether report viewer control is configured properly or not:

   Configuring ReportViewer for Asynchronous Rendering - http://msdn.microsoft.com/en-us/library/ms252090(VS.80).aspx

   SQL Server 2005 Reporting Services ReportViewer Control and IE7 –http://arcanecode.wordpress.com/2007/11/29/sql-server-2005-reporting-services-reportviewer-control-and-ie7/

Troubleshooting Issues in Report Processing

You can face session timeouts or some other processing errors when you have reports containing a large amount of data; there is no sure shot solution for this but there are a few workarounds for it as mentioned here :

Processing Large Reports - http://msdn.microsoft.com/en-us/library/ms159638.aspx

1. Session Timeout during execution - http://blogs.msdn.com/jgalla/archive/2006/10/11/session-timeout-during-execution.aspx
2. Out of memory exceptions, increase the memory used by report server –http://msdn.microsoft.com/en-us/library/ms156002.aspx

Troubleshooting Report Design and Layout Issues

1. Troubleshooting Report Problems – http://msdn.microsoft.com/en-us/library/ms156290.aspx

References:

• Troubleshooting Reporting Services – http://msdn.microsoft.com/en-us/library/ms159135.aspx