6

I am running SSMS V18.2 and connecting to SQL 2008 - 2019 instances.

I am researching various multiple subnet connection scenarios. I learned that ODBC 13.1+ includes TransparentNetworkIPResolution which alters the way connections are made with multiple subnets.

My computer has several different drivers on it.

Screenshot of 5 different drivers

I want to know which (if any) of these SSMS is using for direct connections to SQL instances in my network.

I have looked at tools > options and I don't see anything that gives me an option to change what driver is used to connect.

When I Google, I get results about using ODBC for Linked servers.

Does SSMS use ODBC for direct connects?

If so can I select which one it uses?

Edit There is a comment and an answer indicating SSMS does not use ODBC, based on my continuing research it I think it uses the SQL Server Native Client 11.0 in the screen shot above.

Looking at Using Connection String Keywords with SQL Server Native Client it seems like you can connect with the driver of you choice using the keyword Driver I am still researching how that might work.

Edit2 Actually it looks like (see related) the driver is not one of those in the screen shot, nor can you use the keyword driver to change drivers.

  • 3
    This query from an SSMS query window returns .Net SqlClient Data Provider: SELECT client_interface_name FROM sys.dm_exec_sessions WHERE session_id = @@SPID; – Dan Guzman Dec 30 '19 at 12:38
13

SSMS doesn't use ODBC drivers to connect to SQL Server databases. It uses the .Net Framework driver. You cannot choose which driver it uses as it is hard-coded in the application. But why would you need to choose another one?

Linked servers are another thing. It is your DB Server itself connecting to another server. If it is SQL Server, it will use its native driver. It if it another DB Engine type, it will then ask you which driver to use. And this is where you select your ODBC driver for MySQL, Oracle, Postgres or other.

|improve this answer|||||
  • +1. thank you. "But why would you need to choose another one?" For an easy way to experiment with multi subnet connection scenarios. You can define MultiSubnetFailover=True in your SSMS connection, how about changing between different ODBC drivers as well.? – James Jenkins Dec 30 '19 at 14:33
  • If you want to do this, you can use MSAccess which can use ODBC to connect to SQL Server. You could also write a quick app, that could be even more verbose. – Pierre Dec 30 '19 at 19:48
  • @Pierre, you could test various scenarios ODBC, OLE DB, and SqlClient using PowerShell via System,.Data.Odbc, System,.Data.OleDb, and System,.Data.SqlClient. – Dan Guzman Dec 31 '19 at 0:31
  • When connecting as a Linked Server to another SQL Server the default is to use the native driver, but you can persuade it to use another driver, if you know how. – RBarryYoung Dec 31 '19 at 3:42
  • While this answer is highly upvoted, I am hesitant to accept it. The code in the comment by Dan Guzman returns .Net SqlClient Data Provider and I am not sure if that is the same or different from .Net Framework driver. in this answer. I am looking around but not finding a lot of documentation that makes me comfortable that I understand what is what. – James Jenkins Dec 31 '19 at 15:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.