Extending Reporting Services

I am doing a reporting proof-of-concept (POC) for my company. Business intelligence (BI) is often the last thing that gets thought of during an application’s life cycle because it’s only really necessary after you get customers. Before that, the main focus is on application features. Soon after launch, your coworkers and your customers start asking questions about usage and adoption, and customers start to ask for summary information on their data as well as just dumping their data. If you’re the only guy in charge of the database, this is often overwhelming.

Thankfully, we now have many options for what’s called self-service BI. The developer or DBA sets up the basic data models (say Orders) in an automated tool like SQL Server Reporting Services (SSRS) and then allows an information worker to select the columns, perform joins, and add filters to create reports. This basically cuts down miscommunication that can often happen between IT and the rest of the business, since the people who need the reports are building the reports.

SSRS works out of the box for basic reporting, and by basic I mean non-self-service reports that are created by IT and put onto a server that the rest of the company can access with their Windows credentials. A report consumer can also do stuff like schedule a report to be run on the 1st of every month, and have it sent to Person A, B, and C. With a little bit of work, you can create what are called models, which are basically user-friendly database schema in SSRS. A normal user can then create ad hoc reports from a model. While these features are useful, many organizations need to hand reports to external customers and they need a reporting system that can handle custom authentication.

That’s the boat my company and I are in. We have a SaaS application that needs robust reporting that people outside of IT can use to create reports. The basic requirements for any SaaS reporting is that there’s customer authentication and authorization that’s tied back to the application, as well as customer segmentation, so users only see their own data. Unfortunately, SSRS does not have these features built-in. Fortunately, it’s a rather extensible product, and I proved that it could be used to effectively allow custom authentication as well as data segmentation.

apartment-complex
We got ourselves some multi-tenancy.

Custom Authentication

SSRS is simply an ASP.NET application that depends on SQL Server for storing its repository and SQL Server Agent for executing its jobs. As such, custom authentication is simply a matter of modifying a few XML files and providing a few .NET DLLs and .aspx pages to the server. I’m not going to reproduce the tutorial here, but CodePlex has a walk through process here.

Here’s a sample of what I did to add custom authentication (this example connects to a MySQL database for authentication):

internal static bool VerifyPassword(string suppliedUserName, string suppliedPassword)
{
	bool passwordMatch = false;

	string connStr = "server={server};user={user};database={database};port={port};password={password};";

	using (MySqlConnection conn = new MySqlConnection(connStr))
	{
		string sql = "Select pwd From usr Where username = @username;";

		MySqlCommand cmd = new MySqlCommand(sql, conn);
		cmd.Parameters.Add(new SqlParameter("username", suppliedUserName));
		try
		{
			conn.Open();
			using (MySqlDataReader reader = cmd.ExecuteReader())
			{
				reader.Read();
				string dbPasswordHash = reader.GetString(0);
				string suppliedPasswordHash = suppliedPassword;

				for (int i = 0; i < 100; i++)
				{
					suppliedPasswordHash = hashFunction(suppliedPasswordHash);
				}

				// Now verify them. Returns true if they are equal
				passwordMatch = suppliedPasswordHash.Equals(dbPasswordHash);
			}
		}
		catch (Exception ex)
		{
			throw new Exception(string.Format(CultureInfo.InvariantCulture,
			CustomSecurity.VerifyUserException + ex.Message));
		}
	}
	return passwordMatch;
}

This took no time at all to do. The hardest part was making sure I got all of the XML configuration right to enable this. Basically, in the tutorial I linked to above, you do two things: first, you tell SSRS to use the custom authentication objects and second, you tell it that the custom code you wrote is secure.

Data segmentation

Some reporting solutions have an idea of multi-tenancy built-in, but SSRS is not one of those products. For every customer, you have to create a new set of models and reports that are locked down to their data only. Thankfully, SSRS has a set of useful Web services that allows you to create reports and models programmatically.

You can use Visual Studio to create the set of C# objects that you use to interact with the report server. To get started, you create a new project and add a Web reference. To do this, go to Add Service Reference by right-clicking on Service Reference in the Solution Explorer. Then click Advanced in the lower left-hand corner, then click Add Web Reference. This will get you the basic C# objects you can then use to interact with SRSS. You work with SRSS through the ReportingService2010 object. Here’s how you create that using domain credentials:

NetworkCredential clientCredentials = new NetworkCredential("{domain}", "{username}", "{password}");

ReportingService2010 svc = new ReportingService2010();
svc.Credentials = clientCredentials;

You can then do things like create a folder (this one is create in the root):

Property[] props = new Property[0];

// Create a folder
svc.CreateFolder("{folder}", "/", props);

Or add a new report:


Byte[] reportDefinition = File.ReadAllBytes("{cannedReport}.rdl");
Warning[] warnings = null;

svc.CreateCatalogItem("Report", "", "/", false, reportDefinition, null, out warnings);

Extend to your heart’s content

Those are the basics, but there’s so much more to the Web services as well as SSRS in general. You can read all the methods available to you in the WS here. No product is perfect, and so it’s really great that you and your team have the option to tailor an already solid solution to your exact needs. For me, the custom authentication and Web services took SSRS from being a non-starter to a great option. That’s the beauty of programmable software.

Tagged with: ,
Posted in Databases, IT

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>