Writing an SSRS Validation Tool

25 October 2010

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.

1 const string path = "http://SomeServer/ReportServer";
2 var uri = new Uri(path);
3 var nCreds = new NetworkCredential("Username", "Password", "Domain");
4 var creds = new CredentialCache { { uri, "NTLM", nCreds}};
5 var req = (HttpWebRequest) WebRequest.Create(path);
6 req.Credentials = creds;

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

 1 using (var strm = req.GetResponse().GetResponseStream())
 2 {
 3  if (strm == null)
 4      throw new NullReferenceException("Response stream was null.");
 6  using (var stream = new StreamReader(strm))
 7  {
 8      var line = stream.ReadLine();
 9      while (!stream.EndOfStream)
10      {
11          //TODO: Add comparison logic here...
13          line = stream.ReadLine();
14      }
15  }
16 }

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.

 1 var minVersion = new Version(9, 00, 4053, 00);
 2 const string regex = "^[\\s]*?<meta[\\s]*?name=\"Generator\"[\\s]*?content=\"" +
 3  "Microsoft[\\s]*?SQL[\\s]*?Server[\\s]*?Reporting[\\s]*?Services[\\s]*?" +
 4  "Version[\\s]*?([0-9]+?\\.[0-9]+?\\.[0-9]+?\\.[0-9]+?)\">$";
 5 const RegexOptions options = ((RegexOptions.IgnorePatternWhitespace | RegexOptions.Multiline) | RegexOptions.IgnoreCase);
 6 var reg = new Regex(regex, options);
 8 if (!string.IsNullOrEmpty(line) && reg.IsMatch(line))
 9 {
10  var match = reg.Match(line);
11  var versionString = match.Groups[1].Value;
12  var version = new Version(versionString);
14  if (version >= minVersion)
15      return true;
16 }

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.)

 1 catch (WebException ex)
 2 {
 3  if (ex.Message.Contains("(401) Unauthorized."))
 4      return -3;
 5  if (ex.Message.Contains("(404) Not Found."))
 6      return -4;
 8  return -5; //unknown error
 9 }
10 catch (Exception ex)
11 {
12  return -5; //unknown error
13 }

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.

 1 //Add the following references:
 2 //using System;
 3 //using System.IO;
 4 //using System.Net;
 5 //using System.Text.RegularExpressions;
 7 public int VerifyMinimumSsrsVerion(string url, string domain, string un, string pwd)
 8 {
 9  var minVersion = new Version(9, 00, 4053, 00);
10  const string regex = "^[\\s]*?<meta[\\s]*?name=\"Generator\"[\\s]*?content=\"" +
11      "Microsoft[\\s]*?SQL[\\s]*?Server[\\s]*?Reporting[\\s]*?Services[\\s]*?" +
12      "Version[\\s]*?([0-9]+?\\.[0-9]+?\\.[0-9]+?\\.[0-9]+?)\">$";
13  const RegexOptions options = ((RegexOptions.IgnorePatternWhitespace | RegexOptions.Multiline) | RegexOptions.IgnoreCase);
14  var reg = new Regex(regex, options);
16  try
17  {
18      var nCreds = new NetworkCredential(un, pwd, domain);
19      var creds = new CredentialCache { { new Uri(url), "NTLM", nCreds}};
20      var req = (HttpWebRequest) WebRequest.Create(url);
21      req.Credentials = creds;
23      using (var strm = req.GetResponse().GetResponseStream())
24      {
25          if (strm == null)
26              throw new NullReferenceException("Response stream was null.");
28          using (var stream = new StreamReader(strm))
29          {
30              var line = stream.ReadLine();
31              while (!stream.EndOfStream)
32              {
33                  if (!string.IsNullOrEmpty(line) && reg.IsMatch(line))
34                  {
35                      var match = reg.Match(line);
36                      var versionString = match.Groups[1].Value;
37                      var version = new Version(versionString);
39                      if (version >= minVersion)
40                          return 0; //sucess
41                      else
42                          return -1; //Version did not meet minimum
43                  }
45                  line = stream.ReadLine();
46              }
47              return -2; //Version number could not be identified.
48          }
49      }
50  }
51  catch (WebException ex)
52  {
53      if (ex.Message.Contains("(401) Unauthorized."))
54          return -3;
55      if (ex.Message.Contains("(404) Not Found."))
56          return -4;
58      return -5; //unknown error
59  }
60  catch (Exception)
61  {
62      return -5; //unknown error
63  }
64 }