Condividi tramite

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

  • Articolo

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.

SQL Server version

TDS version

SQL Server 7.0

7.0

SQL Server 2000

7.1

SQL Server 2000 SP1

7.1 Revision 1

SQL Server 2005

7.2

SQL Server 2008

7.3.A, 7.3.B

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).

Comments

  • Anonymous
    October 26, 2010
    This "CONVERT(CHAR(4), CAST(protocol_version AS BINARY(4)), 1)" doesn't return the varbinary 1st byte in char (SQL 2005). If I rewrite it like this it works: 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

  • Anonymous
    October 28, 2010
    Looks like Jens has a similar blog a while ago which I didn't find. Here is the link: blogs.msdn.com/.../tds-protocol-versions-meet-client-stacks.aspx

  • Anonymous
    October 28, 2010
    Great stuff from both you, Kun, & from Jens.  (Great minds think alike, eh?)  BTW, don't know if you're aware--the SQL twitterati were all, well, a-twitter about this post—there were numerous RT's. @aspiringgeek

  • Anonymous
    November 01, 2010
    I can't find a comment I received. Someone pointed out that the query didn't work for his/her scenario of SQL 2005. I'm adopting the suggesiton of using Substring instead of Convert function. Thanks for the suggestion!

  • Anonymous
    January 03, 2013
    SELECT db_name(sys.sysprocesses.dbid) as DBName, sys.sysprocesses.loginame, 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,client_net_address ,client_tcp_port,local_tcp_port ,T.text FROM sys.dm_exec_connections CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS T JOIN sys.sysprocesses ON sys.dm_exec_connections.session_id = sys.sysprocesses.spid WHERE sys.sysprocesses.dbid = 8 and sys.dm_exec_connections.protocol_version = 1895825409 I used this query to identify specific protocol version connections to a specific database.