25

I am trying to connect to MSSQL server from Ubuntu. I have installed freetds like suggested here.

enter image description here

However, when I try to configure /etc/odbc.ini and enter a driver path I have no driver at location /usr/local/freetds/lib/libtdsodbc.so.

enter image description here

Can someone help me to install freetds and to configure odbc to use it? *edit1: I have found libtdsodbc.so in /usr/lib/x86_64-linux-gnu/odbc. Should I use that driver/path?

Community
  • 1
  • 1
Hrvoje T
  • 3,365
  • 4
  • 28
  • 41
  • 1
    check my the steps I followed here, hope that help you: http://stackoverflow.com/questions/34725523/working-with-nodejs-mssql-at-linux-ubuntu – Hasan A Yousef Jan 11 '16 at 15:54

3 Answers3

35

I've created a Vagrant box which has a full installation example here: https://github.com/FlipperPA/django-python3-vagrant/

...but here are the basic steps.

(NOTE: change dbserver.domain.com to your server hostname or IP)

# Install pre-requesite packages
sudo apt-get install unixodbc unixodbc-dev freetds-dev freetds-bin tdsodbc

Point odbcinst.ini to the driver in /etc/odbcinst.ini:

[FreeTDS]
Description = v0.91 with protocol v7.2
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so

Create your DSNs in /etc/odbc.ini:

[dbserverdsn]
Driver = FreeTDS
Server = dbserver.domain.com
Port = 1433
TDS_Version = 7.2

...and your DSNs in /etc/freetds.conf:

[global]
    # TDS protocol version, use:
    # 7.3 for SQL Server 2008 or greater (tested through 2014)
    # 7.2 for SQL Server 2005
    # 7.1 for SQL Server 2000
    # 7.0 for SQL Server 7
    tds version = 7.2
    port = 1433

    # 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 Microsoft server
[dbserverdsn]
    host = dbserver.domain.com
    port = 1433
    tds version = 7.2

After completing this, you can test your connection by attempting to connect with tsql (to test the FreeTDS layer) and isql (for the unixODBC through FreeTDS stack).

DPSSpatial
  • 767
  • 3
  • 11
  • 31
FlipperPA
  • 13,607
  • 4
  • 39
  • 71
  • 3
    I'm new in this. I suppose I shoud change `Server = dbserver.domain.com` with IP of my server? Is this how I use tsql `tsql -S dbserverdsn` or should I write IP there? When I use `tsql -S dbserverdsn` I get `Error 100 (severity 11): unrecognized msgno Error 20009 (severity 9):Unable to connect: Adaptive Server is unavailable or does not exist OS error 111, "Connection refused" There was a problem connecting to the server` – Hrvoje T Oct 27 '15 at 14:13
  • there should be tsql -S dbserverdsn – Apit John Ismail Aug 23 '17 at 15:04
  • 3
    I checked the tds version with `tsql -C` and saw it was 4.2. I replaced the 7.2's above with 4.2 and that seemed to remove the `msgno error` – Jay Killeen Oct 11 '17 at 13:55
  • 1
    @FlipperPA - wow thank you for this solution - I am able to connect QGIS Destop to MS SQL Server on a Linux Mint desktop, which I haven't been able to figure out for nearly a decade! – DPSSpatial Aug 28 '23 at 15:36
22

The version available via apt-get is a really old version. To get a newer version:

sudo apt-get install wget
sudo apt-get install build-essential
sudo apt-get install libc6-dev

# find latest version of FreeTDS ftp://ftp.freetds.org/pub/freetds/stable/

wget ftp://ftp.freetds.org/pub/freetds/stable/freetds-1.2.tar.gz
tar -xzf freetds-1.2.tar.gz
cd freetds-1.2
./configure --prefix=/usr/local --with-tdsver=7.3
sudo make
sudo make install
RegularlyScheduledProgramming
  • 1,347
  • 1
  • 11
  • 27
rdaniels
  • 939
  • 8
  • 7
0

in freedts.conf

[Server80]
        host = example.com
        port = 1433
        tds version = 8.0
        client charset = UTF-8

in odbc.ini

[MSSQL8]
Driver          = FreeTDS
Description     = Sybase JDBC Server
Trace           = No
Servername      = Server80
Database        = DBNAME
UID             = sa
ClientCharset   = UTF-8

in odbcinst.ini

[FreeTDS]
Description=v0.63 with protocol v8.0
Driver=/usr/lib/i386-linux-gnu/odbc/libtdsodbc.so
UsageCount=2
kuptsov
  • 9
  • 2