Writing an SSRS Validation Tool

I have been involved in a project that integrates with the Microsoft SQL Server Reporting Services (SSRS) web service API for quite a while now, and the majority of the issues I have seen are related to the configuration of SSRS and the installation of our product on top of SSRS.

One of the first and most painful experiences my team had was configuring versions of SSRS prior to SQL Server 2005 SP2. It was extremely temperamental to configure, and I would strongly recommend not supporting earlier versions. However, since main stream support for SQL Server 2005 SP2 has already ended, you will probably want to support something more modern such as SQL Server 2008 R2. If you are installing your software in hundreds of customer sites, your installer is going to need a mechanism to verify that the SSRS instance you are given meets your minimum version requirement.

Installing the correct version of SSRS can be trickier than you think. This difficulty has to do with the overall adoption of SSRS. I currently work in the health care sector, and in my experience, very few hospitals know what SSRS does and most of them do not use it. Consequently, DBAs often have the Data Engine and Sql Agent features of SQL Server installed and patched, but none of the other features get installed. When the DBA installs SSRS on to the existing server, they will often forget to reapply the service packs. Therefore, the versions of SSRS and the Data Engine do not match (i.e. the Data Engine is patched, but the SSRS is not). This will not only cause odd behavior, but it masks the fact that the server is not running the minimum specified version of SSRS.

Verifying the version of SSRS is not straight forward either. It is highly unlikely that a DBA would let you install anything directly on to a data server. Therefore, your application is likely to be installed on a separate machine. This means that the installer will not have access to the registry or file system on the machine that houses SSRS, and will not be able to perform the actions described on TechNet to detect the version. Because of this, the easiest way I have found to check the version is to write a command line tool that does a web request to the SSRS web service API virtual directory (e.g. http://SomeServer/ReportServer) and verify the version number returned in the HTTP response.



The first thing that has to happen to verify the SSRS version via a web request is to authenticate. Coincidentally, the second major issue my project has run into is confusion over the credentials for the service account we use to communicate with the SSRS APIs. Despite excellent documentation, the person running the installer is likely to be confused about why the installer needs credentials for a windows account, what the scope of that account should be (i.e. does it need to be a domain account or can it be local to the database server), and where to configure this user to have adequate permissions in SSRS. If you are simply installing to an intranet data warehouse, this might not be as big of an issue because installations are rare and handled by knowledgeable staff. However, if your company produces software that is installed at customer sites and the installation depends on a variety of people who may or may not be knowledgeable, it is extremely important that your installer be able to validate the version of SSRS and verify that the service user can authenticate.

Since, by default, SSRS uses windows integrated security within IIS, the web request to verify the version number has to be configured with NT Lan Management (NTLM) security.

const string path = "http://SomeServer/ReportServer";
var uri = new Uri(path);
var nCreds = new NetworkCredential("MyUsername", "MyPassword", "MyDomain");
var creds = new CredentialCache { { uri, "NTLM", nCreds}};
var req = (HttpWebRequest) WebRequest.Create(path);
req.Credentials = creds;

Once the credentials are configured, the response stream can be read and iterated through.

using (var strm = req.GetResponse().GetResponseStream())
{
if (strm == null)
throw new NullReferenceException("Response stream was null.");

using (var stream = new StreamReader(strm))
{
var line = stream.ReadLine();
while (!stream.EndOfStream)
{
//TODO: Add comparison logic here...

line = stream.ReadLine();
}
}
}

As each line is read from the response stream, a regular expression can be used to identify the version number and it can be compared to a constant.

var minVersion = new Version(9, 00, 4053, 00);
const string regex = "^[\\s]*?<meta[\\s]*?name=\"Generator\"[\\s]*?content=\"" +
"Microsoft[\\s]*?SQL[\\s]*?Server[\\s]*?Reporting[\\s]*?Services[\\s]*?" +
"Version[\\s]*?([0-9]+?\\.[0-9]+?\\.[0-9]+?\\.[0-9]+?)\">$";
const RegexOptions options = ((RegexOptions.IgnorePatternWhitespace | RegexOptions.Multiline) | RegexOptions.IgnoreCase);
var reg = new Regex(regex, options);

if (!string.IsNullOrEmpty(line) && reg.IsMatch(line))
{
var match = reg.Match(line);
var versionString = match.Groups[1].Value;
var version = new Version(versionString);

if (version >= minVersion)
return true;
}

To make this tool more useful to the end user and to help them diagnose their problems, more granular return codes can be used instead of using a boolean return. Typically on projects I have been involved with, a negative return code signifies an error and anything greater or equal to zero is a success. With the use of return codes, a series of catch blocks can be used to check for common errors. (Note: For simplicity, the examples in this post do not have logging. However, logging is also extremely useful to the users when diagnosing a problem, and it should not be forgotten.)

catch (WebException ex)
{
if (ex.Message.Contains("(401) Unauthorized."))
return -3;
if (ex.Message.Contains("(404) Not Found."))
return -4;

return -5; //unknown error
}
catch (Exception ex)
{
return -5; //unknown error
}

Verifying your software's dependencies is an important practice that can eliminate problems and embarrassment when your product is deployed. Putting all of the logic from this article together, a method to validate the SSRS version and the service credentials might look something like the following.

//Add the following references:
//using System;
//using System.IO;
//using System.Net;
//using System.Text.RegularExpressions;

public int VerifyMinimumSsrsVerion(string url, string domain, string un, string pwd)
{
var minVersion = new Version(9, 00, 4053, 00);
const string regex = "^[\\s]*?<meta[\\s]*?name=\"Generator\"[\\s]*?content=\"" +
"Microsoft[\\s]*?SQL[\\s]*?Server[\\s]*?Reporting[\\s]*?Services[\\s]*?" +
"Version[\\s]*?([0-9]+?\\.[0-9]+?\\.[0-9]+?\\.[0-9]+?)\">$";
const RegexOptions options = ((RegexOptions.IgnorePatternWhitespace | RegexOptions.Multiline) | RegexOptions.IgnoreCase);
var reg = new Regex(regex, options);

try
{
var nCreds = new NetworkCredential(un, pwd, domain);
var creds = new CredentialCache { { new Uri(url), "NTLM", nCreds}};
var req = (HttpWebRequest) WebRequest.Create(url);
req.Credentials = creds;

using (var strm = req.GetResponse().GetResponseStream())
{
if (strm == null)
throw new NullReferenceException("Response stream was null.");

using (var stream = new StreamReader(strm))
{
var line = stream.ReadLine();
while (!stream.EndOfStream)
{
if (!string.IsNullOrEmpty(line) && reg.IsMatch(line))
{
var match = reg.Match(line);
var versionString = match.Groups[1].Value;
var version = new Version(versionString);

if (version >= minVersion)
return 0; //sucess
else
return -1; //Version did not meet minimum
}

line = stream.ReadLine();
}
return -2; //Version number could not be identified.  
}
}
}
catch (WebException ex)
{
if (ex.Message.Contains("(401) Unauthorized."))
return -3;
if (ex.Message.Contains("(404) Not Found."))
return -4;

return -5; //unknown error
}
catch (Exception)
{
return -5; //unknown error
}
}

Command Line Parsing

Command Line Parsing

Introduction
From time to time, I am still asked to create a console application for someone, and most of the time PowerShell is still not accepted by my clients for various reasons. Just like method parameters can become unwieldy in an under-designed system as requirements change, so can command line arguments. To minimize the mess, regular expressions can be used to access a command/arguments pattern (e.g. EncryptionUtil.exe /Decrypt /File="c:\My Documents\foo.xml"). Furthermore, if argument are passed in as a key/value pairing (e.g. /File="c:\My Documents\foo.xml"), the order of the arguments should not matter.

Determining the Command

/// 

/// Parses command line arguments to find the "command" 
/// that should be executed.
/// 
/// The command line arguments.
/// 
/// The command that is expected to be executed. 
/// Note: The value has been set to lower case (Invariant). 
/// 
/// 
/// Throws and exception if more than one command is found.
/// 
static string GetArgumentCommand(string[] args)
{
const string regex = "^/([^/][^=]+?)$";
var reg = new Regex(regex);

string result = null;

//Loop through arguments looking for mataches to regex.
foreach (var arg in args)
{
//Argument didn't match, continue to next arg.
if (!reg.IsMatch(arg))
continue; 

//More than one argument matched. Throw an error.
if (!string.IsNullOrEmpty(result))
throw new TooManyCommandsException();

//Argument matched  
var match = reg.Match(arg);
//Check for null/empty
var validMatch = match.Groups.Count >= 2 
&& match.Groups[1].Value != null;
result = validMatch ?
match.Groups[1].Value.ToLowerInvariant() : 
null;
}

return result;
}

Determining the Arguments

/// 

/// Parses command line arguments and creates a dictionary
/// of key/value pair arguments.
/// Note: If a key is used more than once, the first one is used.
/// 
/// The command line arguments.
/// 
/// Dictionary containing arguements.
/// Note: All keys have been set to lower case (Invariant). 
/// 
static Dictionary<string, string> GetArgumentDictionary(string[] args)
{
const string regex = "^/([^/][\\S]+?)\\=([\\S\\s]+?)$";
var reg = new Regex(regex);

var dic = new Dictionary<string, string>();

//Loop through arguments looking for mataches to regex.
foreach (var arg in args)
{
//Argument didn't match, continue to next arg.
if (!reg.IsMatch(arg))
continue;


//Argument matched
var match = reg.Match(arg);

//Determining Key
var validMatch = match.Groups.Count >= 3 
&& match.Groups[1].Value != null;
var key = validMatch ? 
match.Groups[1].Value.ToLowerInvariant() : 
null;
if (string.IsNullOrEmpty(key))
continue;

//Check for duplicate key.
if (dic.ContainsKey(key))
continue;

//Determine value
validMatch = match.Groups.Count >= 3;
var val = validMatch ? match.Groups[2].Value : null;

//Add key/value pair to dictionary.
dic.Add(key, val);
}

return dic;
}

Putting it Together

/// 

/// Application entry point.
/// 
/// The command line arguments.
static void Main(string[] args)
{
try
{
//Get the command and dictionary of arguments.
var cmd = GetArgumentCommand(args);
var dic = GetArgumentDictionary(args);

//Execute appropriate command.
switch (cmd) //remember commands are lower case
{
case "foo":
ExecuteFooCommand(dic);
break;
case "bar":
ExecuteBarCommand(dic);
break;
case "?": //redundant, but makes the point.
default:
DisplayHelpText(dic);
break;
}
}
catch (Exception ex)
{
Console.WriteLine("An exception occured.");
Console.WriteLine(ex.Message);
Console.WriteLine(ex.StackTrace);
}
}


/// 

/// Executes the foo command.
/// 
/// Dictionary containing key/value arguements.
static void ExecuteFooCommand(Dictionary<string, string> dic)
{
//Add appropriate code here.
}

/// 

/// Executes the bar command.
/// 
/// Dictionary containing key/value arguements.
static void ExecuteBarCommand(Dictionary<string, string> dic)
{
//Add appropriate code here.
}

/// 

/// Displays help text to the console.
/// 
/// Dictionary containing key/value arguements.
static void DisplayHelpText(Dictionary<string, string> dic)
{
//Add appropriate code here.
}