Accessing Report Server using Report Server Web Service - Microsoft SQL Server 2008R2

Today I am going to write about how to access SQL Server Report Server through Report Server Web service. You can access all the full functionality of the report server through this Report Server Web service. The Report Server Web service is an XML Web service with a SOAP API. It uses SOAP over HTTP and acts as a communications interface between client programs and the report server.

The Microsoft SQL Server 2008R2 Report Server Web service provides two endpoints, one is for report management and the other one is for report execution.

1. ReportService2010
• The ReportService2010 endpoint contains methods for managing objects in a Report Server in either native or SharePoint integrated mode. The WSDL for this endpoint is accessed through  http://server/reportserver/ReportService2010.asmx?wsdl.
2. ReportExecution2005
• The ReportExecution2005 endpoint allows developers to programmatically process and render reports in a Report Server. The WSDL for this endpoint is accessed through  http://server/reportserver/ReportExecution2005.asmx?wsdl.

Previous versions of Microsoft SQL Servers' has several versions of Report Server Web service endpoints. For example ReportService2005 and ReportService2006. But ReportService2005 andReportService2006 endpoints are deprecated in SQL Server 2008 R2. The ReportService2010 endpoint includes the functionalities of both endpoints and contains additional management features.

Now, I will move into how to access Report Server using Report Server Web Service. I have created sample web site and in the Default.aspx page I have put a single button. First what I would do is, I will add a Service Reference to Report Server Web Service and the endpoint I am going to use is ReportService2010.

I will right click on my Web Site Project and will click on Add Service Reference.

Add Service Reference

In here, I have put http://server/reportserver/ReportService2010.asmx as address, I did not add ?wsdl to the end of the address, because both are valid formats.

Now if you observe the Web.config file, you will see that following part is added. 

<system.serviceModel>
  <bindings>
    <basicHttpBinding>
      <binding name="ReportingService2010Soap" closeTimeout="00:01:00" openTimeout="00:01:00" receiveTimeout="00:10:00" sendTimeout="00:01:00" allowCookies="false" bypassProxyOnLocal="false" hostNameComparisonMode="StrongWildcard" maxBufferSize="65536" maxBufferPoolSize="524288" maxReceivedMessageSize="65536" messageEncoding="Text" textEncoding="utf-8" transferMode="Buffered" useDefaultWebProxy="true">
        <readerQuotas maxDepth="32" maxStringContentLength="8192" maxArrayLength="16384" maxBytesPerRead="4096" maxNameTableCharCount="16384"/>
        <security mode="None">
          <transport clientCredentialType="None" proxyCredentialType="None" realm=""/>
          <message clientCredentialType="UserName" algorithmSuite="Default"/>
        </security>
      </binding>
    </basicHttpBinding>
  </bindings>
  <client>
    <endpoint address="http://server/ReportServer/ReportService2010.asmx" binding="basicHttpBinding" bindingConfiguration="ReportingService2010Soap" contract="ReportService2010.ReportingService2010Soap" name="ReportingService2010Soap"/>
  </client>
</system.serviceModel>

Now in my button click event I am writing following code.

using System.Net;
using ReportService2010;

protected void btnListChildren_Click(object sender, EventArgs e)
{
    NetworkCredential clientCredentials = new NetworkCredential("username", "password", "domain");
    ReportService2010.ReportingService2010SoapClient client = new ReportService2010.ReportingService2010SoapClient();
    client.ClientCredentials.Windows.AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation;
    client.ClientCredentials.Windows.ClientCredential = clientCredentials;
    client.Open();
    TrustedUserHeader t = new TrustedUserHeader();
    CatalogItem[] items;
    // I need to list of children of a specified folder.
    ServerInfoHeader oServerInfoHeader = client.ListChildren(t, "/", true, out items);
    foreach (var item in items)
    {
        // I can access any properties of item
    }
}

Now again in my Web.config file I need to do some modifications. If not I might get this type of error when I am executing my button click event.

 

Request is unauthorized.

I am modifying the Web.config file as follows.

<security mode="TransportCredentialOnly">
  <transport clientCredentialType="Ntlm" proxyCredentialType="None" realm=""/>
  <message clientCredentialType="UserName" algorithmSuite="Default"/>
</security>

 

That's all. Now when I run the Web Site I can get the list of children in the parent folder through the Report Server Web Service. Through this Web Service we can access all the full functionality of the report server. Isn't it great.

Happy Coding.

Regards,
Jaliya