You have been asked to create an ODBC Data Source to connect an application to SQL Server. Most of the time you my not have a choice and the driver you will use to create a connection to SQL Server is dependant on your application requirements. But exactly what does each driver do and why do I see different drivers depending on the server I connect to?
Note: while you can choose between 32 bit and 64bit drivers for the purposes of this post I am going to concentrate on 64bit SQL Server drivers.
If we create a DSN on machine without SQL Server installed we will get the following driver option:
This is the Windows WDAC/MDAC driver which can be used to connect to SQL Server. Typically this would by used by generic application not taking advantage of SQL specific features. This driver is distributed as part of the Windows install. The roadmap for this driver can be viewed here
For SQL Server 2005 through to 2012 Microsoft SQL Server distributes and maintains a SQL Server driver called SQL Server Native Client (SNAC). Different versions of SQL Server will have different SNAC numbers.
Taking the example of SQL Server 2008R2 we can see version 10.0 of SNAC
The SNAC driver will be installed as part of the SQL Server install. On client machines where SQL Server will not be installed a standalone package is available as part of the Microsoft SQL Server 2008 R2 Feature Pack.
Why install SNAC?
With an install of SQL Server 2014 the new ODBC driver for SQL Server is installed. It also includes an install of the SQL Server 2012 SNAC driver.