SSRS Blog

Been awhile since I had posted anything and what better way to restart this blog than with some Timeout Issues. Now this list is designed for SSRS 2005, but you can put a few of these tips to use in 2008. Consider these steps in the below order.


1) Set the HttpRuntime ExecutionTimeout value to a greater value (ie 3 hours) on the Report Server

How do you do this? Simple!

a.       Open the ReportServer’s Web.config file by going to Start -> Administrative Tools -> Internet Information Services.
b.      From there, expand Web Sites -> Default Web Site, and click on ReportServer. On the right-hand pane, right-click ‘Web.Config’ and select ‘Open’.
c.       Locate the HttpRuntime parameter. If it doesn’t exist, you will have to create it within the <System.Web> section.
d.      Set the executionTimeout value to ‘10800’ (3 hours) as shown below:


2) Increase the script timeout on the report server

   1. Go onto your Reporting Server and open up Internet Information Services; right-click on the ReportServer and select Properties.
   2. Go to the Options tab, and set the ASP Script timeout to 300 seconds as shown below.

3) Set the report to never time out on the server

   1. Open your web browser on the server and go to http://localhost/Reports
   2. Navigate to the Report location and click on the problem report(s).
   3. On the left-hand pane, click on Properties.
   4. In the ‘Report Execution Timeout’ click the ‘Do not timeout report execution’.
 

In the SSRS Reporting Environment, you may never encounter this error and you live a happy and easy life. We all know an SSRS report can easily ping your server to 100% CPU if you decide to export a 120 page report, to PDF,  with charts, etc. etc. You may get a complaint that performance suffers in another application while running the reports and you want to fix it, but do not know how.

Then one day you or someone at your firm has decided to do your SQL Server a favor and separate out SSRS from the SQL Server. This sounds like a great idea. You build a new SSRS box (lets call it SSRSPROD), run some performance optimizations (http://sqlcat.com/technicalnotes/archive/2009/01/14/reporting-services-performance-optimizations.aspx) and you are set.

You remote onto the new server, deploy all of your reports, open up IE on your server and check the reports; they run and life is good. You give the all clear to your users and suddenly your inbox is flooded with "My Reports do not work, I am getting a weird error" Testing it out for yourself on your computer, you suddenly see the dreaded "401 - Unauthorized Access Error". You go and check the data sources, you are using Windows Authentication, everything looks good. All of the user/group permissions on the reporting server are also correct. You remote onto the server, the reports run and once again they work. You then ask the Lord Google for an answer and see a ton of Kerberos discussions and different links. !@##$#@ you say to yourself. What did I do??? You have stumbled onto the Double-Hop Authentication Problem that so many of us have hit before in ASP.NET land or even SSRS.

Here are three quick and easy solutions, maybe not so quick and easy, but they work:

1) Maybe you just have a simple header error. This would happen if SSRSPROD is accessed through a different name like http://myreports/reports instead of http://ssrsprod/reports. The solution is here:
http://blogs.msdn.com/lukaszp/archive/2008/07/18/reporting-services-http-401-unauthorized-host-headers-require-your-attention.aspx

2) Convert all of your Data Sources to use SQL Accounts. This works because SQL connection parameters are passed through the authentication without a care. This has the added benefit of allowing your reports to be Subscription enabled in the future. HOWEVER, it has the major drawback of setting up the SQL Account as well as Security on the report. If this report should only have been viewed by John Doe, it is going to take some effort to make sure that SQL Account has the same permissions as JD. It may be tempting, but DO NOT GIVE YOUR NEW ACCOUNT DBO privileges. You will thank me after your next security audit.

3) Setup Authentication Delegation, Pass-through, whatever you like to call it. Mohammed Jeelani has a nice start from his blog:
http://blogs.msdn.com/mjeelani/archive/2004/12/07/275921.aspx
Then you can look at Microsoft's delegation articles:
http://technet.microsoft.com/en-us/library/cc739740%28WS.10%29.aspx
Finally, when you feel you have some more background on this, click these two links to solve your problem IF you are using Kerberos:
http://msdn.microsoft.com/en-us/library/cc281253.aspx#proxyfirewallRSWindowsNegotiate
http://msdn.microsoft.com/en-us/library/cc281382.aspx

Please post any comments!
Thanks

In my experience with SSRS, I have noticed that there are two types of report developers:

1) SQL Developer that naturally flowed into SSRS, uses Stored Procedures for database development and has a very solid background for development.

2) Developers from other categories that somehow fell into SSRS.

I come from the second school but I have an endless desire for learning about technology and have been fortunate enough to work with some really intelligent developers.

Which brings me to one of the more common issues in SSRS - Passing multi-value parameter into a stored procedure.
Munish Batel does an excellent job of explaining the issue here:
http://munishbansal.wordpress.com/2008/12/29/passing-multi-value-parameter-in-stored-procedure-ssrs-report/

However, I am going to go into an even crazier amount of detail and provide some tips and things to remember.

1) Know your stored procedure. When you are getting into the realm of passing of multi-value parameter into a stored procedure, make sure you know the total size of the parameter in your SP as well as the total number of choices you can have in the multi-value (MV) parameter.

• For example - we had a developer setup a MV parameter that could have up to 10,000 items checked (!!!) in the list. In the stored procedure, the parameter definition was varchar(50). You can see where this would go wrong.

2) If you are using the solution posted above, it is important you understand you will need to code a UDF function to split up the parameter - Split in VB land. This URL does a good job of explaining that as well:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

I am partial to this function:

-- 7998 bytes max (but faster)
   CREATE FUNCTION dbo.udf_Split (@param varchar(7998)) RETURNS TABLE AS
   RETURN(SELECT substring(',' + @param + ',', Number + 1,
                    charindex(',', ',' + @param + ',', Number + 1) - Number - 1)
                 AS Value
          FROM   Numbers
          WHERE  Number <= len(',' + @param + ',') - 1


            AND  substring(',' + @param + ',', Number, 1) = ',')