Tcp port addressing and runtime for ADOMD.Net

For SQL Server database instances, we specify custom ports in connection string as serverName,portName (seperated by comma). But in case of SQL Server Analysis Services, the convention is serverName:portName. Although the colon syntax is more common for tcp clients in general, but SQL Server database engine deviation created an incorrect assumption that the comma syntax will be used consistently across all microsoft products.

Another confusion in the mix was the minimal runtime requirements for SSAS .Net client. The search of word ‘runtime’ for SSAS didn’t yield any useful results. Accompanying this with errors below leads to confusion of COM registration, OLEDB requirements, etc.

The different types of errors including

Microsoft.AnalysisServices.AdomdClient.AdomdConnectionException: A connection cannot be made. Ensure that the server is running. ---> System.Net.Sockets.SocketException: The requested name is valid, but no data of the requested type was found
Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException: Class not registered
COMException (0x80040154): Retrieving the COM class factory for component with CLSID {B9776FC2-70D8-4664-A0DF-998114524D67} failed due to the following error: 80040154. Microsoft.AnalysisServices.AdomdClient.IXMLAStream..ctor() +32
SQL Browser service is missing

After trying several things (including regsvr32 msadomdx.dll), installing different SQL Server version ADOMD msi and ADOMD OLEDB msi for both 2005 and 2008, the solution narrowed down to two simple things:

1. We were using .Net 4.0 with SSAS 2005 via ADOMD.Net on Windows 2008 R2. On the web server which renders SSAS data, SQL Server 2008 R2 ADOMD.Net was the only required installation. This can be located by searching for ‘SQL Server feature pack’.

2. Use proper connection string. Below is a simple aspx test page which connects at a custom port (1234):

<%
try {
Microsoft.AnalysisServices.AdomdClient.AdomdConnection cn = new Microsoft.AnalysisServices.AdomdClient.AdomdConnection();
cn.ConnectionString = "Data source=SSASHost:1234;Provider=MSOLAP.2;Initial Catalog=myCatalog; Integrated Security=SSPI";
cn.Open();
Response.Write("OK");
}
catch (Exception ex) {
Response.Write(ex.ToString());
}
%>

Network sniffer in C#

Between the 3 classes (finance, econ, accounting) and their exams within a week, I came across SharpPcap and could resist writing something using this excellent library. Its a wrapper around winpcap so thats a pre-req. The codeproject sample code was a bit obselete at few instances, but it was not difficult to find the correct methods.

Here is the overall flow:

1. Choose network interface

2. Configure capture settings (tcpdump filter, capture mode)

3. Handle an event which will fire on packet arrival.

4. Start sniffing !!

1. Choosing Interface

This app was a console app. Mostly it was quick typing as done in author’s sample.

var devs = CaptureDeviceList.Instance;

From the list of devices, you list and pick which one you like to monitor.

2. Configure capture settings
Here you handle the event (PacketArrivalEventHandler), open the device in promiscuous mode and set any filter. I chose only tcp. Thats really nice to write tcpdump-style filters which can be a lot of fun. Next you start listening … till you exit.

          
 dev.OnPacketArrival += new PacketArrivalEventHandler(dev_OnPacketArrival);
 dev.Open(DeviceMode.Promiscuous, 1000);
 dev.Filter = "tcp";
 Console.WriteLine("Listening started for: dev: " + dev.Name);
 dev.StartCapture();
 Console.ReadLine();
 dev.Close();

3. Handle packet arrival event
After the packet arrives, you grab what you need. Converting RawPacket to Packet was a google search. The rest was straightforward. IP properties like source & destination ip are from ip casted packet. Tcp ports and payload were from tcp casted packet. I used parallel extension of C# (Task class) to offload the slow ip to name resolution to another function in async manner.

static void dev_OnPacketArrival(object sender, CaptureEventArgs e)
{
	string s0 = e.Packet.Timeval.Date.ToLocalTime().ToLongTimeString();
	string s1, s2, s3, s4, s5;
	s1 = s2 = s3 = s4 = s5 = string.Empty;

	var packet = Packet.ParsePacket(e.Packet);
	var tcp = TcpPacket.GetEncapsulated(packet);
	var ip = IpPacket.GetEncapsulated(packet);

	if (ip != null)
	{
		s1 = ip.SourceAddress.ToString();
		s3 = ip.DestinationAddress.ToString();
	}
	if (tcp != null)
	{
		s2 = tcp.SourcePort.ToString();
		s4 = tcp.DestinationPort.ToString();
		s5 = System.Text.Encoding.Default.GetString(tcp.PayloadData);
	}

	Task.Factory.StartNew(() => displayAfterResolve(s0, s1, s2, s3, s4, s5));
}

4. Handle packet arrival event
Here I resolve ip to name where possible and then dumped it to console. ResolveQueueSize is simple performance counter to watch the pending resolution queue growth visually. Also, a simple dictionary was used as dnsCache to avoid redundant lookups. The regex to cleanup payload needs to be worked on.

static void displayAfterResolve(string time, string srcIP, string srcPort, string dstIP, string dstPort, string payload)
{
	resolveQueueSize.Increment(); // perf counter
	string fmt = "@ {0}, {1}:{2} -> {3}:{4}. [{5} -> {6}]\r\n{7}\r\n";
	payload = Regex.Replace(payload, @"[^\w\s]", ""); // to improve ...
	Console.WriteLine(fmt, time, srcIP, srcPort, dstIP, dstPort, resolve(srcIP), resolve(dstIP), payload);
	resolveQueueSize.Decrement();  // perf counter
}

private static Dictionary dnsCache = new Dictionary();
private static object locker = new object();

static string resolve(string ip)
{
	cacheSize.RawValue = dnsCache.Count;

	string hostname = string.Empty;
	try
	{
		hostname = Dns.GetHostByAddress(ip).HostName;
	}
	catch
	{
		hostname = ip;
	}
	lock (locker)
	{
		if (!dnsCache.ContainsKey(ip))
		{
			dnsCache[ip] = hostname;
			return hostname;
		}
		else
			return dnsCache[ip];
	}
}

There is much more in the SharpPcap and my attempt was to get started with it. Quick google search resulting in links on how people have created psuedo firewall … by controlling ‘unwanted’ connections. Ofcourse, its way more fun than 3 classes + 4 cleps for almost the end my degree … but those are not that bad either.

OpenBTS

From a link to an upcoming hacking competition news, I learned about a software OpenBTS, which allows to setup your own cell tower with a relatively inexpensive kit. The source is on sourceforge but the pricing of kit was $3500 without support and $6k with support.

Here are the links to project and wikipedia page.

Microsoft Mathematics 4.0

Recently I got an alert for Microsoft Mathematics 4.0 from sd. I downloaded it from MS site (linko) and gave it try.
Installation was smooth + it installed DirectX on my XP pro windows.

Without reading any documentation, I tried to follow the dialog labelled “Type an expression and then click Enter.”
So I typed a an equation and pressed enter. To my surprise, it worked out great. Common symbols were taken care automatically e.g. x^2 converted to x square. Here are few screenshots:

Notice the solution steps – these could have been useful during our school years !!

a quadratic…

The graphing capability looks cool too, but after few clicks it crashed once.

Overall, it appears to have many more capabilities including stats, matrix operations, calculus. So try it out!

—–
You may already know similar capabilities of the online service: wolfram.
Here is its output to find the roots of the quadratic equation:

Comparing dynamic data sets

For comparison of tabular data there could be many ways, but as a learning exercise, here is the C# 4.0 “dynamic” way that I created with Sen.

1. Create sample dynamic data sets (not ADO.Net). The code is simply returing List of dynamic where each dynamic represents a column and its value (IDictionary). Assume that Report Id is PK.

// returns List and dynamic is IDictionary
var ds1 = GetData1(); 
var ds2 = GetData2();

2. Join the two data sets on a designated column e.g. Report Id. Return the matching Report Ids

var matchingReportIDs = ds1.Join(ds2, c1 => c1.REPORT_ID, c2 => c2.REPORT_ID, (s, d) => new { ReportId = s.REPORT_ID});
var lstMatchingReports = matchingReportIDs.ToList();

3. Loop through each match and compare the rest of the columns. You can use hashing (e.g. MD5 checksum) in case if you don’t care to see the exact column name and value which is different.

Here is the full source: Linko.