SQL Server Reporting Services Web Service Consumption



Recently had a requirement to generate pdf. Couple of choices came to my mind to achieve this, those are using 3rd party library like pdfsharp, using sql server reporting web service. I wanted to use sql server reporting web service for reasons like experience in generating ssrs reports, ssrs takes care of all heavy lifting like template, binding and already have the SSRS infrastructure in place.

Created a console application in which the SSRS web service is consumed and returned blob is submitted to an external system. As usual add the web reference to the service from the console application like below,




By using the client service reference, it was quick to get the blob by passing required parameters to render method. Everything worked by running the console application and it was the time to deployment to Dev environment. This console application has to run on a scheduler and this scheduler is crashing to run the console app. Based on event log messages there were compatibility issues between scheduler and application service reference code because the generated code based on .NET Framework 2.0 Web services technology.


Below link helped me in implementing the solution using Service reference,
http://blog.willbeattie.com/2010/11/consuming-ssrs-reportexecutionservice.html

Code:
public byte[] GetReport(string orderid)
{
    var reportingservice = new ReportExecutionServiceSoapClient();
    var password = ConfigurationManager.AppSettings["Password"];
    var credentials = new NetworkCredential(ConfigurationManager.AppSettings["Username"], password, ConfigurationManager.AppSettings["Domain"]);
    reportingservice.ClientCredentials.Windows
    .AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation;
    reportingservice.ClientCredentials.Windows.ClientCredential = credentials;
    byte[] result = null;
    string reportPath = "/ReportFolder/reportname";
    string format = "pdf";
    string historyID = null;
    string devInfo = "<DeviceInfo>" + "<SimplePageHeaders>True</SimplePageHeaders>" + "</DeviceInfo>";
 
    ParameterValue[] parameters = new ParameterValue[1];
    parameters[0] = new ParameterValue();
    parameters[0].Name = "OrderId";
    parameters[0].Value = orderid;  
 
    string encoding;
    string mimeType;
    string extension;
    Warning[] warnings = null;
    string[] streamIds = null;
    byte[] output;
 
    // Init Report to execute
    ServerInfoHeader serverInfoHeader;
    ExecutionInfo executionInfo;
    ExecutionHeader executionHeader = reportingservice.LoadReport(null, reportPath, historyID,
        out serverInfoHeader, out executionInfo);
 
    // Attach Report Parameters
    reportingservice.SetExecutionParameters(executionHeader, null, parameters, null, out executionInfo);
 
 
    // Render
    reportingservice.Render(executionHeader, null, format, devInfo, out output, out extension, out mimeType,
        out encoding, out warnings, out streamIds);
 
    return output;
 
}
Config Settings:
<system.serviceModel>
    <bindings>
      <basicHttpBinding>
        <binding name="ReportExecutionServiceSoap" closeTimeout="00:01:00"
            openTimeout="00:01:00" receiveTimeout="00:10:00" sendTimeout="00:01:00"
            allowCookies="false" bypassProxyOnLocal="false" hostNameComparisonMode="StrongWildcard"
            maxBufferSize="31457280" maxBufferPoolSize="65536" maxReceivedMessageSize="31457280"
            messageEncoding="Text" textEncoding="utf-8" transferMode="Buffered"
            useDefaultWebProxy="true">
          <readerQuotas maxDepth="2147483647" maxStringContentLength="2147483647" maxArrayLength="2147483647"
              maxBytesPerRead="2147483647" maxNameTableCharCount="2147483647" />
          <security mode="TransportCredentialOnly">
            <transport clientCredentialType="Ntlm" proxyCredentialType="None" realm="" />
            <message clientCredentialType="UserName" algorithmSuite="Default" />
          </security>
        </binding>
      </basicHttpBinding>    
    </bindings>
    <client>
      <endpoint address="http://localhost:80/ReportServer/ReportExecution2005.asmx"
          binding="basicHttpBinding" bindingConfiguration="ReportExecutionServiceSoap"
          contract="ReportingService.ReportExecutionServiceSoap" name="ReportExecutionServiceSoap" />     
    </client>
</system.serviceModel>

I did research on understanding nodes mentioned in the config and below are the links helped me,


Security node: https://docs.microsoft.com/en-us/dotnet/framework/configure-apps/file-schema/wcf/security-of-basichttpbinding
https://stackoverflow.com/questions/4481131/what-are-the-differences-between-security-mode-transport-and-security-mode-tr

Transport node: https://docs.microsoft.com/en-us/dotnet/framework/configure-apps/file-schema/wcf/transport-of-basichttpbinding
Message node: https://docs.microsoft.com/en-us/dotnet/framework/configure-apps/file-schema/wcf/message-of-basichttpbinding
Microsoft NTLM: https://msdn.microsoft.com/en-us/library/windows/desktop/aa378749(v=vs.85).aspx


Thank you.