Wednesday, May 16, 2012

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 and ReportService2006 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

15 comments:

  1. Hello,
    I tried your suggestion and resolved the error you mentioned above. But when I applied your solution I got another error.

    System.ServiceModel.FaultException

    {"The operation you are attempting requires a secure connection (HTTPS). ---> Microsoft.ReportingServices.Diagnostics.Utilities.SecureConnectionRequiredException: The operation you are attempting requires a secure connection (HTTPS)."}

    StackTrace:


    Server stack trace:
    at System.ServiceModel.Channels.ServiceChannel.HandleReply(ProxyOperationRuntime operation, ProxyRpc& rpc)
    at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs, TimeSpan timeout)
    at System.ServiceModel.Channels.ServiceChannelProxy.InvokeService(IMethodCallMessage methodCall, ProxyOperationRuntime operation)
    at System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage message)

    Exception rethrown at [0]:
    at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
    at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
    at StudyAdministrator.SoundwaveReportingService.ReportingService2010Soap.ListChildren(ListChildrenRequest request)
    at StudyAdministrator.SoundwaveReportingService.ReportingService2010SoapClient.StudyAdministrator.SoundwaveReportingService.ReportingService2010Soap.ListChildren(ListChildrenRequest request) in C:\...\Service References\SoundwaveReportingService\Reference.cs:line 11540
    at StudyAdministrator.SoundwaveReportingService.ReportingService2010SoapClient.ListChildren(TrustedUserHeader TrustedUserHeader, String ItemPath, Boolean Recursive, CatalogItem[]& CatalogItems) in C:\...\Service References\SoundwaveReportingService\Reference.cs:line 11548
    at StudyAdministrator.WebForms.ItcReporting.Page_Load(Object sender, EventArgs e) in C:\...\WebForms\ItcReporting.aspx.cs:line 44
    at System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e)
    at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e)
    at System.Web.UI.Control.OnLoad(EventArgs e)
    at System.Web.UI.Control.LoadRecursive()
    at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

    ReplyDelete
  2. Hi,

    It seems you have configured Reporting Server Web service methods to use secure connections. Please refer this link,

    http://msdn.microsoft.com/en-us/library/ms154709(v=sql.105).aspx

    Happy Coding.

    Regards,
    Jaliya

    ReplyDelete
  3. You have no idea how many tears you have saved me, I can't believe how poorly documented this web service is everywhere else. Thanks!

    ReplyDelete
  4. It works! You saved my time. Thanks

    ReplyDelete
  5. You certainly pointed me in the right direction. I had to do a bit extra to get the current user's credentials and use Windows instead of Ntlm in my case, but it works. Thanks :)

    ReplyDelete
  6. Where does the security section of the web.config go?

    Thanks

    ReplyDelete
  7. Tremendously helpful. Thank you very much.

    ReplyDelete
  8. Hi Jaliya,
    I know this is an old article you wrote and I just found it. I have a problem with to add web reference. I am using Visual Studio 2012. I click add service reference and go to web reference. I have only crystal report viewers in my IIS. As I have SQL server 2012 reporting servers MSDN says reporting server now saves in the database as a BLOB. So how can I create a web reference to my reporting server.
    When I use http://mycomputername/report_manager_2012 (my server name) it does not add a web reference. I hop you can help me. Thanks

    ReplyDelete
    Replies
    1. Hi,

      To add a web reference use the following address.
      http://server/reportserver/ReportService2010.asmx

      In SQL Server 2012 and SQL Server 2014, it's the same. Please have a look at following article as well.
      http://msdn.microsoft.com/en-us/library/ms155398.aspx

      Delete
    2. Hi Jaliya,
      Thanks for your reply. When I tried that I get this message.
      The permissions granted to user 'OFFICE\ADharma' are insufficient for performing this operation. (rsAccessDenied) Get Online Help.
      The thing is I logged in as SQL administrator went to sysadmin and gave the rights to me.

      Delete
    3. I opened the visual Studio 2012 as an administrator. Then I can find this .aspx web address. Then I get this:
      "The HTML document does not contain Web service discovery information."
      Thanks

      Delete
    4. try adding as,
      http://server/reportserver/ReportService2010.asmx?wsdl

      Delete
    5. Thaks Jaliya the doc http://msdn.microsoft.com/en-us/library/ms154709(v=sql.105).aspx took care of my issue, thanks for the post!

      Delete