Skip to main content

Consuming the SSRS ReportExecutionService from a .NET Client

 

I’ve just finished writing a nice wrapper which internally calls the SSRS ReportExecutionService to generate reports.
Whilst it was fairly simple to implement there has been some major changes between 2005 and 2008 and the majority of online and documentation is based on the 2005 implementation.

The most important change is that the Report Server and Report Manager are no longer hosted in IIS which will be a welcomed change to Sys Admins but makes the security model and hosting model vastly different. So far I’ve yet to figure out how to allow Anonymous Access, if anyone knows how to do this leave a comment and it will be most appreciated.

Getting Started

To get started you’ll want to add a service reference to http://localhost/ReportServer_SQL2008/ReportExecution2005.asmx where ReportServer_SQL2008 is the name you configure in the Reporting Services Configuration Manager.

The Web Application files are located in C:\Program Files\Microsoft SQL Server\MSRS10.SQL2008\Reporting Services, you’ll want to to create a dedicated User and grant it permissions to these application folders.

Once you’ve done that you’ll need to browse to the Report Manager logging in as an Administrator account and then add a Role for your new user under the Security page http://localhost/Reports_SQL2008/Pages/Settings.aspx?SelectedSubTabId=SecurityLinkID and also under the Properties tab in Home http://localhost/Reports_SQL2008/Pages/Folder.aspx?SelectedTabId=PropertiesTab.

Calling the API

This post is mainly about solving some of the problems you may face, for detailed information on calling the API check out MSDN which has a wealth of information on the topic, here is the Overview and more specifically how to Render a Report.

However note that you may have to pass a TrustedUserHeader type whenever calling a Web Service method, it’s ok for this value to be Null.

WCF Security

Once you’ve followed the guide and coded your solution appropriately you may come across this exception or one similar when trying to call a Web Service method.

The authentication header received from the server was 'Negotiate,NTLM'.

The key here is to set the Network Credential property before to a Windows user who has access to the Reports and has the correct Role Assignments in the Report Manager.

var rsClient = new ReportExecutionServiceSoapClient(); 

            rsClient.ClientCredentials.Windows
                .ClientCredential = new NetworkCredential("username", "password");
            rsClient.ClientCredentials.Windows
                .AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation;
            

Also make sure that in the Web.Config in C:\Program Files\Microsoft SQL Server\MSRS10.SQL2008\Reporting\Services\ReportServer has the following values set.

   <authentication mode="Windows" /> 
   <identity impersonate="true" />

These are the bindings I use and work well. Make sure to set all the properties to the Maximum allowed values as Report Server returns some fairly large arrays.

<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="65536" maxBufferPoolSize="65536" maxReceivedMessageSize="65536"
            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/ReportServer_SQL2008/ReportExecution2005.asmx"
          binding="basicHttpBinding" bindingConfiguration="ReportExecutionServiceSoap"
          contract="Services.Impl.ReportExecution2005.ReportExecutionServiceSoap"
          name="ReportExecutionServiceSoap" />
    </client>
  </system.serviceModel>

 

Conclusion

I’m all about using the right tool for the job so for Reporting when you’re using SQL Server 2008 as your data store you really can’t go past SSRS which even comes free with SQL Server Express Edition.

Also included in SSRS 2008 is the Report Builder which is a Report Designer tool accessible from the Report Manager allowing users to design reports without having to install the Business Intelligence Studio.

Hope this helps someone.

Popular posts from this blog

ASP.NET MVC Release Candidate - Upgrade issues - Spec#

First of all, great news that the ASP.NET MVC Release Candidate has finally been released.  Full credit to the team for the hard work on this.  You can get the download here  However this is the first time I have had upgrade issues.  Phil Haack has noted some of the issues here   If like me you have lot's of CTP's and Add-Ins then you might experience some pain in Uninstalling MVC Beta on Vista SP1  This is the list of Add-Ins / CTP's I had to uninstall to get it to work  Spec# PEX Resharper 4.1  Sourcelinks ANTS Profiler 4   Can't say I'm too impressed as it wasted over an hour of my time.  As it turned out Spec# turned out to be the offending culprit, it's forgiveable to have issues with a third party product but a Microsoft one? Guess no-one on the ASP.NET team has Spec# installed. 

Freeing Disk Space on C:\ Windows Server 2008

  I just spent the last little while trying to clear space on our servers in order to install .NET 4.5 . Decided to post so my future self can find the information when I next have to do this. I performed all the usual tasks: Deleting any files/folders from C:\windows\temp and C:\Users\%UserName%\AppData\Local\Temp Delete all EventViewer logs Save to another Disk if you want to keep them Remove any unused programs, e.g. Firefox Remove anything in C:\inetpub\logs Remove any file/folders C:\Windows\System32\LogFiles Remove any file/folders from C:\Users\%UserName%\Downloads Remove any file/folders able to be removed from C:\Users\%UserName%\Desktop Remove any file/folders able to be removed from C:\Users\%UserName%\My Documents Stop Windows Update service and remove all files/folders from C:\Windows\SoftwareDistribution Deleting an Event Logs Run COMPCLN.exe Move the Virtual Memory file to another disk However this wasn’t enough & I found the most space was