Naviant
888.686.4624

Identifying Mismatched or Outdated ODBC Drivers

Are you experiencing unexplained SQL Server database issues? Is your web server having issues with symptoms ranging from request time-outs to login failures to deadlocks to memory allocation issues to hangs or unexplained restarts? Many of these issues can be tracked back to mismatched or outdated ODBC drivers.

To avoid these and other potential database issues, it is important that the ODBC drivers in use match the database version being referenced. The driver names are distinct so you can tell them apart at a glance.

SQL Version Driver Name
SQL Server 2000 SQL Server
SQL Server 2005 SQL Native Client
SQL Server 2008 SQL Server Native Client 10.0
SQL Server 2012 SQL Server Native Client 11.0

Note that there are multiple builds of each of the drivers and locking issues can also occur with older driver builds (e.g., running SQL Server 2005 with the original ODBC drivers dated October 2005, when an updated driver dated December 2008 is available from Microsoft).

The following query can be used to identify the version (but not build) of ODBC driver used for active connections against SQL Server 2005 or higher databases:

SELECT A.session_id
, B.login_name
, B.host_name
, A.client_net_address
, B.client_interface_name
, A.protocol_type
, CAST(A.protocol_version AS VARBINARY(9))
,driver_version =
CASE SUBSTRING(CAST(A.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’
WHEN 0x74 THEN ‘SQL Server 2012’
ELSE ‘Unknown driver’
END
FROM sys.dm_exec_connections A
INNER JOIN sys.dm_exec_sessions B ON A.session_id = B.session_id
WHERE B.client_interface_name = ‘ODBC’
AND SUBSTRING(CAST(A.protocol_version AS BINARY(4)), 1,1) <> 0x73

In the example above, the query will return workstation information for all active connections to all databases in the SQL Server instance that are using a driver version other than SQL Server 2008 (0x73). You can replace that last portion with your SQL Server version’s code to identify mismatched drivers being used in your solution. (i.e. 0x70 = SQL Server 7, 0x74 = SQL Server 2012, etc.) Please note that this is only the main version, and won’t help with identifying SQL Server 2005 driver builds of an earlier SP than the server.

About Jennifer Siegel

Jennifer (aka Pixie) has over 20 years of experience in the Information Technology field, which includes 13+ years of Systems Administration and 15+ years of developing and implementing technical documentation and training. She has extensive experience with OnBase, SQL, and a firm foundation in computer science. Despite this, she considers herself a well-adjusted nerd with hobbies including gardening (poorly), archery, and knitting in public.

Subscribe to our Newsletter!

We will send you a newsletter once a month with updates from our blog, resource library, and events calendar.

Newsletter Sign Up
Interested to see what ECM can do for your company?
Get in touch with us today to learn more
Contact Us