Using Reporting Services (SSRS) as a reference in an ASP.NET Core site

Viewed 13282

I struggled to find a solution for this for days so I wanted to share my situation. I'm converting an existing ASP.NET MVC application over to ASP.NET Core MVC. However the biggest change with using the ASP.NET Core, is that the System.Web namespace is a no go. However typically, SQL Server Reporting Services (SSRS) is typically added as a WebReference to a project, which is based on - you guessed it, System.Web.

So I needed to find an alternative way to be able to hit the SSRS endpoint for executing reports. For my scenario, I primarily wanted PDF's (though the call to SSRS's Render method allows you to pick what the export format is).

The solution to this problem presented it's own problems, most notably an error to the tune of:

Microsoft.ReportingServices.Diagnostics.Utilities.MissingSessionIdException: The session identifier is missing. A session identifier is required for this operation.

So my two questions I ended up answering that might be of value to others were, How do I use SSRS without System.Web, and how do I solve the error about "Missing session identifier"

3 Answers

Reference to Nick's solution above, I fixed the running exception of "session identifier is missing".

Here I show the whole solution of calling Asp.net core 2.0 web api to access SSRS and return pdf report in browser.

    [HttpGet("getpdfreport")]
    public async Task<IActionResult> GetPDFReport()
    {
        string reportName = "YourReport";
        IDictionary<string, object> parameters = new Dictionary<string, object>();
        parameters.Add("companyId", "2");
        parameters.Add("customerId", "123");
        string languageCode = "en-us";

        byte[] reportContent = await this.RenderReport(reportName, parameters, languageCode, "PDF");

        Stream stream = new MemoryStream(reportContent);

        return new FileStreamResult(stream, "application/pdf");

    }

    /// <summary>
    /// </summary>
    /// <param name="reportName">
    ///  report name.
    /// </param>
    /// <param name="parameters">report's required parameters</param>
    /// <param name="exportFormat">value = "PDF" or "EXCEL". By default it is pdf.</param>
    /// <param name="languageCode">
    ///   value = 'en-us', 'fr-ca', 'es-us', 'zh-chs'. 
    /// </param>
    /// <returns></returns>
    private async Task<byte[]> RenderReport(string reportName, IDictionary<string, object> parameters,  string languageCode, string exportFormat)
    {
        //
        // SSRS report path. Note: Need to include parent folder directory and report name.
        // Such as value = "/[report folder]/[report name]".
        //
        string reportPath = string.Format("{0}{1}", ConfigSettings.ReportingServiceReportFolder, reportName);

        //
        // Binding setup, since ASP.NET Core apps don't use a web.config file
        //
        var binding = new BasicHttpBinding(BasicHttpSecurityMode.TransportCredentialOnly);
        binding.Security.Transport.ClientCredentialType = HttpClientCredentialType.Ntlm;
        binding.MaxReceivedMessageSize = this.ConfigSettings.ReportingServiceReportMaxSize; //It is 10MB size limit on response to allow for larger PDFs

        //Create the execution service SOAP Client
        ReportExecutionServiceSoapClient reportClient = new ReportExecutionServiceSoapClient(binding, new EndpointAddress(this.ConfigSettings.ReportingServiceUrl));

        //Setup access credentials. Here use windows credentials.
        var clientCredentials = new NetworkCredential(this.ConfigSettings.ReportingServiceUserAccount, this.ConfigSettings.ReportingServiceUserAccountPassword, this.ConfigSettings.ReportingServiceUserAccountDomain);
        reportClient.ClientCredentials.Windows.AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation;
        reportClient.ClientCredentials.Windows.ClientCredential = clientCredentials;

        //This handles the problem of "Missing session identifier"
        reportClient.Endpoint.EndpointBehaviors.Add(new ReportingServiceEndPointBehavior());

        string historyID = null ;
        TrustedUserHeader trustedUserHeader = new TrustedUserHeader();
        ExecutionHeader execHeader = new ExecutionHeader();

        trustedUserHeader.UserName = clientCredentials.UserName;

        //
        // Load the report
        //
        var taskLoadReport = await reportClient.LoadReportAsync(trustedUserHeader, reportPath, historyID);
        // Fixed the exception of "session identifier is missing".
        execHeader.ExecutionID = taskLoadReport.executionInfo.ExecutionID;

         //
         //Set the parameteres asked for by the report
         //
         ParameterValue[] reportParameters = null;
        if (parameters != null && parameters.Count > 0)
        {
            reportParameters = taskLoadReport.executionInfo.Parameters.Where(x => parameters.ContainsKey(x.Name)).Select(x => new ParameterValue() { Name = x.Name, Value = parameters[x.Name].ToString() }).ToArray();
        }

        await reportClient.SetExecutionParametersAsync(execHeader, trustedUserHeader, reportParameters, languageCode);
        // run the report
        const string deviceInfo = @"<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>";

        var response = await reportClient.RenderAsync(new RenderRequest(execHeader, trustedUserHeader, exportFormat ?? "PDF", deviceInfo));

        //spit out the result
        return response.Result;
    }



/// <summary>
///  Work for reporting service.
/// </summary>
public class ReportingServiceEndPointBehavior : IEndpointBehavior
{
    public void AddBindingParameters(ServiceEndpoint endpoint, BindingParameterCollection bindingParameters) { }

    public void ApplyClientBehavior(ServiceEndpoint endpoint, ClientRuntime clientRuntime)
    {
        clientRuntime.ClientMessageInspectors.Add(new ReportingServiceExecutionInspector());
    }

    public void ApplyDispatchBehavior(ServiceEndpoint endpoint, EndpointDispatcher endpointDispatcher) { }

    public void Validate(ServiceEndpoint endpoint) { }
}

public class ReportingServiceExecutionInspector : IClientMessageInspector
{
    private MessageHeaders headers;

    public void AfterReceiveReply(ref Message reply, object correlationState)
    {
        var index = reply.Headers.FindHeader("ExecutionHeader", "http://schemas.microsoft.com/sqlserver/2005/06/30/reporting/reportingservices");
        if (index >= 0 && headers == null)
        {
            headers = new MessageHeaders(MessageVersion.Soap11);
            headers.CopyHeaderFrom(reply, reply.Headers.FindHeader("ExecutionHeader", "http://schemas.microsoft.com/sqlserver/2005/06/30/reporting/reportingservices"));
        }
    }

    public object BeforeSendRequest(ref Message request, IClientChannel channel)
    {
        if (headers != null)
            request.Headers.CopyHeadersFrom(headers);

        return Guid.NewGuid(); //https://msdn.microsoft.com/en-us/library/system.servicemodel.dispatcher.iclientmessageinspector.beforesendrequest(v=vs.110).aspx#Anchor_0
    }
}
Related