I've been playing around with the low level SQL Server network protocols. These are officially known as [
MS-TDS] Tabular Data Stream Protocol and
[MC-SQLR] SQL Server Resolution Protocol and are fully documented on the
Microsoft Protocols web site. Unfortunately, like every other protocol and RFC I've ever read, comprehension is not easy.
This interest mainly comes from wanting to understand the
SQLPing utility written by
Chip Andrews. To put is simply, SQLPing
nerd sniped me. I've known about and used this little utility on a number of occasions, but I never had the chance to dig into it. After playing with the code and reading over the protocol, I wanted to share and translate tech-speak into something human friendly. This particular post is going to cover the
PRELOGIN message in MS-TDS. I'll cover MC-SQLR in a separate post.
The SQL Handshake
When connecting to a SQL Server, the first thing that happens after connecting is a
PRELOGIN exchange. This happens before any authentication or encryption. It's a knock on the door, which allows the SQL Server and the client to check each other out to see if they are compatible with each other. If the SQL Server can't understand what you send, it kills the connection right then and there - long before any authentication is attempted.
After a successful
PRELOGIN, the client can attempt a second
PRELOGIN for wrapping a SSL/TLS handshake to start encrypting the connection. Regardless, after the
PRELOGIN, the connection is authenticated using
LOGIN7. Finally, if everything has been successful, the client can begin to execute SQL statements.
Microsoft was kind enough to provide a
flowchart, as well, for anyone interested in how the connection is fully negotiated.
The PRELOGIN Request & Response
The structure of the packet is a fairly simple binary format. It can be broken down into three main parts. First the packet header, next the token list, and finally the token data. The tokens are data containing a combination of compatibility and debug information.
One of those tokens is a version numbers. For the client, the driver version is sent. For the server, the
product version is returned. In fact, besides the underlying packet structure, the version is the minimum amount of information that must be sent between client and server.
Here is a bare-bones example of a
PRELOGIN request packet in CSharp. It contains the minimum amount of information required to get a response from the server.
var request = new byte[]
{
// Start Packet Header // 0x00 - 0x08
0x12, // PRELOGIN packet header
0x01, // Status (EOM) (last packet in message)
0x00,0x14, // Packet Length (uint16)
0x00,0x00, // Spid (uint16)
0x00, // Packet (uint8)
0x00, // Window (ignored; always 0x00)
// End Packet Header
// Start Token List // 0x09 - 0x0e
0x00, // VERSION token
0x00,0x06, // Offset Position (from Start Data Header)
0x00,0x06, // Data Length (uint32 + uint16)
0xff, // TERMINATOR token
// End Token List
// Start Data Content // 0x0f - 0x14
0x08,0x00,0x01,0x55, // UL_VERSION (driver 8.00.341)
0x00,0x00, // US_SUBBUILD (always zero)
// End Data Content
};
Likewise, here is a bare-bones example of a
PRELOGIN response packet in C#. In fact, if you compare the request and the response packet, they contain the same structure and data, although the version numbers and packet type are different.
var response = new byte[]
{
// Start Packet Header // 0x00 - 0x08
0x04, // PRELOGIN response packet header
0x01, // Status (EOM) (last packet in message)
0x00,0x14, // Packet Length (uint16)
0x00,0x00, // Spid (uint16)
0x01, // Packet (uint8)
0x00, // Window (ignored; always 0x00)
// End Packet Header
// Start Token List // 0x09 - 0x0e
0x00, // VERSION token
0x00,0x06, // Offset Position (from Start Data Header)
0x00,0x06, // Data Length (uint32 + uint16)
0xff, // TERMINATOR token
// End Token List
// Start Token Data // 0x0f - 0x14
0x0c, 0x00, 0x07, 0xd0, // UL_VERSION (server version 12.00.2000)
0x00, 0x00 // US_SUBBUILD (always zero)
// End Token Data
};
The Packet Header
The packet header contains six pieces of information, stored in eight bytes.
- Type - The type of message being sent. In the case of PRELOGIN, the client will always send 0x12 (PRELOGIN) and the server will always send 0x04 (TABULAR RESULT).
- Status - The state of the message. In this case, both the client and server will always send 0x01 (EOM or end of message), meaning this is the last packet in the message.
- Length - The length of the packet - as an unsigned short - from the start of the packet header to the end of the token data.
- SPID - The session id for the connection - as an unsigned short. In this case, because a session id is not assigned until after authentication occurs, this will always be zero.
- PacketID - The index of the packet in the message. For messages spread across multiple packets, this would be incremented for each packet sent (mod 255). In this case, since we are only sending an receiving a single packet, this will always be zero. This is largely unused and can be ignored by always setting to zero.
- Window - Unused. This will always be zero.
The Token Header
The token header contains two or more tokens, as well as descriptive information about the tokens. For the
PRELOGIN packet, there are a total of nine different tokens supported. Actually, it's probably better to say there are eight tokens and a
TERMINATOR "token", which marks the end of the list of tokens. Besides the
TERMINATOR, each token is followed by an offset and a length. The token itself is a single byte, while the offset and length are both unsigned shorts.
The offset contains the starting point in the packet body - ignoring the packet header - where the data is stored. In the example above, the
VERSION data is stored at 0x0006, which is six bytes after the first token. I'm assuming this, but this would allow a more efficient coding in the network library by separating reading the packet header from the packet body. The packet header will always be eight bytes, while the remainder of the packet will vary.
Also, because the
VERSION token is the first token in packet, the data will also be the first after the
TERMINATOR. This makes the version fairly easy to find since it will be the first six bytes after the 0xFF.
The length contains the number of bytes used by the token data and can be zero. In the example above, the
VERSION data occupies six bytes (0x0006) in the token data block.
- VERSION - The version and the only required token, other than the terminator. For the client, this represents the driver version, while on the server, it represents the product version. The version is stored across as a unsigned long, followed by an unsigned short. More acccurately, it is stored in the form of major version (byte), minor version (byte), build (unsigned short), minor build (unsigned short, always 0x0000).
- ENCRYPTION - Contains one of four values. This setting is used in the negotiation between the client and the server to determine if a SSL/TLS handshake will follow the PRELOGIN. In this case, the value is not important, but the best setting to use is ENCRYPT_OFF (0x00), since this will return the server's ENCRYPTION setting.
- INSTOPT - For the client, this represents a NUL terminated string containing the instance name of the server. For the server, it is a booleaning representing a success (0x00) or failure (0x01) whether the instance name passed by the client matches the one on the server.
- THREADID - An unsigned long used for debug purposes, representing the client application thread.
- MARS - A boolean to enable or disable Multiple Active Result Sets. According to the people at FreeTDS, there is No Earthly Reason for MARS. I won't argue. It's not relevant for this discussion, anyway and only works on SQL Server 2005 or greater when using SQL Native Client drivers or the ODBC driver for SQL Server 11.
- TRACEID - A 16 byte GUID, followed by a 20 byte Activity Id used for debug purposes. I have only seen this token when using SQL Native Client drivers. Again, not relevant for this discussion.
- FEDAUTHREQUIRED - A boolean to enable (0x01) or disable (0x00) Federated Authentication. This is later used in the authentication process. I'm not sure what this is; but it's not relevant for this discussion.
- NONCEOPT - A 32 bit nonce used with Federated Authentication.
- TERMINATOR - A single byte containing 0xFF. This represents the end of the token header and the beginning of the token data. Unlike other tokens, it is not followed by an offset or a length.
Additional Examples
var response1 = new byte[] // Four token response
{
// Start Packet Header // 0x00 - 0x08
0x04, // PRELOGIN response packet header
0x01, // Status (EOM) (last packet in message)
0x00,0x25, // Packet Length (UInt16)
0x00,0x00, // Spid (uint16)
0x01, // Packet (uint8)
0x00, // Window (ignored; always 0x00)
// End Packet Header
// Start Token List // 0x09 - 0x14
0x00, // VERSION token
0x00,0x15, // Data Offset (UInt16) (from Start Data Header)
0x00,0x06, // Data Length (UInt16)
0x01, // ENCRYPTION token
0x00,0x1b, // Data Offset
0x00,0x01, // Data Length
0x02, // INSTOPT token
0x00,0x1c, // Data Offset
0x00,0x01, // Data Length
0x03, // THREADID token
0x00,0x1d, // Data Offset
0x00,0x00, // Data Length
0xff, // TERMINATOR token
// End Token List
// Start Token Data // 0x15 - 0x25
0x08, 0x00, 0x07, 0xf7, // UL_VERSION (08.00.2039)
0x00, 0x00, // US_SUBBUILD (always zero)
0x00, // ENCRYPTION (ENCRYPT_OFF)
0x00 // INSTOPT (SUCCESS)
// End Token Data
};
var response = new byte[] // Five token response
{
// Start Packet Header // 0x00 - 0x08
0x04, // PRELOGIN response packet header
0x01, // Status (EOM) (last packet in message)
0x00,0x30, // Packet Length (UInt16)
0x00,0x00, // Spid (uint16)
0x01, // Packet (uint8)
0x00, // Window (ignored; always 0x00)
// End Packet Header
// Start Token List // 0x09 - 0x26
0x00, // VERSION token
0x00,0x1f, // Data Offset (UInt16) (from Start Data Header)
0x00,0x06, // Data Length (UInt16)
0x01, // ENCRYPTION token
0x00,0x25, // Data Offset
0x00,0x01, // Data Length
0x02, // INSTOPT token
0x00,0x26, // Data Offset
0x00,0x01, // Data Length
0x03, // THREADID token
0x00,0x27, // Data Offset
0x00,0x00, // Data Length
0x04, // MARS token
0x00,0x27, // Data Offset
0x00,0x01, // Data Length
0x05, // TRACEID token
0x00,0x28, // Data Offset
0x00,0x00, // Data Length
0xff, // TERMINATOR token
// End Token List
// Start Token Data // 0x27 - 0x30
0x0c, 0x00, 0x07, 0xd0, // UL_VERSION (12.00.2000)
0x00, 0x00, // US_SUBBUILD (always zero)
0x00, // ENCRYPTION (ENCRYPT_OFF)
0x00, // INSTOPT (SUCCESS)
0x00 // MARS (OFF)
// End Token Data
};
Example CSharp to Retrieve the SQL Product Version
On to the most useful bit in this post. The minimal set of code to retrieve the product version of the SQL Server. The only requirement is knowing the IP address and port for the SQL Server.
using System; // Exception
using System.Net; // IPAddress
using System.Net.Sockets; // TcpClient, NetworkStream
namespace Example
{
public static class SqlClient
{
public static Version Prelogin(IPAddress address, int port)
{
var data = new byte[]
{
// Start Packet Header // 0x00 - 0x08
0x12, // PRELOGIN packet header
0x01, // Status (EOM) (last packet in message)
0x00,0x14, // Packet Length (uint16)
0x00,0x00, // Spid (uint16)
0x00, // Packet (uint8)
0x00, // Window (ignored; always 0x00)
// End Packet Header
// Start Data Header // 0x09 - 0x0e
0x00, // VERSION token
0x00,0x06, // Offset Position (from Start Data Header)
0x00,0x06, // Data Length (uint32 + uint16)
0xff, // TERMINATOR token
// End Data Header
// Start Data Content // 0x0f - 0x14
0x08,0x00,0x01,0x55, // UL_VERSION (8.00.341)
0x00,0x00, // US_SUBBUILD (always zero)
// End Data Content
};
using (var client = new TcpClient())
{
client.Connect(address, port);
using (NetworkStream stream = client.GetStream())
{
var bytesread = 0;
var bytespending = 0;
stream.Write(data, 0, data.Length);
// Read the response header
var responseheader = new byte[8];
bytesread = stream.Read(responseheader, 0, responseheader.Length);
// Double check to ensure the header contains useful data
if (bytesread != 8)
throw new Exception("A header must be 8 bytes.");
if (responseheader[0] != 0x04)
throw new Exception("A header must start with 0x04");
// Read the response body
bytespending = (responseheader[2] << 8) + responseheader[3] - 8;
var responsebody = new byte[bytespending];
bytesread = stream.Read(responsebody, 0, responsebody.Length);
// Double check to ensure the body contains useful data
if (bytesread != bytespending)
throw new Exception("The header bytes must match packet size.");
if (responsebody[0] != 0)
throw new Exception("The VERSION token must be the first token.");
var offset = (responsebody[1] << 8) + responsebody[2];
if (offset + 6 > bytesread)
throw new Exception("The VERSION data starts or ends after the packet.");
if (responsebody[3] != 0 || responsebody[4] != 6)
throw new Exception("The VERSION token must be 6 bytes long.");
return new Version(
responsebody[offset],
responsebody[offset + 1],
((responsebody[offset + 2] << 8) + responsebody[offset + 3])
);
}
}
}
}
}
Update (January 19, 2015): Updated source code using some friendly
code formatting.
