// 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);
}
}
}
}
}
public SQLServer GetSQLServer(uint IP, ulong IPHi, ulong IPLo, ushort Port, bool isIPV6)
{
// search for existing SQL Server and return it - can use foreach loop as this is not called often and there are a small number of entries
foreach (SQLServer s in sqlServers)
{
if (s.isIPV6 == isIPV6 && s.sqlIP == IP && s.sqlIPHi == IPHi && s.sqlIPLo == IPLo && s.sqlPort == Port)
{
return(s);
}
}
// not found - create new SQLServer and return it
SQLServer s2 = new SQLServer();
s2.sqlIP = IP;
s2.sqlIPHi = IPHi;
s2.sqlIPLo = IPLo;
s2.sqlPort = Port;
sqlServers.Add(s2);
return(s2);
}
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;
}
}
}
}
}
// 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
}