How can you tell, from the server side, what version of the SQL Server Data Access Client Driver is being used on a connection? This way…

SQLCAT Blogs - 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 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.

…

…”

In past lives I’ve fought the SQL Server client DLL version battle and it was painful and ugly (and required allot of machine visits, director dumps, etc, etc). Being able to see this data via a query sounds pretty darn nice…