‘Double Hop’ issue from Remote Report Server using the ReportViewer control

HTTP 401.1 – Unauthorized: Logon Failed.
 
Whilst helping to develop an intranet application that used the SQL Server 2008 Reporting Services ReportViewer we came across the problem of authenticating users across servers. 
 

The Problem :

 

An intranet application requires support for authentication using a Windows Identity and this identity needs to be used to control access to reports . In a  scenario where the Web Server hosting the application resides on a different machine to the Report Server the authentication process is required to do a ‘Double Hop’ (Web Client -> Web Server -> Report Server) in order to access the remote resources (the reports) on the Report Server.

 

The ‘Double Hop’ issue to access these remote resources means that delegation is required.

 

Standard impersonate-level impersonation tokens that are usually created when you enable impersonation allow you to access local resources only. To be able to access remote network resources, you require a delegate-level token. To generate a delegate-level token when you impersonate, you need to use Kerberos authentication and your process account needs to be marked as trusted for delegation in Active Directory.

 

(Taken from http://msdn.microsoft.com/en-us/library/ms998358.aspx#paght000025_configuringwindowsauthentication)

 

The Investigation :

 

Having set up the necessary servers to simulate the client environment I researched numerous other solutions to the problem as this is  a common issue encountered by other web developers using the ReportViewer control from the web site to access a remote report server. All the research pointed to the issue of delegation and ensuring that the machines are trusted for delegation.

 

If you access this Web site from a browser while logged onto a different machine in the same domain and the Web site attempts to access network resources, you end up with a null session on the remote server and the resource access will fail. To access remote resources, you need delegation.

 

(Taken from the same article listed above)

 

Since delegate-level tokens when impersonating requires Kerberos authentication I came to the conclusion that the missing link in our server configuration was the ‘Trust for delegation’ setting. Having got the IT department to make the relevant changes to the servers in the AD and rebooted the machines our web application was able to display reports from within the application.

 

The Solution :

 

The solution comes in two parts;

     Internet Explorer security settings must be configured to enable Integrated Windows authentication. By default, Integrated Windows authentication is not enabled in Internet Explorer 6 (or above). To enable the browser to respond to a negotiate challenge and perform Kerberos authentication, select the Enable Integrated Windows Authentication check box in the Security section of the Advanced tab of the Internet Options menu, and then restart the browser. It is also advisable to make sure the server running the web application is added to the list of Intranet Sites in the security settings for the browser and that ‘Automatic Logon only in Intranet Zone’ is also set.

     If this server running IIS is a member of the domain but is not a domain controller, the computer must be trusted for delegation for Kerberos to work correctly. This information is taken from the following article – http://support.microsoft.com/kb/907272 .

 

Supporting Documents:

 

For information on what changes are required to the AD please see the following article – http://technet.microsoft.com/en-us/library/cc738491(WS.10).aspx

 

SQL Server 2008 And Dundas Charts

Using Dundas Charts with SQL Server 2008 Reorting Services
Looking at SQL Server 2008 and in particular Reporting Services I have been interested in the new functionality that has been added and things that have changed since 2005. In particular I wanted to know which bits of the functionality within Dundas Charts that Microsoft had included with 2008. Fi you want a quick summary of whats not included take a look at http://support.microsoft.com/kb/956028. Basically if you have any existing Dundas charts in SSRS 2005 that use code then the 2008 charts that are shipped with SSRS will not be transferable.
Since Dundas did not sell everything to Microsoft the solution is to use Dundas’ own 2008 version of their charts. You can download evaluation edition, Version 2.2 is currently available at the time of writing and can be installed with SSRS 2008. Just to make things interesting, when I tried to install this version on a Windows 2008 Server running SQL Server 2008 the Wise Installation would hang whilst determining the disk space. To try and solve the problem I ran the installation again with logging using the following command line “C:JunkDCR22SQL10EVAL.exe” /L*v “C:JunkInstallLog.txt” /I and this time the installtion completed without any problems. I didnt even need to review the log file.
Having got the control installed successfully my next task is to import a SSRS 2005 chart that uses code and see if it runs with the next version on SSRS2008. Stay tuned for more details….

Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException

The permissions granted to user ‘DOMAINusername’ are insufficient for performing this operation. (rsAccessDenied) .
 
This error had been bugging me for a few hours and it occurs in numerous areas of SSRS so tracking down the problem was a pain. Here was my specific problem;
 
  • I had a domain user that only had permissions to see reports from one particular folder (and sub-folders) on the Report Server.
  • The user had been assigned the Content Manager role and could upload and run reports.
  • The problem ocurred when the user wanted to edit the properties of the report and then they would get the above error.

After a bit of digging around it seems that if your user does not have any permissions at the root level (Home folder) then they are not allowed to change any of the settings within a report, regardless of their current role assignment. In this case the solution was straight forward. I just added my domain user to the root folder with a Browser role. Since my root folder security settings were not propogating to any sub folders the user only has permission to access their particular reports and could now amend the properties for them as well.

For more information on the above error, take a look at;

Its more straight forward than you think.

 Passing MultiValue paramaters to a Stored Procedure in Reporting Services.

 
I have been working on a Reporting Services report that contained a MultiValue select list as one of the parameters and I needed to pass this comma separated list of values to my stored procedure. I had prepared my stored procedure so that it contained a user defined function to split out the values into a table and had tested my stored procedure independently of the report. My problems occurred when I tried to run my report and select multiple from my list. I would end up with the following error – ‘must declare scalar variable @VarName’. To confuse things a little further, if I just selected one item from the MultiValue list then my report would run fine.
 
The answer, according to other enlightend ssrs users, comes from the method used to define your dataset. I had originally defined my dataset using the Text Command Type to call the stored procedure. E.g. EXEC myStoredProc @MultiValueParam. To get stored procedures to use the multivalue paramaters the dataset needs to be created with a Stored Procedure Command Type and then define the parameters within the Parameters tab.
 
After making the change my report would run successfully using my multivalue prameters.

The request failed with HTTP status 403: Forbidden.

The request failed with HTTP status 403: Forbidden.
Following an unsuccessful Windows Update to a client’s server (not performed by me I hasten to add Smile) I started to get these 403 errors when trying to access the Report Server via the web client. The Micsoroft CRM application that was also sitting on the server and utilising the Report Server was also displaying errors when I tried to view the reports that are included in the system.
 
After doing some Googl-ing I found someone else who had encountered a similar problem and the following solution also worked for me;
  • Within IIS on the server hosting your Reporting Services website, right click the ReportServer virtual directory and select Properties…
  • On the Virtual Directory tab select the Configuration… button
  • On the Mappings tab of the Application Configuration dialog take a look at the box containing Wildcard application maps (order of implementation).
  • Mine was missing the c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_isapi.dll entry. I also compared this setting against a similar server which had a working Report Server.
  • I add the c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_isapi.dll entry to the list and then restarted IIS.

Thankfully, this was a successful solution and my access to the Report Server was restored. My reports view in Microsoft CRM had not quite been resolved because I was being shown a different error – Reports.config has invalid schema, and could not be loaded. This issue is discussed in this blog entry.

Reporting Services – SetExecutionParameters

Passing in Read-Only parameters to the Reporting Services Web Service
 
Just a quick one – I have recently been generating dynamic RDL’s and then using the Reporting Services Web Service to load and render the reports. One little headache I did have was defining the parameters for the reports using the SetExecutionParameters method. One of my parameters is read-only and as such when I tried to pass it to the report I got a nice error back informing me of this.
 
I spent some time looking around to determine how I pass a read only parameter to a report without any luck. Then it dawned on me . . . just don’t add the parameter to the prameter array . . . and make sure you reduce the array size by one. So my code now loooks like this;
 
    ReportExecutionWS.ParameterValue[] parameters = new ReportExecutionWS.ParameterValue[3];
    parameters[0] = new ReportExecutionWS.ParameterValue();
    parameters[0].Name = "Version";
    parameters[0].Value = "1.0.0";
    parameters[1] = new ReportExecutionWS.ParameterValue();
    parameters[1].Name = "ReportDate";
    parameters[1].Value = System.DateTime.Now.AddHours(-6).ToString();
    parameters[2] = new ReportExecutionWS.ParameterValue();
    parameters[2].Name = "Resolution";
    parameters[2].Value = "180";
    // We dont need to pass read-only parameters!!!
    //parameters[3] = new ReportExecutionWS.ParameterValue();
    //parameters[3].Name = "ParentId";
    //parameters[3].Value = string.Empty;