Wednesday, August 19, 2009

SQL Server Reporting Service (SSRS) – 401 Unauthorized Access Issue in Production Environment

Let me share with you my experience on one of the issue i had with sql server reporting services in production environment.

Problem Description:
I am using sql server 2008 reporting services for reports in a .Net 3.5 application.

Production Environment:
Application Server: IM-APP1 (this is IIS machine and application is deployed here) has following windows server 2003, .Net 3.5, IIS 6.0
Database and Reporting Server: IM-SQL1 (This server has the database and sql reporting services), windows server 2003, .Net 3.5, SQL Server 2008, SQL 2008 Reporting Services

When i am browsing the application from desktop client, application works fine it fetches the data from database server, but when i navigate to a page, enter the report criteria and hit the button Generate Report then application throws 401-unauthorized access exception.

Now when i host database, reporting services and application on the same server (IM-APP1) then everything works fine.

below is the stack trace of the exception:

Exception Message : The request failed with HTTP status 401: Unauthorized.
Stack Trace : at Microsoft.SqlServer.ReportingServices2005.Execution.RSExecutionConnection.GetSecureMethods()
at Microsoft.SqlServer.ReportingServices2005.Execution.RSExecutionConnection.IsSecureMethod(String methodname)
at Microsoft.SqlServer.ReportingServices2005.Execution.RSExecutionConnection.SetConnectionSSLForMethod(String methodname)
at Microsoft.SqlServer.ReportingServices2005.Execution.RSExecutionConnection.LoadReport(String Report, String HistoryID)
at Microsoft.Reporting.WebForms.ServerReport.GetExecutionInfo()
at Microsoft.Reporting.WebForms.ServerReport.SetParameters(IEnumerable`1 parameters)
at Pages_Reports_LogFormReports.DisplayReport(ReportViewer reportViewer, String reportName, ReportParameter[] parmList)

DisplayReport() is the method name which is responsible for generating the report and display it in report viewer object

I reviewed the DisplayReport() method and found that report server user credentials are not passed to the report server, so here what Application server does is when it calls the reports and if the report is hosted on the same server it doesn't need any credential for generating report so the report is working fine, now in other scenario where reports are hosted on different server then Application Server should pass report server user credential to generate the report.

Now the question is how to pass report server user credential from the application.

Here is the solution:

First We need to Create one sealed class to perform this action. This class need to be inherited from IReportServerCredential interface.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Microsoft.Reporting.WebForms;
using Microsoft.ReportingServices;
using System.Security.Principal;
using RA.FRIMS.Settings;

/*****************************************************************************
* Created By : Nizam
* Created Date : Aug 17, 2009
* Description: This is sealed and serializable class and implementing IReportServerCredentials interface.
* This class Provides Network credentials to be used to connect to the report server.
* *****************************************************************************/

[Serializable]
public sealed class ReportServerNetworkCredentials : IReportServerCredentials
{
#region IReportServerCredentials Members
public bool GetFormsCredentials(out System.Net.Cookie authCookie, out string userName,
out string password, out string authority)
{
authCookie = null;
userName = null;
password = null;
authority = null;

return false;
}

// Specifies the user to impersonate when connecting to a report server.
//A WindowsIdentity object representing the user to impersonate.
public WindowsIdentity ImpersonationUser
{
get
{
return null;
}
}

// Returns network credentials to be used for authentication with the report server.
//A NetworkCredentials object.
public System.Net.ICredentials NetworkCredentials
{
get
{
//you can place below settings in configuration xml file
string userName = "ReportServerUserName";
string domainName = "ReportServerUserInDomainName";
string password = "ReportServerUserPassword";

return new System.Net.NetworkCredential(userName, password, domainName);
}
}

#endregion

}


Now all what you have to do in your display report method is:

//This method display reports in the report viewer object
private void DisplayReport(ReportViewer reportViewer, string reportName, ReportParameter[] parmList)
{
try
{
//For Reports with some input parameters.
reportViewer.ShowCredentialPrompts = false;
reportViewer.ShowParameterPrompts = false;
reportViewer.ShowDocumentMapButton = false;
reportViewer.ShowPageNavigationControls = true;
reportViewer.ShowFindControls = false;
reportViewer.ShowPrintButton = true;

reportViewer.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Remote;

// Here we are going to pass the ReportServerCredentials to the Report Viewer.
reportViewer.ServerReport.ReportServerCredentials = new ReportServerNetworkCredentials();


reportViewer.ServerReport.ReportServerUrl = "http:port//ReportServerMachineName
/reportserver";
reportViewer.ServerReport.ReportPath = "ReportFolderName" + reportName;

reportViewer.ServerReport.SetParameters(parmList);
reportViewer.ServerReport.Refresh();

}
catch (Exception ex)
{
//handle exception
}
}

That's all you are done!!! Now run the report.

Please feel free to comment on this post. Critics are highly appreciated. :-)

2 comments:

  1. This is very hopeful, I have now solved some of my problem. However I would need the user to be able to select parameters and when it posts back it uses the default everytime when I overwrite the Credentials.

    ReplyDelete
  2. For More Information Visit www.Patelinfosoft.us | www.Patelinfosoft.in

    Patel Infosoft ( Ritesh Patel )

    (M) : +91-9904970812 | +91-9824629794.

    For Job Alerts : www.JobAlerts.tk

    ReplyDelete