How to tell which version of SQL Server data access driver is used by an application client

In a recent engagement with an ISV, we’ve run into a sporadic application failure problem when the application is deployed in a mixed environment. It comes down to finding out which version of SQL Server driver the application uses for connection. In the mixed environment, some application server machines have SNAC 9 (shipped with SQL Server 2005) and SNAC 10 (shipped with SQL Server 2008) installed side by side. And the application is designed to support both. It’s important to determine which version of SNAC was used as there are additional features in SNAC 10 that are not supported in SNAC 9 and cause different behavior of the application. For more info about the driver difference, check out https://blogs.msdn.com/b/sqlnativeclient/archive/2008/02/27/microsoft-sql-server-native-client-and-microsoft-sql-server-2008-native-client.aspx

After struggling to find out from application server what version of SNAC actually was being used by the deployed application, we decided to probe from SQL Server side. Looking at sys.dm_exec_connections, there is a column protocol_version, which according to BOL means “Version of the data access protocol associated with this connection”. Basically it tells what protocol is associated with the client connection.

select protocol_type, protocol_version from sys.dm_exec_connections

Result:

TSQL 1930035203

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:

1. Convert the above protocol_version to hex format.

SELECT CONVERT(BINARY(4), 1930035203)

Result:

0x730A0003

2. Take the first two hex digits (0x73) and map it to correct SQL Server version based on the table below (https://msdn.microsoft.com/en-us/library/dd339982(PROT.13).aspx - you may have to copy and paste the link to your browser). Note the rest of the hex digits (0A0003) are intermediate build and minor version.

3. So now we know this connection is from a client using SQL Server 2008 driver.

To put above steps together, you can run following simple 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

The above example and method would allow you to determine which version of SQL Server data access driver is used by a client. It applies to any application that makes connection to SQL Server using driver based on TDS protocol (doesn’t apply to SOAP).