As majority of folks may know time is counting down on SQL Server extended support for SQL Server 2005 , it officially ends April 12, 2016. Working with folks on upgrading to fairly old systems from SQL Server 2000\2005 to newer versions like SQL Server 2012\2014 we ran into fairly rare issue of TDS backward compatibility, mainly with very old clients running older versions of JDBC\ODBC drivers.
Tabular Data Stream (TDS) is an application layer protocol, used to transfer data between a database server and a client. It was initially designed and developed by Sybase Inc. for their Sybase SQL Server relational database engine in 1984, and later by Microsoft in Microsoft SQL Server
Microsoft published TDS specification in 2008 – https://msdn.microsoft.com/en-us/library/cc448435.aspx , Still TDS and especially TDS compatibility on SQL Server upgrades are pretty obscure topic. Microsoft published TDS version list matched to SQL Server and .NET Framework here – https://msdn.microsoft.com/en-us/library/dd339982.aspx
|TDS version||SQL Server version||.NET Framework version|
|7.0||SQL Server 7.0||.NET Framework 1.1|
|7.1||SQL Server 2000||.NET Framework 1.1|
|7.1 Revision 1||SQL Server 2000 SP1||.NET Framework 1.1|
|7.2||SQL Server 2005||.NET Framework 2.0|
|7.3.A||SQL Server 2008||.NET Framework 4.0|
|7.3.B||SQL Server 2008 R2||N/A|
|7.4||SQL Server 2012||.NET Framework 4.5|
|7.4||SQL Server 2014||.NET Framework 4.5|
|7.4||SQL Server 2016 CTP2||.NET Framework 4.6|
Although TDS compatibility isn’t well documented by Microsoft , from what I have experienced it appears Microsoft keeps at least two versions of TDS backwardly compatible.
So how do I find out what versions of TDS are connecting to your server? On newer versions like SQL Server 2005 and above where DMVs are present following query can be used:
select protocol_type, protocol_version from sys.dm_exec_connections
Result will be something like:
Well, the big number still doesn’t say which version of data access driver, does it? Actually it does. The integer value is the representation of TDS version (for TSQL). It needs to be translated as follows, converting protocol version to Hex:
SELECT CONVERT(BINARY(4), 1930035203)
Result will be as follows:
Take the first two hex digits (0x73) and map it to correct SQL Server version based on the table above. So now we know that connection was made via TDS 7.3x , possibly via SQL Server 2008 Client stack.
In one of its older posts SQLCAT actually combined al;l of these steps into one query:
SELECT session_id, protocol_type, driver_version = CASE SUBSTRING(CAST(protocol_version AS BINARY(4)), 1,1) WHEN 0x70 THEN ‘SQL Server 7.0′ WHEN 0x71 THEN ‘SQL Server 2000′ WHEN 0x72 THEN ‘SQL Server 2005′ WHEN 0x73 THEN ‘SQL Server 2008′ ELSE ‘Unknown driver’ END FROM sys.dm_exec_connections
But what if I am on SQL Server 2000, well before DMVs were available and need to check my TDS versions connecting before upgrade?
Here things will become a lot more difficult and labor intensive. The only way I think is via checking version in TDS pre-login packet via network capture on SQL Server side.
As per – https://msdn.microsoft.com/en-us/library/dd357559.aspx pre-login is message sent by the client to set up context for login. The server responds to a client PRELOGIN message with a message of packet header type 0x04 and the packet data containing a PRELOGIN structure. As part there is optional version value:
|VERSION||0x00||PL_OPTION_DATA = UL_VERSION
UL_VERSION is represented in network byte order (big-endian).
The server SHOULD use the VERSION sent by the client to the server. The client SHOULD use the version returned from the server to determine which features are enabled or disabled. The client SHOULD do this only if it is known that this feature is supported by that version of the database.<27>
Theoretically upon capture of such data via network trace it can be filtered in wireshark , from – https://www.wireshark.org/docs/dfref/t/tds.html
tds.prelogin.option.version Version Unsigned integer, 4 bytes 2.0.0
According to spec, version is an option – client should provide version by docs, however possibly some clients don’t. Assume that would be pretty rare, at least all mainstream clients I have seen do.
So dealing with older, obscure SQL Server clients is hard, but doesn’t have to impossible.
For more see – https://blogs.msdn.microsoft.com/jenss/2009/03/02/tds-protocol-versions-meet-client-stacks/, https://blogs.msdn.microsoft.com/sql_protocols/2008/07/15/connection-failure-because-of-mismatched-tds-version/, https://msdn.microsoft.com/en-us/library/dd339982.aspx