Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Determine which version of SQL Server data access driver is used by an application


By:   |   Last Updated: 2011-01-05   |   Comments (3)   |   Related Tips: More > SQL Server Configurations

Problem

SQL Server Native Client was introduced with SQL Server 2005 and a newer version was introduced with SQL Server 2008. How do I determine which SQL Server Native Client version is being used by my SQL connections?

Solution

First of all, it is important to know what SQL Server Native Client (SNAC) is. Microsoft states that a SNAC "contains the SQL Server ODBC driver and the SQL Server OLE DB provider in one native dynamic link library (DLL) supporting applications using native-code APIs (ODBC, OLE DB and ADO) to Microsoft SQL Server." You can read more here: http://msdn.microsoft.com/en-us/data/ff658532.

SNAC 9 was introduced with SQL Server 2005 and SNAC 10 came with SQL Server 2008. It's important to be able to verify which version of SNAC is used by SQL connections, because there are additional features in SNAC 10 that are not supported in SNAC 9. Refer to the link in the next steps section about the differences.

It is fairly easy to identify which protocol a specific SQL session is using by looking at sys.dm_exec_connections view. The DMV sys.dm_exec_connections has a column named protocol_version, which according to BOL means "Version of the data access protocol associated with this connection". Basically, it indicates what protocol is associated with the client connection.

You can run the following query to get a list of connections and their protocol version.

select protocol_type, protocol_version,CONVERT(binary(4),protocol_version)
from sys.dm_exec_connections

As we can see below the converted protocol is pretty cryptic.

determine which SQL Server Native Client version is being used by my SQL connections

According to the table below (source: http://msdn.microsoft.com/en-us/library/dd339982(PROT.13).aspx), we are able to interpret the hexadecimal value of the protocol version. For example, values 0x730B003 indicate that this connection comes from a client using a SQL Server 2008 driver.

SQL Server Client to Server Server to Client
7.0 0x00000070 0x07000000
2000 0x00000071 0x07010000
2000 SP1 0x01000071 0x71000001
2005 0x02000972 0x72090002
2008 0x03000B73 0x730B0003

To make these eaiser to read, we can rewrite our query as follows to show the driver version.

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,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

The output below shows us that our client applications are using a SNAC 9 or SNAC 10 protocol as shown in line 1 and line 2.

there are features in snac 10 that are not supported in snac 9

Next Steps


Last Updated: 2011-01-05


get scripts

next tip button



About the author
MSSQLTips author Matteo Lorini Matteo Lorini is a DBA and has been working in IT since 1993. He specializes in SQL Server and also has knowledge of MySQL.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, August 30, 2017 - 8:44:20 PM - Ray Back To Top

Hello Matteo,

Useful script and as I needed to get more info checked Microsoft and this URL came up.  Which was what needed as well

https://msdn.microsoft.com/en-au/library/dd339982.aspx

Ray


Saturday, March 19, 2011 - 6:38:49 AM - Matteo Back To Top

I was not aware of the BLOG http://blogs.msdn.com/b/sqlcat/archive/2010/10/26/how-to-tell-which-version-of-sql-server-data-access-driver-is-used-by-an-application-client.aspx however, it is not uncommon to find similar information on the web. The idea of this artical was given to me at the local PASS when a speaker was talking about .NET, Connection pooling and SQL protocols.

Thanks for you input


Tuesday, March 15, 2011 - 7:25:56 PM - Repost Back To Top

This seems VERY similar to a MS blog post.


http://blogs.msdn.com/b/sqlcat/archive/2010/10/26/how-to-tell-which-version-of-sql-server-data-access-driver-is-used-by-an-application-client.aspx


Learn more about SQL Server tools