A Wrapper for Running SQL Server 2008 Reporting Services Reports Anonymously
With SQL Server 2008, Microsoft re-architected SQL Server Reporting Services (SSRS) to no longer have an IIS dependency. It now relies directly on HTTP.SYS and handles web protocols independently. This is a good thing, in a lot of respects (see https://msdn.microsoft.com/en-us/library/bb630410.aspx). But, there is also a down-side for those of us who are used to utilizing the IIS framework for hosting SQL Server reports. For one thing, allowing anonymous access to reports just by configuring an IIS virtual directory mapping is gone. Also, client certificates are no longer supported, which is a big deal if you’re in an environment where these are used (like I am).
There have already been a few articles written about anonymous access for SSRS 2008, and a couple of techniques to allow Report-manager type access (see https://blogs.msdn.com/jameswu/archive/2008/07/15/anonymous-access-in-sql-rs-2008.aspx, https://blogs.msdn.com/brianhartman/archive/2008/11/21/custom-credentials-in-the-report-viewer.aspx). Rather than rehashing them, my purpose in this post is to provide a generic solution for accomplishing anonymous access to your reports, without compromising your server security, and something you can tailor to your environment. Although my goal with this is to simply provide anonymous access, you can use this same technique for other purposes such as allowing smart-card logon to the hosting web site, since essentially the limitations related to accessing SSRS related to the authentication are wrapped in an ASP.NET application that does live off of IIS.
The basic concept is to take the SSRS report manager and wrap up the core functionality for executing reports inside of an ASP.NET web page that dynamically discovers and presents the reports and report folders in the same manner that the Report Manager does and then allow them to be executed. It does not provide all the functionality of Report Manager, such as the report management aspects, but you could tailor this to add that. Obviously for an anonymous-access scenario, most of those management functions don’t make sense, since they are associated with the identity of the user.
I’ve posted the solution on the web site https://www.bobthesis.com/reportsportal/ if you want to actually see how this in action. The menu user-interface is not pretty, but I’m not a web-designer, you can easily improve the cosmetics for that by modifying the master page/styles. The demo uses the Adventure Works samples, but you’ll find as we go through the code, there is nothing specific to Adventure works, other than an entry in the config file that indicates the name of the Adventure Works reports folder as the root folder.
So, let’s step through the solution. There are 7 main components to the solution:
- Reporting Services Web Service: The SQL Server Reporting Web Services (ReportingService2005.asmx – that’s right, 2005, it has not been changed for 2008) provides the interface for enumerating the reports and report folders. It provides a host of other capabilities for report execution, rendering and management, but that is the only capability we need for our solution.
- The ReportViewer Control: This is a control that we integrate into our ASP.NET solution. It provides the user experience for executing the reports including prompting for the parameters and providing the various options.
- A custom class for authenticating to the Reporting Web Service. This is what allows us to wrap the authentication in the ASP.NET page for the Report Viewer control to use to connect to the Reporting Service web service.
- An ASP.NET tree-view control for displaying a menu hierarchy for running the reports corresponding to the reporting folder structure defined in SSRS for the particular reporting application.
- A master page that includes the tree view control
- A default.aspx page that includes code to execute the Reporting Services web service and enumerate the results.
- A config file along with entries to define the authentication parameters.
Below is the overall architecture showing the main components. The dashed curved lines represent data flows, solid lines represent composition, dashed lines represent inheritance, and curved non-dashed line represent process flow.
Now, let’s walk through the development of the components, I’m assuming you’re familiar with Visual Studio.NET so will not delve into the details related to the IDE.
First, we create a ASP.NET application project in Visual Studio.NET and we’ll call it ReportsPortal (You could also use a web site project type if you prefer). At this point, we have our web.config and default.aspx. Next, add a master page to the project. At this point, our project structure should like this:
Next, add a reference to the ReportingService2005.asmx. For now, we will use the local machine to create the web service reference. However, we can modify this later to point to a different server, which is what I do for my demo solution. My reporting web service is on a different server than my web server.
Now, our solution should look as follows:
Now, let’s setup our master page to have place holders for our menu and our report viewer. For my demo, I provide the navigation interface on the left side bar and the report viewer on the right side. I use a table control to divide the placeholders and insert the tree navigation control on the left as shown below:
Below is the source code for the Master Page after setting a few properties to make the tree view not completely vanilla:
<%@ Master Language="C#" AutoEventWireup="true"
CodeBehind="ReportsPortal.master.cs"
Inherits="ReportsPortal.ReportsPortal" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"https://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="https://www.w3.org/1999/xhtml" >
<head runat="server">
<title></title>
</style>
<style type="text/css">
.style1
{
font-family: "Comic Sans MS";
height: 351px;
}
.style2
{
font-family: "Lucida Console";
font-size: large;
text-align: center;
}
</style>
</head>
<body bgcolor="#f9f9f9">
<form id="form1" runat="server">
<div class="style2"
style="background-color: #6699FF; font-family: 'Microsoft Sans Serif';
font-size: large; color: #800000;">
Anonymous SQL Server 2008 Reporting Services Wrapper</div>
<table class="style1">
<tr valign="top">
<td bgcolor="#E1FFF7" >
<asp:TreeView ID="TreeView1" runat="server"
NodeIndent="10" ShowLines="true"
ImageSet="BulletedList4">
<HoverNodeStyle BackColor="#339966" />
<SelectedNodeStyle BackColor="#666699" />
</asp:TreeView>
</td>
<td>
<asp:ContentPlaceHolder ID="ContentPlaceHolder1" runat="server">
</asp:ContentPlaceHolder>
</td>
</tr>
</table>
</form>
</body>
</html>
Before, we can start writing the code to use the web service to return our report items and before we can start using the Report Viewer Control, let’s put in the “plumbing” in our web.config, add the report viewer control to the the form, ensure we have the necessary reporting services references and leverage the authentication interface.
Here are the web config entries, we create for both defining the starting location for our report menu as well as the user authentication settings for the proxy reporting user. We need to add the ApplicationSettings section to the config file, which I stick on the bottom. Of course, you should use an account wiht minimal user rights as your proxy reporting user, and you may want to use the .NET DPAPI encryption capabilities to encrypt your web.config file (see https://www.dotnetcurry.com/ShowArticle.aspx?ID=185&AspxAutoDetectCookieSupport=1 for more info on encrypting .net config files), although since these are anonymous reports anyways, it isn’t critical, as long as the proxy reporting user has very limited rights. The report user account should be a normal user account and have permissions in the SQL database to the stored procedures, views, tables, required to run the various reports as well as have browser permissions to the Report Server. If you don’t have an instance name remove the “_myinstance” part of the report service url. Notice we have to repeat the server url in 2 different formats – 1 for the web service reference including the reportservice2005.asmx and the other just containing the root location. The second one is used by the authentication interface and it will not work with the full web service url.
<applicationSettings>
<ReportsPortal.Properties.Settings>
<setting name="ReportsPortal_WsReportService_ReportingService2005"
serializeAs="String">
<value>https://myServer/ReportServer_myinstance/ReportService2005.asmx</value>
</setting>
<setting name="MyReportServerUrl" serializeAs="String">
<value>https://myServer/ReportServer_myinstance</value>
</setting>
<setting name="ReportsRootPath" serializeAs="String">
<value>/</value>
</setting>
<setting name="TopLevelMenuText" serializeAs="String">
<value>AdventureWorks Reports</value>
</setting>
<setting name="MyReportViewerPassword" serializeAs="String">
<value>myPasswordvalue>
</setting>
<setting name="myReportViewerUser" serializeAs="String">
<value>myreportUser</value>
</setting>
<setting name="MyReportViewerDomain" serializeAs="String">
<value>mydomain</value>
</setting>
</ReportsPortal.Properties.Settings>
</applicationSettings>
However, in addition to the above standard application settings, we need to create a special configuration setting for the ReportServerConnection interface in the appsettings section. (see xxx).
Below is in the part of the config file higher up and before system.web:
<appSettings>
<add key="ReportViewerServerConnection"
value="MyReportServerConnection, ReportsPortal"/>
</appSettings>
In this case, “My ReportServerConnection” refers to the name of the class file that you create to implement the IreportServerConnection interface and “ReportsPortal” and “ReportsPortal” refers to the name of the assembly for your web application.
I know this is a bit confusing, so I’ve included all of the code in a zip file that you download when you get to the end of the article…)
Note, you can also use the Settings approach to create these and then use the Properties.Default.Settings to access the values rather than using the Configuration Manager method.
Next, let’s add the ReportViewer control to the default.aspx page. Set the property on the default.aspx page to use the ReportsPortal master page, so we should have 1 placeholder to work with and we drag the Microsoft report viewer control from the Reporting section of the toolbox onto the form. When we drag the control on the form, it will update our project to include a reference to the Microsoft.ReportViewer.WebForms assembly as well as update our web.config with the assembly information.
So, now our default.aspx should look like below, we don’t need to set any properties as this will all happen dynamically in the code, however, you will probably want to increase the width and add zoom mode to make the user experience better.
Below is the source of the default.aspx including some property settings for the report viewer rendering:
<%@ Page Language="C#" AutoEventWireup="true"
CodeBehind="Default.aspx.cs" Inherits="ReportsPortal._Default"
MasterPageFile="~/ReportsPortal.Master" %>
<%@ Register assembly="Microsoft.ReportViewer.WebForms,
Version=9.0.0.0, Culture=neutral,
PublicKeyToken=b03f5f7f11d50a3a"
namespace="Microsoft.Reporting.WebForms"
tagprefix="rsweb" %>
<asp:Content ID="Content1" runat="server"
contentplaceholderid="ContentPlaceHolder1">
<asp:Label ID="ReportLabel" runat="server"></asp:Label>
<rsweb:ReportViewer ID="ReportViewer1" runat="server"
SizeToReportContent="True" Width="800px"
ZoomMode="PageWidth" Height="600px">
</rsweb:ReportViewer>
</asp:Content>
Now, let’s take a look at the authentication interface portion. I use the ReportServerConnection2 interface because it supports stateless reporting and seems the most robust, there are others that can be used (see https://blogs.msdn.com/brianhartman/archive/2008/11/21/custom-credentials-in-the-report-viewer.aspx). To get it to compile, you will need to add a reference to the Microsoft.ReportViewer class. Here is the code for the MyReportServerConnection.cs class (you can use different names, but remember everything has to be in sync with the config setting that references back to the class).
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Microsoft.Reporting.WebForms;
using System.Net;
using System.Security.Principal;
using System.Configuration;
namespace ReportsPortal
{
[Serializable]
public class MyReportServerConnection : IReportServerConnection2
{
public Uri ReportServerUrl
{
get
{
string url = Properties.Settings.Default.MyReportServerUrl;
if (string.IsNullOrEmpty(url))
throw new Exception("Missing url from the Web.config file");
return new Uri(url);
}
}
public int Timeout
{
// set timeout to 60 seconds
get { return 60000; }
}
public IEnumerable<Cookie> Cookies
{
// No custom cookies
get { return null; }
}
public IEnumerable<string> Headers
{
// No custom headers
get { return null; }
}
public MyReportServerConnection()
{
}
public WindowsIdentity ImpersonationUser
{
get { return null; }
}
public ICredentials NetworkCredentials
{
get
{
// return null will force the use of impersonation,
// otherwise, remove the return null and
// implement the other app settings to specify the credential details
// return null;
string userName = Properties.Settings.Default.myReportViewerUser;
if (string.IsNullOrEmpty(userName))
throw new Exception("Missing user name from Web.config file");
string password = Properties.Settings.Default.MyReportViewerPassword;
if (string.IsNullOrEmpty(password))
throw new Exception("Missing password from Web.config file");
string domain = Properties.Settings.Default.MyReportViewerDomain;
if (string.IsNullOrEmpty(domain))
throw new Exception("Missing domain from Web.config file");
return new NetworkCredential(userName, password, domain);
}
}
public bool GetFormsCredentials(
out Cookie authCookie,
out string userName,
out string password,
out string authority)
{
authCookie = null;
userName = null;
password = null;
authority = null;
return false;
}
}
}
The project should now look something like below:
Now, that the plumbing is done, we can focus on the 2 other pieces of code. First, lets do the code to enumerate the reporting services folder and return the lists into the treeview control. This is the heart of the application as this basically wraps the Report Manager user interface for navigating reports and folders. We put the enumeration code in the Master page .cs (ReportsPortal.Master.cs in our case). The Page_load just clears out the menu and repopulates it from the Report Server by calling BuildNavigation as long as this is not just a postback event. The not postback helps the performance by only doing this when first executing the application. The tree view control will contain all of the folders along with the items arranged hierarchically. Each report item then maps to a navigation url that contains a link back to the default.aspx passing in the full report item path. We can leverage the same credentials from the config file for invoking the report service as we do for running the reports.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using ReportsPortal.WsReportService;
using System.Security;
using System.Net;
namespace ReportsPortal
{
public partial class ReportsPortal : System.Web.UI.MasterPage
{
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
TreeView1.Nodes.Clear();
TreeView1.Nodes.Add(new TreeNode
(Properties.Settings.Default.TopLevelMenuText,
Properties.Settings.Default.ReportsRootPath));
BuildNavigation(TreeView1.Nodes[0]);
}
}
private void BuildNavigation(TreeNode parentNode)
{
// Enumerate all of the folders and add the reports
WsReportService.ReportingService2005 ws =
new WsReportService.ReportingService2005();
// Need to pass in credentials for reporting service.
NetworkCredential cred = new NetworkCredential(
Properties.Settings.Default.myReportViewerUser,
Properties.Settings.Default.MyReportViewerPassword,
Properties.Settings.Default.MyReportViewerDomain);
ws.Credentials = cred;
CatalogItem[] items = ws.ListChildren(parentNode.Value, false);
foreach (CatalogItem item in items)
{
if (item.Name != "Data Sources")
{
switch (item.Type)
{
case ItemTypeEnum.Report:
parentNode.ChildNodes.Add
(new TreeNode(
item.Name,
item.Path,
null,
"Default.Aspx?Report="
+ item.Path, "_top"));
break;
case ItemTypeEnum.Folder:
TreeNode newParentNode =
new TreeNode(item.Name, item.Path);
parentNode.ChildNodes.Add(newParentNode);
BuildNavigation(newParentNode);
break;
default:
break;
}
}
}
return;
}
}
}
Next, we make sure our default.aspx is set Now, we can bring it all together in the default.aspx.cs by doing a page load to evaluate the query string that gets set by the master page navigator when the user selects the link. It then instantiates the MyReportServerConnection class and maps the credentials back to the Server report that needs to be run.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Microsoft.Reporting.WebForms;
using System.Net;
namespace ReportsPortal
{
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if ((!IsPostBack) && Request.QueryString.Count > 0)
{
string reportPath = Request.QueryString[0];
this.ReportLabel.Text = Request.QueryString[0];
this.ReportViewer1.ProcessingMode = ProcessingMode.Remote;
MyReportServerConnection rsc = new MyReportServerConnection();
this.ReportViewer1.ServerReport.ReportServerCredentials = rsc;
this.ReportViewer1.ServerReport.ReportPath = reportPath;
this.ReportViewer1.ServerReport.ReportServerUrl =
new Uri((Properties.Settings.Default.MyReportServerUrl));
this.ReportViewer1.ServerReport.Refresh();
}
}
}
}
Before testing this out, we need to validate that permissions are correct for our demonstration report user. The account will need to have local logon rights for example. If you’re going across firewall boundaries, you’ll probably need to either setup the reports as not requiring credentials and use an execution account, or use SSL with a certificate so you can use basic authentication on the web service without worrying about credentials in the clear.
You can setup an execution account by using the Report Server configuration manager as shown below. If you do this, then you might not even have to bother with the special interface for the connection, but I haven’t tested that yet.
So, here is the end result, Yes, the treeview menu interface isn’t pretty, but it works, and it automatically enumerates down to the lowest folders.
Try it for yourself at https://www.bobthesis.com/reportsportal/
Technorati Tags: SSRS,SQL Server Reporting Services
Comments
Anonymous
September 23, 2009
Good Work Mr.bobleith Could you please send me the source Code for this article .. to be sure every thing is ok ... because when i try to run the project after finsh every thing get this error The type 'MyReportServerConnection, QmediaReports' could not be foundAnonymous
October 08, 2009
I'm having trouble getting this code sample to work. The blog mentions sample files. How can I get the sample code base?Anonymous
November 16, 2009
Qasem, The code is now posted. I apologize for the delay. I have been overwhelmed with work related to my doctoral thesis and still trying to do my day job the past couple of months.