TDS Protocol Versions meet client stacks

  • Article

A colleague (Cornel Sukalla) asked me what the protocol_version in sys.dm_exec_connections is all about. From the output it seems that its an INT presenting the version for the protocol used by the client. With some research and having in mind that those cryptic numbers are only another presentation of the “right” value; I came across the following blog post by Xinwei Hong on the SQL Protocols blog. Basically it talks about the matching between server and client TDS protocol implementation and the compatibility between those. You see that the information of the TDS protocol is done through a hex value which is decrypted to the following values.

 

    1:  SELECT CONVERT(VARBINARY(9),1930035203)

 

Blog

The client protocol is not specific for the machine which is connecting, it is specific for the client stack which is creating the TDS stream for the communication. You can see this if you are connecting with either DBLibrary, ODBC, OLEDB or ADO.NET. Therefore you can have different values in the management view for the protocol_version per machine, depending on the client stack being used. The following table shows a sample of a machine connecting with different clients:

 

Application Client stack protocol_version Hex version
Common ADO.NET app (like SQLCMD) ADO.NET 1913192450 0x72090002
Common OLEDB app (like Query Analyzer) SQLOLEDB 1895825409 0x71000001
OSQL ODBC 1895825409 0x71000001
ISQL DBLibrary 67239936 0x04020000(uses DBLibrary)

 

The negotiation for the used TDS version for the communication takes place between the client and the server. The entire session will use this protocol version specifying the supported features, functionality and data types available.

A comprehensive list of the implementation and the possible version can be seen here:

https://msdn.microsoft.com/en-us/library/dd339982(PROT.13).aspx

 

-Jens