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 http://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
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)
2. Take the first two hex digits (0x73) and map it to correct SQL Server version based on the table below (http://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
SQL Server 7.0
SQL Server 2000
SQL Server 2000 SP1
7.1 Revision 1
SQL Server 2005
SQL Server 2008
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'ENDFROM 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).
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
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.
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!
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
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS T
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.