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

Question

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"

Answer 1

The first step I used to solve this was Visual Studio's Connected Services and WCF. This method generates some classes that are similar to WebReferences but are based on System.DataModel instead of System.Web. I should note you can generate these same classes using the SvcUtil.exe tool if for whatever reason you don't have Visual Studio.

When using VS2017 <15.5, you need to grab the extension for adding WCF service references from Visual Studio Marketplace. For VS2017 >= 15.5, it's now built in. After which you should have a new entry when right-clicking on Connected Service, helpfully called Add Connected Service.... One of the entries on the next screen should be the Microsoft WCF Web Service Reference Provider (At the time of this writing, the extension is in Preview). Enter the URI to your service endpoint, which for me was in the form of http://[SERVERNAME-OR-IP]/ReportServer/ReportExecution2005.asmx?wsdl, and set your Namespace at the bottom. I left all the other defaults, and then hit Finish. I don't recall what I used in that field when I first got this setup but I wanted it to be [MyProjectNamespace].ReportingServices when it was done.

That will give you your class(es).

Again, this can alternatively be done using SvcUtil.exe too.

The code/classes I used in addition to my new reference are as follows. I did my best to make the code as comprehensive as possible. My actual implementation is a lot more refactored, but it just adds complexity that isn't needed to grasp how this all works. So I've tried to make this code as linear as possible. If there are errors with my code, feel free to let me know :-)

public async Task<byte[]> RenderReport(string report, IDictionary<string, object> parameters, string exportFormat = null)
{
    //My 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 = 10485760; //I wanted a 10MB size limit on response to allow for larger PDFs

    //Create the execution service SOAP Client
    var rsExec = new ReportExecutionServiceSoapClient(binding, new EndpointAddress(reportingServicesUrl));

    //Setup access credentials. I use windows credentials, yours may differ
    var clientCredentials = new NetworkCredential(reportingServicesUserName, reportingServicesPassword, reportingServicesDomain);
    rsExec.ClientCredentials.Windows.AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation;
    rsExec.ClientCredentials.Windows.ClientCredential = clientCredentials;

    //This handles the problem of "Missing session identifier"
    rsExec.Endpoint.Behaviors.Add(new ReportingServicesEndpointBehavior());

    //Load the report
    var taskLoadReport = await rsExec.LoadReportAsync(report, null);

    //Set the parameteres asked for by the report
    var reportParameters = taskLoadReport.Parameters.Where(x => parameters.ContainsKey(x.Name)).Select(x => new ParameterValue() { Name = x.Name, Value = parameters[x.Name].ToString() }).ToArray();
    await rsExec.SetExecutionParametersAsync(reportParameters, "en-us");

    //run the report
    const string deviceInfo = @"<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>";
    var response = await rsExec.RenderAsync(new RenderRequest(exportFormat ?? "PDF", deviceInfo));

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

Most of this is self explanatory, but I wanted to call out the endpoint behavior I'm adding. See, when loading the report details and subsequently using that information to set the parameters of the report using the values I have in my parameters... well parameter, and then rendering the report, you need to have a session identifier set to connect the calls to be all part of the same session context. The session identifier its looking for is a SOAP header value called ExecutionHeader with a value called 'ExecutionID'. This is provided on the response to my call to LoadReportAsync, but isn't automatically carried over to all future calls to the API. I've tried multiple ways to do this but ran into problems because of the inherent classes trying to set the XML Namespace to something other than what I want. Ultimately the EndpointBehavior was the least intrusive solution (and the only one I got working). The classes that support this look like this.

using System.ServiceModel.Channels;
using System.ServiceModel.Description;
using System.ServiceModel.Dispatcher;

internal class ReportingServicesEndpointBehavior : IEndpointBehavior
{
    public void AddBindingParameters(ServiceEndpoint endpoint, BindingParameterCollection bindingParameters) { }

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

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

    public void Validate(ServiceEndpoint endpoint) { }
}

internal class ReportingServicesExecutionInspector : 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
    }
}

There's two classes in here; one is a EndpointBehavior, and the other is a MessageInspector. The EndpointBehavior's sole purpose is to hookup the MessageInspector. I could found no way around that extra step. But what the MessageInspector does is, every time a response comes back, if we don't have an ExecutionHeader yet saved from a past response, we save the one from this response. Subsequently, every time we send a request, if we have an ExecutionHeader saved from a past response, I append it to the Headers for this new request. By doing it this way, I ensure that the Namespace and all the other intricacies around this session identifer are exactly the way the service provided them to begin with, so I'm as confident as I can be they will be valid.

Hopefully this helps anyone looking for solutions to this. I saw lots of other questions on the topic online but none had the answers/explanation I needed.

Answer 2

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
    }
}