I'm trying to work out how these two pieces of the jigsaw interact and fit together when connecting to an MS-SQL server on linux.
As I understand it, FreeTDS is protocol (i.e. a set of rules) for talking to MS-SQL and it is the thing that actually does the talking. Unixodbc is a driver that implements the ODBC API, i.e. implements a set of functions I guess.
Why are both things necessary? Can anyone elaborate on my sketchy understanding of what these two things actually do?
unixODBC is a 'DriverManager' for ODBC. You can use unixODBC when on a Linux or *nix system to connect to any ODBC-capable database. Doing so means that you can write one lot of database queries which you should be able to use between different databases. If you were not on Unix, you would use a different Driver Manager, for example the built-in MS Office one.
To make all the components clear: if you're using a language, let's say Python, to connect to SQL Server, your connection might pass from Python's pyodbc (translates python objects to and from unixODBC), to unixODBC (manages drivers, such as FreeTDS), to FreeTDS (translates unixODBC objects to and from the TDS protocol, which Microsoft embraces) to SQL Server.
The unixODBC website http://www.unixodbc.org/ says:
An ODBC application makes ODBC calls to the DriverManager. The DriverManager carries out a number of tasks for the application such as:
- ensuring the proper driver is loaded/unloaded
- validation tasks
- 3.5 to 3.0 to 2.0 call and data mapping
Most calls to the DriverManager get passed onto the loaded Driver to be further processed but that is of little concern to the application.
Some advantages to using an ODBC DriverManager include:
- portable data access code
- runtime binding to a Data Source
- ability to easily change the Data Source
Briefly, it is the Driver Manager which reads your DSN, looks at the configured data sources, and decides where and how to connect.
Depending on which database you use, you will need a different driver. This piece of code 'translates' your requests made using ODBC to the right protocol for the relevant database management system. This is the component that would need to be different for different data sources. In your case, TDS is the protocol used by MS SQL Server. FreeTDS is a free software implementation of this protocol.
See also Wikipedia https://en.wikipedia.org/wiki/Open_Database_Connectivity (emphasis kept):
ODBC accomplishes DBMS independence by using an ODBC driver as a translation layer between the application and the DBMS. The application uses ODBC functions through an ODBC driver manager with which it is linked, and the driver passes the query to the DBMS. An ODBC driver can be thought of as analogous to a printer driver or other driver, providing a standard set of functions for the application to use, and implementing DBMS-specific functionality. An application that can use ODBC is referred to as "ODBC-compliant". Any ODBC-compliant application can access any DBMS for which a driver is installed. Drivers exist for all major DBMSs, many other data sources like address book systems and Microsoft Excel, and even for text or CSV files.