Thursday, March 5, 2015

Accessing Reporting Services from WCF Service

This is Networking 101, but I'm not a network engineer so I really struggled with this.

I have a WCF Service being called from WPF. The service needs to get a report from reporting services and return it to the WPF client. I could call reporting services directly from WPF, but there is extra functionality required that is most efficiently performed on the middle tier. My problem is how to provide good credentials to reporting services.

I'm using a request/response model to get the content of the report. The code looks like this...

    Dim Request As System.Net.HttpWebRequest = Nothing
    Dim Response As System.Net.HttpWebResponse = Nothing

    Request = System.Net.WebRequest.Create(URL)
    Response = Request.GetResponse()
    Stream = Response.GetResponseStream()
    BytesToRead = Response.ContentLength

The URL looks like this...
http://sql64/ReportServer?/Purchasing/VENDOR&rs:Format=PDF&rs:Command=Render&ID=219461

First, the WCF Service is hosted within an Application Pool that is running in IIS. So the identity of the service is determined by the identity of the application pool. I can either specify Request.UseDefaultCredentials = True which will pass the Application Pool's identity or I can construct a new credential from an explicit domain, user name, and password. Either way reporting services needs to be able to configured to permit the specified credential access to run the desired reports.

I need to be able to implement the solution on other people's networks, so I don't like the idea of creating a special domain user just for running reports. This is where NETWORK SERVICE comes in.

The NETWORK SERVICE account is special. Although each machine in the domain has it's own NETWORK SERVICE account, other machines in the domain can recognize it with a special notation... <DOMAIN>\<MACHINE>$ means "the NETWORK SERVICE account of DOMAIN\MACHINE".

So I started by changing the identity of the application pool that hosts my WCF Service to NETWORK SERVICE using IIS Manager.



The next step is to modify the WCF service to pass the default credentials to reporting services by adding this line before GetResponse().

    Request.UseDefaultCredentials = True

And the last bit is to give NETWORK SERVICE the rights to access your reports. To do this, you launch http://<sql server>/Reports in a web browser. 


Click on Site Settings, then select the security tab and click on the [New Roll Assignment] button. Enter <DOMAIN>\<MT NAME>$ (ie TWC-DOM2\DEV_MT$) and check the System User checkbox, and click [OK]. This gives your middle tier's NETWORK SERVICE user limited access to reporting services.

Now click on Home, select Folder Settings from the menu bar, and click the [New Roll Assignment] button. Enter the same User Name as the prior step, check the Browser checkbox, and click [OK]. This gives your middle tier authority to run all reports.

If you have developers that are hosting the WCF service on their development boxes, they will need to alter their local application pool and add themselves to the reporting services security.

No comments:

Post a Comment