Friday, 23 January 2015

Calling SSRS with a WCF proxy

This is one of those things that you either need to do or you don’t.  If you don’t then good for you - if you do then the first thing to know is it isn’t as easy as it should be.

The SQL Server Reporting Services web service is an ASMX service, which means it’s intended to be called by an ASMX proxy of the kind that Visual Studio is wont to generate via the “Add Web Reference” context menu.  The only problem with Visual Studio generated proxies (for either WCF or ASMX services) is that they are a big bloated nightmare.  In theory the basicHttpBinding of WCF provides backward compatibility with ASMX web services, so you can start by running SvcUtil against the WSDL to generate a proxy in a single file – for example (assuming a local deployment of SSRS):

svcutil http://localhost:80/ReportServer/ReportExecution2005.asmx /language:c# /t:code /namespace:*,"SSRSClientDemo.Proxies" /out:ReportServerClient.cs /noconfig

The configuration is as follows:

        <security mode="TransportCredentialOnly"> 
          <transport clientCredentialType="Ntlm" /> 
    <endpoint address="http://localhost:80/ReportServer/ReportExecution2005.asmx" 
              name="ReportExecutionServiceSoap" /> 

Note that the clientCredentialType is Ntlm – this implies a non-Kerberos deployment (which a local environment will normally be).  As a general rule of thumb, if the reporting service is on a different machine to the database (also known as a scale-out deployment) then you’re using Kerberos and need to set the clientCredentialType to Windows.

So far so good, but you’ll probably find that the proxy can’t authenticate to the reporting service.  This is because the default security mode for basicHttpBinding is none.  So we need to get the proxy to use default network credentials (i.e. your Windows login):

var reportServiceClient = new ReportExecutionServiceSoapClient(); 
reportServiceClient.ClientCredentials.Windows.ClientCredential = System.Net.CredentialCache.DefaultNetworkCredentials;

For Kerberos deployments we need to enable delegation.  We can hard-code this but as we should aim to make everything configurable, it makes sense to derive it based on the clientCredentialType:

var impersonationLevel = TokenImpersonationLevel.None; 
var basicHttpBinding = reportServiceClient.Endpoint.Binding as BasicHttpBinding;

if (basicHttpBinding != null) 
    if (basicHttpBinding.Security.Transport != null && 
        basicHttpBinding.Security.Transport.ClientCredentialType == HttpClientCredentialType.Windows) 
        impersonationLevel = TokenImpersonationLevel.Delegation; 

reportServiceClient.ClientCredentials.Windows.AllowedImpersonationLevel = impersonationLevel; 

This queries the binding as set in configuration and if it’s Windows it enables delegation.

So once you’ve done that you can use the proxy as you would if it were ASMX.  The only issue is that WCF proxies are sort-of-mostly protocol agnostic so it doesn’t send useful information such as the HttpLanguage which means that both server and client regional settings will be ignored (and thus default to en-US).  Fortunately Paulo Morgado has a solution to this problem.

I’ve created a downloadable Visual Studio sample client and RDL file which demonstrates all of this.  This includes the regional settings fudge – if you look at line 30 in the HttpLanguageMessageInspector.cs file you can see where I set the culture based on Thread.CurrentThread.CurrentCulture.