Denham Coder

A colleague of mine asked me what the protocol_version column represented in the DMV sys.dm_exec_connections and whether it was useful in anyway. The Microsoft documentation does not help too much.

It is actually the version of the TDS Protocol used between client and server. When a client connects to SQL Server, as part of the logon process the version of the TDS protocol is negotiated. If a SQL client with version 2008 is connecting to a SQL Server running 2016 for example; the 2008 TDS version will be used.

The following TSQL can be used to determine the SQL client version. Note: This is valid to SQL 2017.

SELECT CASE CONVERT(VARBINARY(9), protocol_version)
          WHEN 0x07000000 THEN 'SQL 7.0'
          WHEN 0x07010000 THEN 'SQL 2000'   
          WHEN 0x71000001 THEN 'SQL 2000 SP1'
          WHEN 0x72090002 THEN 'SQL 2005'
          WHEN 0x730A0003 THEN 'SQL 2008'
          WHEN 0x730B0003 THEN 'SQL 2008'
          WHEN 0x74000004 THEN 'SQL 2012'
          WHEN 0x74000004 THEN 'SQL 2014'
          WHEN 0x74000004 THEN 'SQL 2016'
          WHEN 0x74000004 THEN 'SQL 2017'
          ELSE CONVERT(VARCHAR(20), protocol_version)
       END AS [TDS Version], *
FROM   sys.dm_exec_connections

Looking at the output below we can see that we have a number of different SQL client versions connecting.

So the original question was whether this is useful. If you plan on updating your database schema to use features that are only available in the latest versions, you may start breaking your clients. So knowing what versions are connecting is useful.

A good example is the “Always Encrypted” feature. Your clients must be running a specific version of the client in order to use it.

If you fancy some bedtime reading, you can download the TDS Protocol specification here.