Linux ODBC configuration with FreeTDS

README.md

How to talk to MS SQL server from Linux using freetds driver. See the appropriate .conf files for configuration

To test the tsql freetds driver and conf using tsql:

~$ tsql -S mssql2 -U '<username>' -P '<password>'
locale is "en_GB.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1>

If prompted with 1> then the connection was successful. The name mssql2 after the -S should exists in the /etc/freetds.conf.

Once this is successful, make sure odbc config works using isql too. The test after the -v needs to match the settings from the /etc/odbc.ini.

~$ isql -v test <username> <password>

+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> SELECT TestTime FROM [testdb].[dbo].[metric1]
+------------------------+
| TestTime               |
+------------------------+
| 2017-08-15 19:17:43.000|
| 2017-08-15 19:17:47.000|
| 2017-08-15 19:17:48.000|
| 2017-08-15 19:18:01.000|=
+------------------------+
SQLRowCount returns 46
46 rows fetched

SQL> quit

freetds.conf

# /etc/freetds.conf
# server specific section
[global]
        # TDS protocol version
        tds version = 8.0

        # Whether to write a TDSDUMP file for diagnostic purposes
        # (setting this to /tmp is insecure on a multi-user system)
;       dump file = /tmp/freetds.log
;       debug flags = 0xffff

        # Command and connection timeouts
;       timeout = 10
;       connect timeout = 10

        # If you get out-of-memory errors, it may mean that your client
        # is trying to allocate a huge buffer for a TEXT field.  
        # Try setting 'text size' to a more reasonable limit 
        text size = 64512

# A typical Sybase server
[egServer50]
        host = symachine.domain.com
        port = 5000
        tds version = 5.0

# A typical Microsoft SQL server
# we don't specify the port 1433
[mssql]
        # use dns if available
        host = 192.168.1.2
        instance = DatabaseInstanceName

[mssql2]
        host = 192.168.1.3
        instance = DatabaseInstanceName
        # use this property by looking at the Windows Local policy. Windows Settings->Security Settings->Local Policies->Security Options-> 'Network security: LAN Manager authentication level'
        # depending on that use the ntlmv2 settings. This example is for Windows where it's set to "Send LTLVM2 response only. Refuse LM & NTLVM"
        use ntlmv2 = yes

odbc.ini

# /etc/odbc.ini

[test]
# This is an example with MS SQL Server where we don't really use the def 1433 since SQlBrowser listens to UDP port 1434
Driver = FreeTDS
Description = MS SQL Server driver
# CAREFUL = this 'Servername' property has to match with the config server name defined on the /etc/freetds.conf
Servername = mssql2
Trace = No
#Port = 1433
Database = testdb
Charset = UTF-8

odbcinst.ini

# /etc/odbcinst.ini

# make sure you have the libtsS.so driver setup correctly by installing the freetds and dev libraries. This is just an example
# Also don't forget about unixODBC unixODBC-devel

[FreeTDS]
Description     = FreeTDS
Setup           = /usr/lib64/libtdsS.so
Setup64         = /usr/lib64/libtdsS.so
Driver          = /usr/lib64/libtdsodbc.so
Driver64        = /usr/lib64/libtdsodbc.so
FileUsage       = 1

# Driver from the mysql-connector-odbc package
# Setup from the unixODBC package
#[MySQL]

python_example.py

# example