Loring Software, Inc

A Software Developer's Notebook

I have two customers using SQL Server Reporting Services (SSRS) to provide reports for their projects.  In one case, the client wants to have their clients get reports off their Extranet.  Since I want to make the reports really fancy, I am writing them in .Net for the browser experience, but to get Excel and PDF download functionality, I thought I would simply create nice static reports in SSRS and use the Render web service to download them.

The major problem I was having was that the default authentication scheme for SSRS web services is NTLM (Windows Integrated Authentication), which on the Internet is not possible.  I tried a number of hacks to get the requests from my browser running against my non-domain local running Visual Studio's IIS to actually get something back from the SSRS web services, but I got all sorts of errors back when I called the Render method, like:

The request failed with HTTP status 401: Unauthorized.

or

System.Web.Services.Protocols.SoapException: The permissions granted to user '...\IUSR..' are insufficient for performing this operation....

The first error occurred when I tried to pass my domain credentials (although I am not completely sure why, as I am an admin), and the second when I turned on anonymous access to the IIS ReportServer applicaiontion.  After hacking around far too much, I decided to sleep on it, and by around 6AM the approach I needed to take kind of gelled.

What I needed to understand was where all the protections were being set up.  I had IE, talking to a Visual Studio IIS on my local machine (not on the client's domain) calling a SSRS web service on the client's IIS, talking to SQL server.  So after finally getting the Google search terms right, I finally figured out how I wanted to set up the permissions.

- Create a local user on the SSRS/IIS server (Settings/Administrative Tools/Computer Management/System Tools/Local Users and Groups/Users/New User...)  You'll want to turn off Must Change Password, and check cannot change and never expires:

 New User       

- SSRS creates a couple of groups which sets the actual folder permissions to the SSRS web site.  You need to add this user to the

SQLServer2005ReportingServicesWebServiceUser$<server>$MSSQLSERVER

group, also in the Computer Management Console.  This lets this user, when "logged in", to actually run the aspx web service pages.  Locate this group in the Groups folder of the console, and right click and choose Add to group...  Find the SSRSReportUser, and add it to the screen



Add User

Now, this user needs to be able to read reports in SSRS.  You need to log in to the Reporting Service Control Panel to add this user to the Browser role in SSRS.  On the server, go to

http://localhost/Reports/Pages/Folder.aspx

which brings up the Home Page.  Provided you are an administrator, you will see the Properties tab in the upper left.  Click on that and choose New Role Assignment, and assign a Browser role for the new user:

Set Role

Okay, now the new user has rights to the web service's folders, and rights in SSRS to pull reports (only).  The last thing we need to do is connect to the web service as that user.  The first time I did this, I just set the anonymous user's identity in the ReportServer application in IIS to this user.  But since the Report Designer uses the same services to save reports, I was unable to deploy reports unless I unchecked the Enable Anonymous Access box in IIS when I needed it.and put is back when I was done.  This would not do, especially on a production server when clients could be coming in.  So I added the credentials of this user to the web services Credentials member before making the call:

ReportingService a_ws = new ReportingService();
a_ws.Credentials = new NetworkCredential("SSRSReportUser", "xxx");
byte [] a_result = a_ws.Render(...

A clear text password is not the best thing to have lying around in your code, so the credentials should be stored in encrypted form, which you can learn by Googling "asp.net encrypt network credentials".



Copyright © 2017 Loring Software, Inc. All Rights Reserved
Questions about what I am writing about? Email me