// post processing - added Dec 5, 2016
public static void FindStraySQLServers(NetworkTrace trace)
{
// go through each SQL Server
// if only 1 conversation, see if conversation client IP and port match another SQL Server's Server IP and port
// if so, reverse the covnersation and add to the other SQl Server and get rid of this one
foreach (SQLServer s in trace.sqlServers)
{
if (s.conversations.Count == 1)
{
ConversationData c = (ConversationData)(s.conversations[0]);
SQLServer s2 = trace.FindSQLServer(c.sourceIP, c.sourceIPHi, c.sourceIPLo, c.sourcePort, c.isIPV6);
if (s2 != null)
{
reverseSourceDest(c);
s2.conversations.Add(c);
s.conversations.Remove(c);
s.sqlIP = 0;
s.sqlIPHi = 0;
s.sqlIPLo = 0;
s.sqlPort = 0;
}
}
}
// remove bad entries from the trace.sqlServes collection - iterate backwards because of Remove method
for (int i = trace.sqlServers.Count - 1; i > 0; i--)
{
SQLServer s = (SQLServer)(trace.sqlServers[i]);
if (s.conversations.Count == 0 && s.sqlPort == 0 && s.sqlIP == 0 && s.sqlIPHi == 0 & s.sqlIPLo == 0)
{
trace.sqlServers.RemoveAt(i);
}
}
}
public long pktmonMaxDelay = 0; // - set in OutputText.DisplayDelayedPktmonEvents
public void AddFrame(FrameData f, NetworkTrace t) // replaces adding the frame directly to the frames ArrayList
{
//
// Rubrick:
//
// If the frame is not a pktmon frame, add the the frames ArrayData and exit. This will be the norm for almost all traces analyzed.
// If the frame is a pktmon frame, find the previous instance of this frame (go backwards for efficiency).
// If no previous instance, create the pktmonComponentFrames ArrayList and add the frame to that and add the frame to the ArrayList and exit.
// If there is a previous frame, add the frame to the pktmonComponentFrames ArrayList of that frame and exit.
//
if (f.pktmon == null)
{
frames.Add(f);
t.frames.Add(f);
}
else
{
// search and add - use the limit of 20 frames. For a single conversation, it can't be out of sequence that much
if (f.pktmon.eventID == 170) // drop packet event
{
this.hasPktmonDroppedEvent = true;
this.pktmonDropReason = f.pktmon.DropReason;
}
const int BACK_COUNT_LIMIT = 20;
int backCount = 0;
for (int i = this.frames.Count - 1; i >= 0; i--)
{
FrameData priorFrame = (FrameData)frames[i];
backCount++;
if (priorFrame.pktmon.PktGroupId == f.pktmon.PktGroupId)
{
priorFrame.pktmonComponentFrames.Add(f); // found withing BACK_COUNT_LIMIT frames
return;
}
if (backCount >= BACK_COUNT_LIMIT)
{
break;
}
}
// not found within BACK_COUNT_LIMIT frames, so assume it's the first
f.pktmonComponentFrames = new ArrayList();
f.pktmonComponentFrames.Add(f); // make sure the frame is first in the list, so we don't have to treat it differently when performing pktmon analyses
frames.Add(f);
t.frames.Add(f);
}
}
public static void Process(NetworkTrace trace)
{
foreach (ConversationData c in trace.conversations)
{
if (c.sourcePort == 88)
{
TDSParser.reverseSourceDest(c);
}
if (c.destPort != 88)
{
continue;
}
KerberosData KerbData = null;
try
{
if (c.isUDP) // UDP
{
KerbData = ProcessUDP(c);
}
else // TCP
{
KerbData = ProcessTCP(c);
}
// ignore non KRB_TGS requests
// ignore responses without an associated request - we don't want to log errors for unidentified request types
// ignore SNames we don't know what they are ... or how to read them
//if (KerbData.RequestType == MessageTypes.KRB_TGS_REQ
// && (KerbData.SNameType == 2 || KerbData.SNameType == 10))
if (KerbData.RequestType == MessageTypes.KRB_TGS_REQ)
{
trace.KerbResponses.Add(KerbData);
}
}
catch (Exception ex)
{
Console.WriteLine("Exception during Kerberos processing." + "\r\n" + ex.Message + "\r\n" + ex.StackTrace);
Program.logDiagnostic("Exception during Kerberos processing." + "\r\n" + ex.Message + "\r\n" + ex.StackTrace);
} // catch
} // foreach
} // void Process(...)
// post processing
public static void FindStraySQLConversations(NetworkTrace trace)
{
// go through all non-SQL conversations and see if they are on a port that SQL is using and set the flag, reverse source and dest if necessary, and add to SQLServers.conversations
foreach (ConversationData c in trace.conversations)
{
if (c.isSQL == false)
{
SQLServer server = trace.FindSQLServer(c);
if (server != null)
{
c.isSQL = true;
server.conversations.Add(c);
if (c.destIP != server.sqlIP || c.destIPHi != server.sqlIPHi || c.destIPLo != server.sqlIPLo || c.destPort != server.sqlPort)
{
reverseSourceDest(c);
}
}
}
}
}
static void Main(string[] args)
{
string fileSpec = null;
string outFile = null;
string diagOutFile = null;
string statOutFile = null;
ArrayList sqlHints = new ArrayList();
ActivityTimer T = new ActivityTimer();
//
// Set command-line rules and parse the command-line
//
CommandLineParser cp = new CommandLineParser();
cp.AddRule(new ArgRule("", true, false, true, true)); // file name is required
cp.AddRule(new ArgRule("output", true, false, true, false)); // -output filename is optional and case-insensitive, one time only
cp.AddRule(new ArgRule("sql", true, true, false, false)); // -sql 10:10:10:10,1433 -sql 10.10.10.11,1433 ... -sql ip,port 0..n times (ipv4 or ipv6)
cp.AddRule(new ArgRule("convList", false, false, true, false)); // -convList outputs a rather lengthy report segment that is normally not required
cp.AddRule(new ArgRule("filterFmt", true, false, true, false)); // -filterFmt NETMON|WireShark replaces the Client IP address and port columns with a filter string, instead
string ruleViolation = cp.Parse(args);
if (ruleViolation != "")
{
Console.WriteLine("Bad arguments: " + ruleViolation);
displayUsage();
return; // exit the application
}
else // for case-insensitive argument names, use lower-case to match
{
ArrayList values = null;
//
// set filename -> fileSpec
//
values = cp.GetArgs("");
fileSpec = ((CommandLineArgs)values[0]).value; // required, so it's here or we'd get a ruleViolation earlier
//
// set outputFile -> outFile
//
values = cp.GetArgs("output");
if (values.Count != 0) // argument supplied
{
outFile = ((CommandLineArgs)values[0]).value;
}
else // argument omitted
{
outFile = utility.getLogFileName(fileSpec);
}
diagOutFile = utility.getDiagLogFileName(outFile);
statOutFile = utility.getStatLogFileName(outFile);
//
// set sqlHints
//
values = cp.GetArgs("sql");
if (values.Count != 0) // argument supplied
{
foreach (CommandLineArgs value in values)
{
sqlHints.Add(value.value);
}
}
//
// set outputConversationList
//
values = cp.GetArgs("convlist");
if (values.Count != 0)
{
outputConversationList = true;
}
//
// set filterFormat
//
values = cp.GetArgs("filterfmt");
if (values.Count != 0)
{
string value = ((CommandLineArgs)values[0]).value.ToUpper();
switch (value)
{
case "NETMON":
{
filterFormat = "N";
break;
}
case "WIRESHARK":
{
filterFormat = "W";
break;
}
case "AUTO":
{
filterFormat = "A"; // gets set to N or W based on the file type of the first file opened
break;
}
default:
{
Console.WriteLine("Bad arguments: filterFmt");
displayUsage();
return; // exit the application
}
}
}
}
commandLine = string.Join(" ", args);
try
{
diagFile = new StreamWriter(diagOutFile);
// output diagnostic header
logDiagnostic("SQL Server Network Analyzer " + VERSION_NUMBER);
logDiagnostic("Command line arguments: " + string.Join(" ", args));
logDiagnostic("Analysis run on: " + DateTime.Now.ToString(utility.DATE_FORMAT));
// open log file
CurrentActivity = "opening log file: " + outFile;
logFile = new StreamWriter(outFile);
NetworkTrace Trace = new NetworkTrace();
// add SQL hints
foreach (string value in sqlHints)
{
bool isIPV6 = false;
ushort port = 0;
uint ipv4 = 0;
ulong ipv6hi = 0, ipv6lo = 0;
utility.ParseIPPortString(value, ref isIPV6, ref port, ref ipv4, ref ipv6hi, ref ipv6lo);
Trace.GetSQLServer(ipv4, ipv6hi, ipv6lo, port, isIPV6); // creates an entry in the SQl Server table if not one already - allows for duplicate -sql command-line arguments
}
// read files and parse into memory structures
CurrentActivity = "parsing input file(s) from the folder.";
Parser.ParseFileSpec(fileSpec, Trace);
//Post Processing
CurrentActivity = "processing data.";
T.start("\nReversing backward conversations");
Parser.ReverseBackwardConversations(Trace);
T.stop();
T.start("Finding retransmitted packets");
Parser.FindRetransmits(Trace);
T.stop();
T.start("Finding retransmitted Keep-Alive packets");
Parser.FindKeepAliveRetransmits(Trace);
T.stop();
T.start("Finding continuation packets");
Parser.FindContinuationFrames(Trace);
T.stop();
T.start("Parsing TDS frames");
TDSParser.ProcessTDS(Trace);
T.stop();
T.start("Finding stray SQL conversations");
TDSParser.FindStraySQLConversations(Trace);
T.stop();
T.start("Finding stray SQL Servers");
TDSParser.FindStraySQLServers(Trace);
T.stop();
T.start("Creating packets from frames for SQL Conversations");
TDSParser.CreatingPacketsFromFrames(Trace);
T.stop();
T.start("Parsing UDP frames");
SSRPParser.ProcessUDP(Trace);
T.stop();
T.start("Parsing DNS frames");
NameResolutionParser.ProcessUDP(Trace);
T.stop();
T.start("Parsing Kerberos frames");
//CurrentActivity = "analyzing data.";
KerberosParser.Process(Trace);
T.stop();
T.start("Locating Domain Controllers");
//CurrentActivity = "analyzing data.";
DomainControllerParser.Process(Trace);
T.stop();
//Analysis
CurrentActivity = "writing report.";
statFile = new StreamWriter(statOutFile);
OutputText.TextReport(Trace);
statFile.Close();
statFile = null;
}
catch (Exception ex)
{
Console.WriteLine("An error occurred while " + CurrentActivity + "\r\n" + ex.Message + "\r\n" + ex.StackTrace);
logDiagnostic("An error occurred while " + CurrentActivity + "\r\n" + ex.Message + "\r\n" + ex.StackTrace);
}
finally
{
if (logFile != null)
{
logFile.Close();
}
if (diagFile != null)
{
diagFile.Close();
}
if (statFile != null)
{
statFile.Close();
}
}
}
public static void ProcessUDP(NetworkTrace trace)
{
foreach (ConversationData c in trace.conversations)
{
if (c.isUDP && c.sourcePort == 1434)
{
TDSParser.reverseSourceDest(c);
}
//parse only UDP conversations that are on port 1434
if ((!c.isUDP) || ((c.isUDP) && (c.destPort != 1434)))
{
continue;
}
SSRPData SSRPRequest = trace.GetSSRPRequest(c.destIP, c.destIPHi, c.destIPLo, c.isIPV6);
if (!SSRPRequest.hasConversation(c))
{
SSRPRequest.conversations.Add(c);
}
foreach (FrameData fd in c.frames)
{
try
{
if ((byte)(fd.payload[0]) == (byte)3) // CLNT_UCAST_EX
{
SSRPRequest.hasResponse = false;
}
else if ((byte)(fd.payload[0]) == (byte)4) // Request for specific instance (CLNT_UCAST_INST)
{
SSRPRequest.hasResponse = false;
if (c.frames.Count == 1)
{
SSRPRequest.hasNoResponse = true;
}
ushort Length = utility.ReadUInt16(fd.payload, 1);
SSRPRequest.instanceRequested = utility.ReadAnsiString(fd.payload, 3, Length);
//SSRPRequest.clientPort = c.sourcePort;
//SSRPRequest.clientIP = (c.isIPV6) ? utility.FormatIPV6Address(c.sourceIPHi, c.sourceIPLo) : utility.FormatIPV4Address(c.sourceIP);
SSRPRequest.sqlIP = c.destIP;
SSRPRequest.sqlIPHi = c.destIPHi;
SSRPRequest.sqlIPLo = c.destIPLo;
}
else if ((byte)(fd.payload[0]) == (byte)5) // Response of specifric instance (SVR_RESP)
{
SSRPRequest.hasResponse = true;
ushort Length = utility.ReadUInt16(fd.payload, 1);
String Response = utility.ReadAnsiString(fd.payload, 3, Length);
ParseSSRPResponse(Response, SSRPRequest, trace);
//if (SSRPRequest.sqlPort != 0)
//{
// SQLServer s = trace.GetSQLServer(SSRPRequest.sqlIP, SSRPRequest.sqlIPHi, SSRPRequest.sqlIPLo, SSRPRequest.sqlPort, SSRPRequest.isIPV6);
// if (s != null)
// {
// if (s.sqlHostName == "") s.sqlHostName = SSRPRequest.sqlHostName;
// if (s.instanceName == "") s.instanceName = SSRPRequest.instanceName;
// if (s.isClustered == "") s.isClustered = SSRPRequest.isClustered;
// if (s.serverVersion == "") s.serverVersion = SSRPRequest.serverVersion;
// if (s.namedPipe == "") s.namedPipe = SSRPRequest.namedPipe;
// }
}
}
catch (Exception ex)
{
Program.logDiagnostic("SSRP Parser: Problem parsing frame " + fd.frameNo + " in file " + fd.file.filePath + ".");
Program.logDiagnostic(ex.Message);
}
}
}
} // Process UDP
public static void ParseSSRPResponse(String ssrpResponse, SSRPData SSRPRequest, NetworkTrace trace)
{
if (ssrpResponse.Length <= 0)
{
return;
}
//the client either (i) sends a single request to a specific machine and expects a single response, or
//(ii) broadcasts or multicasts a request to the network and expects zero or more responses from different
//discovery services on the network - Page# 30 in SSRP Specs.
//Response can contain more than one server informaton.
//Each server info separated by ;;
String[] Servers = ssrpResponse.Split(new string[] { ";;" }, StringSplitOptions.None);
foreach (var Server in Servers)
{
String[] Tokens = Server.Split(';');
SSRPRequest.sqlHostName = GetUDPToken(Tokens, "ServerName");
SSRPRequest.instanceName = GetUDPToken(Tokens, "InstanceName");
SSRPRequest.isClustered = GetUDPToken(Tokens, "IsClustered");
SSRPRequest.serverVersion = GetUDPToken(Tokens, "Version");
SSRPRequest.namedPipe = GetUDPToken(Tokens, "np");
string portString = GetUDPToken(Tokens, "tcp");
SSRPRequest.sqlPort = portString.Length > 0 ? Convert.ToUInt16(portString) : (ushort)0;
if (SSRPRequest.sqlPort != 0)
{
SQLServer s = trace.GetSQLServer(SSRPRequest.sqlIP,
SSRPRequest.sqlIPHi,
SSRPRequest.sqlIPLo,
SSRPRequest.sqlPort,
SSRPRequest.isIPV6);
if (s != null)
{
if (s.sqlHostName == "")
{
s.sqlHostName = SSRPRequest.sqlHostName;
}
if (s.instanceName == "")
{
s.instanceName = SSRPRequest.instanceName;
}
if (s.isClustered == "")
{
s.isClustered = SSRPRequest.isClustered;
}
if (s.serverVersion == "")
{
s.serverVersion = SSRPRequest.serverVersion;
}
if (s.namedPipe == "")
{
s.namedPipe = SSRPRequest.namedPipe;
}
}
}
}
}
public static void ProcessUDP(NetworkTrace trace)
{
string[] DnsReturnMessage = new string[] {
"Success",
"Format error; DNS server did not understand the update request.",
"DNS server encountered an internal error, such as a forwarding timeout.",
"A name that should exist does not exist.",
"DNS server does not support the specified Operation code.",
"DNS server refuses to perform the update.",
"A name that should not exist does exist.",
"A resource record set that should not exist does exist.",
"A resource record set that should exist does not exist.",
"DNS server is not authoritative for the zone named in the Zone section.",
"A name used in the Prerequisite or Update sections is not within the zone specified by the Zone section.",
"Invalid return code.",
"Invalid return code.",
"Invalid return code.",
"Invalid return code.",
"Reserved."
};
foreach (ConversationData c in trace.conversations)
{
//DNS traffic is over UDP. If the current converstion is not UDP then skip that non DNS conversation.
if (!c.isUDP)
{
continue;
}
//Skip the conversation, if its just UDP, but not DNS
if ((c.isUDP) && c.destPort != 53)
{
continue;
}
trace.DNSRequestCount++;
try
{
//Parse the DNS frames of the conversation.
foreach (FrameData fd in c.frames)
{
DNS DNSresponse = new DNS();
if (fd.payloadLength < 1)
{
continue;
}
//DNS data starts at 42nd byte of the total payload. so the payload = (Total Payload - 42) bytes.
TDSReader ByteReader = new TDSReader(fd.payload, 0, -1, fd.payloadLength);
ByteReader.ReadBigEndianUInt16(); //Skip over the query ID.
//Read the Flags and convert into bytes.
int FlagsHigh = ByteReader.ReadByte();
int FlagsLow = ByteReader.ReadByte();
if ((FlagsHigh & (int)0x80) == (int)0x0) // DNS request
{
//DNSresponse.srcServerIP = c.sourceIP.ToString();
DNSresponse.dnsServerIP = c.destIP.ToString();
}
else if ((FlagsHigh & (int)0x80) == (int)0x80) // DNS response
{
int rCode = FlagsLow & 0x0F; // should be between 0 - 15.
//DNSresponse.srcServerIP= c.destIP.ToString();
DNSresponse.dnsServerIP = c.sourceIP.ToString();
DNSresponse.frameNo = fd.frameNo;
DNSresponse.TimeStamp = new DateTime(((FrameData)c.frames[c.frames.Count - 1]).ticks).ToString(utility.TIME_FORMAT);
//new DateTime(((FrameData)trace.frames[0]).ticks).ToString(utility.TIME_FORMAT);
DNSresponse.errorCode = rCode;
DNSresponse.ErrorDesc = DnsReturnMessage[rCode];
//Question Count - 2 bytes
DNSresponse.QuestionCount = ByteReader.ReadInt16();
//Answer Count - 2 bytes
DNSresponse.AnswerCount = ByteReader.ReadInt16();
//Skip 2 bytes - Name Server count
ByteReader.ReadInt16();
//Skip 2 bytes - Additional count
ByteReader.ReadInt16();
//Start reading the QName
//13th byte of the DNS Payload - payload[12]
byte length = ByteReader.ReadByte();
string Name = "";
while (length != 0)
{
Name += (Name == "" ? "" : ".") + ByteReader.ReadAnsiString(length);
length = ByteReader.ReadByte();
}
DNSresponse.nameReqested = Name;
DNSresponse.convData = c;
//DNSresponse.srcPort = c.sourcePort;
// Console.WriteLine(fd.file.filePath + "\t" + fd.frameNo.ToString());
if (rCode != (int)DNSReturnCodes.NOERROR)
{
trace.DNSResponses.Add(DNSresponse);
}
}
}
}
catch (Exception ex)
{
Console.WriteLine("Error parsing DNS conversation: " + ex.Message + "\r\n" + ex.StackTrace);
Program.logDiagnostic("Error parsing DNS conversation: " + ex.Message + "\r\n" + ex.StackTrace);
}
}
}
// post processing
public static void ProcessTDS(NetworkTrace trace)
{
foreach (ConversationData c in trace.conversations)
{
long payLoadLength = 0;
int tdsClientSource = 0;
int tdsClientDest = 0;
int tdsServerSource = 0;
int tdsServerDest = 0;
int tdsOtherFrames = 0;
int switchClientServer = 0; // 0 = do not switch; 1+ = switch - only increment if c.hasApplicationData and PostLoginResponse are false
if (c.isUDP)
{
continue;
}
//if (c.sourcePort == 53388 || c.destPort == 53388) // place to put breakpoint when trying to isolate a specifc conversation
// Console.WriteLine();
// bypass client or server port < 500 - the well-known ports
// SQL will not configure itself in this range with Dynamic Ports - uses a port from the ephemeral range
// Well Known Ports: 0 through 1023
// Registered Ports: 1024 through 49151
// Dynamic/Private : 49152 through 65535
// had some customer use ports 1021-1024 for SQL ports - lower limit to avoid common services: HTTP, HTTPS, Kerberos, SMB, etc., all below 500
if ((c.sourcePort < 500) || (c.destPort < 500))
{
continue; // to avoid confusing encrypted traffic on common services with SQL traffic. Override with /SQL command-line switch
}
//TLS//SSL Payload ?
foreach (FrameData fd in c.frames)
{
try
{
// increased < 7 to < 9. to fix exception in TCP ETW *** TODO TODO TODO
// weed out non-TDS packets
//if (fd.payloadLength < 7) continue; // ATTENTION payload is 8 bytes *** should we be < 8 instead ??? *** TODO TODO TODO
if (fd.payloadLength < 9)
{
continue; // ATTENTION payload is 8 bytes
}
// ignore continuation packets until we get a complete TDS message parser built
if (fd.isContinuation)
{
continue;
}
// bypass HTTP
if ((utility.ReadAnsiString(fd.payload, 0, 3).ToUpper() == "GET") ||
(utility.ReadAnsiString(fd.payload, 0, 4).ToUpper() == "POST") ||
(utility.ReadAnsiString(fd.payload, 0, 4).ToUpper() == "HTTP")) // other keywords much less common
{
break; // exit the entire conversation
}
int firstByte = fd.payload[0];
//if (c.sourcePort == 1431) // debugging construct
//{
// if (fd.frameNo == 428)
// Console.WriteLine(fd.frameNo + " " + firstByte + " " + fd.FormatPayloadChars(30));
//}
// make sure we have a supported packet type
if ((firstByte != (int)TDSPacketType.SQLBATCH) && // 1
(firstByte != (int)TDSPacketType.LOGIN) && // 2
(firstByte != (int)TDSPacketType.RPC) && // 3
(firstByte != (int)TDSPacketType.RESPONSE) && // 4
(firstByte != (int)TDSPacketType.ATTENTION) && // 6
(firstByte != (int)TDSPacketType.BULKLOAD) && // 7
(firstByte != (int)TDSPacketType.DTC) && // 14
(firstByte != (int)TDSPacketType.SSPI) && // 17
(firstByte != (int)TDSPacketType.PRELOGIN) && // 18
(firstByte != (int)TDSPacketType.APPDATA)) // 23
{
continue;
}
// read header parts that we are interested in
bool tdsEOM = (fd.payload[1] & 0x1) == 1;
ushort tdsLength = utility.B2UInt16(fd.payload, 2);
// APPDATA does not have a TDS payload, but a TLS payload, so skip these tests if APPDATA
if (firstByte != (int)TDSPacketType.APPDATA)
{
// TDS header Length argument needs to be non-zero and also >= payload length
if (tdsLength == 0 || tdsLength < fd.payloadLength)
{
continue;
}
if (fd.payload[6] > 1)
{
continue; // TDS Continuous Response packets can have greater values, but we are ignoring them right now
}
// TDS window needs to be 0 -- from TDSView -- TODO understand the reason for this - does MARS have non-Zero value?
if (fd.payload[7] != 0)
{
continue;
}
}
switch (firstByte)
{
case (byte)TDSPacketType.PRELOGIN: // can be client or server
{
byte preloginType = fd.payload[8]; // first byte after TDS header = prelogin type
byte handshakeType = 0;
byte sslMajorVersion = 0;
byte sslMinorVersion = 0;
if (preloginType == 0) // Prelogin packet
{
GetClientPreloginInfo(fd.payload, fd.conversation);
c.hasPrelogin = true;
if (fd.isFromClient)
{
tdsClientSource++; // looks like SQL is on the destIP side - good
}
else
{
if (c.hasApplicationData == false && c.hasPostLoginResponse == false)
{
switchClientServer++;
}
tdsClientDest++; // looks like SQL is on the sourceIP side - need to switch later
}
}
else if (preloginType == 0x16) // SSL handshake
{
sslMajorVersion = fd.payload[9]; // first byte after preLogintype
sslMinorVersion = fd.payload[10]; // next byte
handshakeType = fd.payload[13]; // first byte after SSL header = SSL handshake type
if (handshakeType == 1 || handshakeType == 0x10) // Client Hello or Client Key Exchange
{
if (handshakeType == 1)
{
c.hasClientSSL = true;
c.tlsVersionClient = translateSSLVersion(sslMajorVersion, sslMinorVersion);
if (sslMajorVersion != 3 || sslMinorVersion != 3)
{
c.hasLowTLSVersion = true; // mark anything other than TLS 1.2
}
}
if (handshakeType == 0x10)
{
c.hasKeyExchange = true;
}
if (fd.isFromClient)
{
tdsClientSource++; // looks like SQL is on the destIP side - good
}
else
{
if (c.hasApplicationData == false && c.hasPostLoginResponse == false)
{
switchClientServer++;
}
tdsClientDest++; // looks like SQL is on the sourceIP side - need to switch later
}
}
else if (handshakeType == 2) // Server Hello -- do we sometimes hit here, or is it just in the TDS RESPONSE version of this logic
{
Program.logDiagnostic($"Non-Response Server Hello packet seen in file {fd.file} and frame {fd.frameNo}.");
c.hasServerSSL = true;
c.tlsVersionServer = translateSSLVersion(sslMajorVersion, sslMinorVersion);
if (sslMajorVersion != 3 || sslMinorVersion != 3)
{
c.hasLowTLSVersion = true; // mark anything other than TLS 1.2
}
if (fd.isFromClient)
{
tdsServerSource++; // looks like SQL is on the sourceIP side - need to switch later
}
else
{
tdsServerDest++; // looks like SQL is on the destIP side - good
}
}
}
else if (preloginType == 0x14) // Cipher exchange - could be client or server
{
c.hasCipherExchange = true;
tdsOtherFrames++; // since could be client or server
}
//accumulate the payload.
payLoadLength += fd.payload.Length;
break;
}
case (byte)TDSPacketType.APPDATA: // 0x17 = Application data
{
c.hasApplicationData = true;
tdsOtherFrames++; // since could be client or server
//accumulate the payload.
payLoadLength += fd.payload.Length;
break;
}
case (byte)TDSPacketType.LOGIN:
{
//accumulate the payload. *** normally, we should not see this packet unencrypted ***
c.hasLogin7 = true;
payLoadLength += fd.payload.Length;
if (fd.isFromClient)
{
tdsClientSource++; // looks like SQL is on the destIP side - good
}
else
{
tdsClientDest++; // looks like SQL is on the sourceIP side - need to switch later
}
break;
}
case (byte)TDSPacketType.SSPI:
{
c.hasIntegratedSecurity = true;
//accumulate the payload.
payLoadLength += fd.payload.Length;
// Check for NTLM Response Message - TODO if this isn't true, do we abort the TDS parsing for this conversation?
if (fd.payloadLength > 16)
{
if ((utility.ReadAnsiString(fd.payload, 8, 7) == "NTLMSSP") && // NTLM signature
(fd.payload[15] == 0) && // null terminated
(fd.payload[16] == 3)) // Type = Authenticate Message
{
c.hasNTLMResponse = true;
//Parse User name and domain name
//check are they both 0 length? if yes, set a flag in conversation data that indicates null credentials
c.hasNullNTLMCreds = AreTheCredentialsNull(fd);
if (fd.isFromClient == false && c.hasApplicationData == false && c.hasPostLoginResponse == false)
{
switchClientServer++;
}
}
}
if (fd.isFromClient)
{
tdsClientSource++; // looks like SQL is on the destIP side - good
}
else
{
tdsClientDest++; // looks like SQL is on the sourceIP side - need to switch later
}
break;
}
case (byte)TDSPacketType.RPC:
case (byte)TDSPacketType.SQLBATCH:
case (byte)TDSPacketType.DTC:
{
//accumulate the payload.
payLoadLength += fd.payload.Length;
c.hasPostLoginResponse = true; // if we're doing this, login has already succeeded
/*******
* if (firstByte == (int)TDSPacketType.SQLBATCH)
* {
*
* int Length = (int) utility.ReadUInt32(fd.payload, 2);
*
* }
**************/
if (fd.isFromClient)
{
tdsClientSource++; // looks like SQL is on the destIP side - good
}
else
{
tdsClientDest++; // looks like SQL is on the sourceIP side - need to switch later
}
break;
}
case (byte)TDSPacketType.ATTENTION: // added Dec 5, 2016
{
//accumulate the payload.
payLoadLength += fd.payload.Length;
c.hasPostLoginResponse = true; // if we're doing this, login has already succeeded
c.AttentionTime = fd.ticks;
if (fd.isFromClient)
{
tdsClientSource++; // looks like SQL is on the destIP side - good
}
else
{
tdsClientDest++; // looks like SQL is on the sourceIP side - need to switch later
}
break;
}
case (byte)TDSPacketType.RESPONSE: //0x4
{
// process error responses
if (fd.payload[8] == (byte)TDSTokenType.ERROR)
{
c.Error = utility.ReadUInt32(fd.payload, 11);
c.ErrorState = fd.payload[15];
int ErrorLen = (int)fd.payload[17];
c.ErrorMsg = utility.ReadUnicodeString(fd.payload, 19, ErrorLen);
}
//pre-login info from Server.
// if (tokenOffset(fd.payload, (byte)TDSTokenType.PRELOGINRESPONSE) > 7) // response header is offset 0..7 - need to fix this routine
else if (fd.payload[8] == (byte)TDSTokenType.PRELOGINRESPONSE) // only 1 token in the payload
{
GetServerPreloginInfo(fd.payload, fd.conversation);
c.hasPreloginResponse = true;
if (fd.isFromClient && c.hasApplicationData == false && c.hasPostLoginResponse == false)
{
switchClientServer++;
}
}
else if (fd.payload[8] == 0x16 && fd.payloadLength > 10) // SSL
{
byte preloginType = fd.payload[8]; // first byte after TDS header = prelogin type
byte handshakeType = fd.payload[13]; // first byte after SSL header = SSL handshake type
byte sslMajorVersion = fd.payload[9]; // first byte after preLogintype
byte sslMinorVersion = fd.payload[10]; // next byte
if (handshakeType == 2) // Server Hello
{
c.hasServerSSL = true;
c.tlsVersionServer = translateSSLVersion(sslMajorVersion, sslMinorVersion);
if (sslMajorVersion != 3 || sslMinorVersion != 3)
{
c.hasLowTLSVersion = true; // mark anything other than TLS 1.2
}
if (fd.isFromClient)
{
tdsServerSource++; // looks like SQL is on the sourceIP side - need to switch later
}
else
{
tdsServerDest++; // looks like SQL is on the destIP side - good
}
}
}
else if ((fd.payloadLength > 19) &&
(fd.payload[8] == (byte)TDSTokenType.SSPI) && // NTLM Challenge message
(utility.ReadAnsiString(fd.payload, 11, 7) == "NTLMSSP") && // NTLM signature
(fd.payload[18] == 0) && // null terminated
(fd.payload[19] == 2)) // type = Challenge Message
{
c.hasNTLMChallenge = true;
if (fd.isFromClient == false && c.hasApplicationData == false && c.hasPostLoginResponse == false)
{
switchClientServer++;
}
}
else if ((tokenOffset(fd.payload, (byte)TDSTokenType.ENVCHANGE) > 7) && // response header is offset 0..7
(tokenOffset(fd.payload, (byte)TDSTokenType.INFO) > 7) &&
(tokenOffset(fd.payload, (byte)TDSTokenType.LOGINACK) > 7))
{
c.hasPostLoginResponse = true;
c.LoginAckTime = fd.ticks;
try
{
// parse LoginAck packet
int offset = tokenOffset(fd.payload, (byte)TDSTokenType.LOGINACK);
c.tdsVersionServer = utility.B2UInt32(fd.payload, offset + 4);
int nameLength = fd.payload[offset + 8] * 2; // unicode characters
c.serverVersion = fd.payload[offset + nameLength + 9] + "." + fd.payload[offset + nameLength + 10] + "." +
fd.payload[offset + nameLength + 11] + "." + fd.payload[offset + nameLength + 12];
// parse Info packet - any one, doesn't matter, all have the database name in them
offset = tokenOffset(fd.payload, (byte)TDSTokenType.INFO);
offset += utility.ReadUInt16(fd.payload, offset + 9) * 2; // unicode characters - and 2-byte length - skip this
nameLength = fd.payload[offset + 11]; // unicode characters
c.serverName = utility.ReadUnicodeString(fd.payload, offset + 12, nameLength); // arg is chars not bytes
// parse ENVCHANGE packet
offset = tokenOffset(fd.payload, (byte)TDSTokenType.ENVCHANGE);
int EnvChangeType = 0;
int tokenLength = 0;
while (offset != -1)
{
tokenLength = utility.ReadUInt16(fd.payload, offset + 1);
EnvChangeType = fd.payload[offset + 3];
if (EnvChangeType == 1) // database name
{
nameLength = fd.payload[offset + 4];
c.databaseName = utility.ReadUnicodeString(fd.payload, offset + 5, nameLength);
}
else if (EnvChangeType == 0x14) // server redirection
{
c.RedirectPort = utility.ReadUInt16(fd.payload, offset + 7);
int ServerLen = fd.payload[offset + 9];
c.RedirectServer = utility.ReadUnicodeString(fd.payload, offset + 11, ServerLen);
c.hasReadOnlyIntentConnection = true;
}
offset = tokenOffset(fd.payload, (byte)TDSTokenType.ENVCHANGE, offset + tokenLength + 3);
}
}
catch (IndexOutOfRangeException ex)
{
Program.logDiagnostic("Index out of range exception in file " + trace.files.IndexOf(fd.file) + ", frame " + fd.frameNo + " parsing LoginAck token.");
if (tdsEOM && tdsLength > fd.payloadLength) // truncated packet
{
Program.logDiagnostic("Payload length read = " + fd.payloadLength + ". TDS length = " + tdsLength);
}
}
finally
{
if (fd.isFromClient == false && c.hasApplicationData == false && c.hasPostLoginResponse == false)
{
switchClientServer++;
}
}
}
//accumulate the payload.
payLoadLength += fd.payload.Length;
if (fd.isFromClient)
{
tdsServerSource++; // looks like SQL is on the sourceIP side - need to switch later
}
else
{
tdsServerDest++; // looks like SQL is on the destIP side - good
}
break;
}
case (byte)TDSPacketType.INFO: //0xAB
{
break;
}
} // end switch
} // end try
catch (IndexOutOfRangeException ex)
{
Program.logDiagnostic("Index out of range exception in file " + trace.files.IndexOf(fd.file) + ", frame " + fd.frameNo + ".");
}
catch (Exception ex)
{
Program.logDiagnostic("Exception in file " + trace.files.IndexOf(fd.file) + ", frame " + fd.frameNo + ". \n\r" + ex.Message);
}
} // end for each frame in conversation.frames
//Enable hasTDS flag and isSQL flag - they are FALSE by default
// Ignore tdsOtherFrames because pure SSL conversations will have 0x14 and 0x16 packet types and show as false positive
// We may lose a couple of SQL Servers with one fragemented conversation, but they are not likely of interest
int KeyFrameCount = (c.hasPrelogin ? 1 : 0)
+ (c.hasPreloginResponse ? 1 : 0)
+ (c.hasClientSSL ? 1 : 0)
+ (c.hasServerSSL ? 1 : 0)
+ (c.hasKeyExchange ? 1 : 0)
+ (c.hasCipherExchange ? 1 : 0)
+ (c.hasApplicationData ? 1 : 0)
+ (c.hasPostLoginResponse ? 1 : 0);
//
// If we see the beginning of the conversation, but there are not enough key frames, then very strong % not SQL.
// If the number of SQL packets is < 2% of the overall frame count, then not likely SQL. Can adjust this; 1% might be okay, too.
// There is a possibility we're wrong, but other conversations should correct that.
//
if ((c.synCount > 0 && KeyFrameCount < 3) || ((tdsClientSource + tdsServerDest + tdsServerSource + tdsClientDest) * 50) < c.frames.Count)
{
tdsClientSource = tdsServerDest = tdsServerSource = tdsClientDest = 0; // short-cut this option for determining if we have a SQL conversation
}
if (KeyFrameCount > 4) // we're pretty sure this is a SQL Server
{
c.hasTDS = true;
c.isSQL = true;
c.tdsFrames = tdsClientSource + tdsServerSource + tdsClientDest + tdsServerDest + tdsOtherFrames;
}
else if (((tdsClientSource + tdsServerDest) > 0) && ((tdsServerSource + tdsClientDest) == 0) ||
((tdsServerSource + tdsClientDest) > 0) && ((tdsClientSource + tdsServerDest) == 0)) // we're sort of sure
{
c.hasTDS = true;
c.isSQL = true;
c.tdsFrames = tdsClientSource + tdsServerSource + tdsClientDest + tdsServerDest + tdsOtherFrames;
}
else
{
c.hasTDS = false;
c.isSQL = false;
}
// based on the accumulated TDS flags, determine whether we need to switch client and server IP addresses and ports to make SQl on the destination side
if (((tdsServerDest + tdsClientSource) > 0) && ((tdsClientDest + tdsServerSource) == 0))
{
// SQL IP and port is the conversation destination IP and destination Port
// do nothing
}
else if (((tdsClientDest + tdsServerSource) > 0) && ((tdsServerDest + tdsClientSource) == 0))
{
// SQL IP and port number are on the source side of the conversation
// Reverse everything so it goes on the destination side
reverseSourceDest(c);
}
else if (switchClientServer > 0) // based on key frames we parse more completely
{
reverseSourceDest(c);
}
else if (((tdsClientDest + tdsServerSource) > 0) && ((tdsServerDest + tdsClientSource) > 0))
{
if (trace.FindSQLServer(c) != null)
{
c.hasTDS = true;
c.isSQL = true;
c.tdsFrames = tdsClientSource + tdsServerSource + tdsClientDest + tdsServerDest + tdsOtherFrames;
}
// this is a parsing issue - we should log regardless
Program.logDiagnostic("*** TDS conversation with SQL on the both sides. Look into this ***");
Program.logDiagnostic(c.ColumnData());
Program.logDiagnostic();
Program.logDiagnostic("CD: " + tdsClientDest.ToString() + "; SS:" + tdsServerSource.ToString() + "; SD:" + tdsServerDest.ToString() + "; CS:" + tdsClientSource.ToString());
Program.logDiagnostic();
}
// add the identified SQL Server to the collection and add the conversation to the server conversations
if (c.isSQL)
{
SQLServer Server = trace.GetSQLServer(c.destIP, c.destIPHi, c.destIPLo, c.destPort, c.isIPV6);
Server.conversations.Add(c);
if (Server.serverVersion == "" && c.serverVersion != null)
{
Server.serverVersion = c.serverVersion;
}
if (Server.sqlHostName == "" && c.serverName != null)
{
Server.sqlHostName = c.serverName;
}
}
} // for each conversation
}
public static void Process(NetworkTrace trace)
{
trace.DomainControllers = new System.Collections.ArrayList();
DomainController d = null;
// Locate TCP and UDP conversations with server ports 53 (DNS) and 88 (KERBEROS) and 389 (LDAP)
foreach (ConversationData c in trace.conversations)
{
if (c.destPort == 53 /* DNS */ || c.destPort == 88 /* Kerberos */ || c.destPort == 389 /* LDAP */)
{
d = trace.GetDomainController(c.destIP, c.destIPHi, c.destIPLo, c.isIPV6);
if (c.destPort == 53)
{
d.DNSPort53Count++;
}
if (c.destPort == 88)
{
d.KerbPort88Count++;
}
if (c.destPort == 389)
{
d.LDAPPort389Count++;
}
}
}
// Find any stray conversations with the DC
foreach (ConversationData c in trace.conversations)
{
d = trace.FindDomainController(c);
if (d != null)
{
d.conversations.Add(c);
}
}
// Find MSRPC Conversations and Port
foreach (DomainController dc in trace.DomainControllers)
{
foreach (ConversationData c in dc.conversations)
{
if (c.isUDP == false && c.destPort > 1000) // ignore low port #s
{
// potential MSRPC traffic
foreach (FrameData f in c.frames)
{
ushort Port = c.destPort;
if (isMSRPC(f.payload))
{
dc.MSRPCPortCount++;
if (dc.MSRPCPort == 0)
{
dc.MSRPCPort = Port;
}
else if (dc.MSRPCPort != Port)
{
dc.hasMultipleMSRPCPorts = true;
}
break;
}
}
}
}
}
}