WCF and SSRS

In working on a proof of concept for a new project, the architects were hoping to provide the data via a WCF web service to reporting services. It appeared to be relatively straightforward, although, not well documented. As an additional twist, they wanted to implement WS-Security.

I did some experimentation . . .

I found some semi-useful information on MSDN – (345251 for example, which has a decent outline on XML query syntax). I was able to use this to consume information non WCF web services that existed already.

While I was experimenting, the .NET developer was busily creating a WCF web service for consumption. When it was ready, we gave it a try.

No dice. Very frustrating. Second and third tries all yielded the same results. I was unable to connect to the web service.

While I was trying to resolve the problems, I found ONE blog post on the subject. In it, Jesse Ezell described his frustration in trying this very thing.

I think he summed it best when he said:

"1. SQL Reporting Services XML and web services support is slightly better than a piece of crap.

2. Documentation for the XML query provider in SQL Reporting Services blows."

http://weblogs.asp.net/jezell/archive/2008/02/21/using-wcf-endpoints-with-sql-reporting-services.aspx for the full post.

The first step, he explains, is to expose the WCF service using BasicHttpBinding. I got the .NET guy to remove the WS-Security binding, and everything started working. Hope that this saves someone the headache!