Home / ubuntu / Questions / 578934
Accepted
mertyildiran
Asked: 2015-01-29 10:16:34 +0800 CST

MSSQL connection from Ubuntu

  • 772

I need an easy and complete tutorial for making a MSSQL connection from Ubuntu.

I think I installed FreeTDS and UnixODBC but configurations are very complicated I didn't understand the issue.

I followed this tutorial: https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/wiki/Platform-Installation---Ubuntu

But I failed.

root@hackmachine:~# isql -v example.com XXXXX XXXXXXX
[IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified
[ISQL]ERROR: Could not SQLConnect
root@hackmachine:~#

Edit:

Before this edit "/etc/odbcinst.ini" and "/etc/odbc.ini" was empty.

I added these lines to /etc/odbcinst.ini :

[FreeTDS]

Description     = TDS driver (Sybase/MS SQL)

Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so

Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so

CPTimeout       =

CPReuse         =

FileUsage       = 1

I added these lines to /etc/odbc.ini :

[project_development]
Driver = FreeTDS
Description     = ODBC connection via FreeTDS
Trace           = No
Servername      = developer
Database        = project_development

[project_test]
Driver = FreeTDS
Description = ODBC connection via FreeTDS
Trace = No
Servername = developer
Database = test

[project_production]
Driver = FreeTDS
Description = ODBC connection via FreeTDS
Trace = No
Servername = production
Database = project_production

I didn't make any changes on "/etc/freetds/freetds.conf" since the very beginning.

Still, nothing has changed.

connection

7 Answers

  1. Best Answer

    Here are step by step instructions (found here):

    First, install unixODBC:

    sudo apt-get install unixodbc unixodbc-dev

    I also installed the following (perhaps necessary) packages:

    sudo apt-get install tdsodbc php5-odbc

    Then download, untar, compile, and install FreeTDS (warning, the URL may change):

    cd /usr/local

wget http://ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz
tar xvfz freetds-stable.tgz
cd freetds-0.82
./configure --enable-msdblib --with-tdsver=8.0 --with-unixodbc=/usr
make
make install
make clean

    Attempt a connection over Telnet to your SQL Server instance:

    telnet 192.168.0.1 1433

    Use the tsql tool to test out the connection:

    tsql -S 192.168.0.1 -U devuser

    This should prompt you for the password, after which you can hope against hope to see this beautiful sign:

    1>

    If that worked, I recommend throwing a (coding) party. Next up is some configging. Open the FreeTDS config file. /usr/local/etc/freetds.conf

    Add the following entry to the bottom of the file. We’re setting up a datasource name (DSN) called ‘MSSQL’.

    [MSSQL]
host = 192.168.0.1
port = 1433
tds version = 8.0

    Now open the ODBC configuration file: /usr/local/etc/odbcinst.ini

    And add the following MSSQL driver entry (FreeTDS) at the end:

    [FreeTDS]
Description = FreeTDS driver
Driver = /usr/local/lib/libtdsodbc.so
Setup=/usr/lib/odbc/libtdsS.so
FileUsage = 1
UsageCount = 1

    Then, finally, set up the DSN within ODBC in the odbc.ini file here /usr/local/etc/odbc.ini By adding this bit to the file:

    [MSSQL]
Description = MS SQL Server
Driver = /usr/local/lib/libtdsodbc.so
Server = 192.168.2.3
UID = devuser
PWD = devpass
ReadOnly = No
Port = 1433

    Test out the connection using the isql tool:

    isql -v MSSQL devuser 'devpass'

    If you see “Connected!” you’re golden, congratulations! If not, I’m truly sorry; see below where there are some resources that might help.

    Now restart Apache and test it from PHP using ‘MSSQL’ as the DSN. If something doesn’t work, you might try installing any or all of these packages: mdbtools libmdbodbc libmdbtools mdbtools-gmdb

    • 11

  2. On Trusty 14.04 I had problems making http://ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz

    When I tried to run a tsql command, I found out tsql can be installed via apt:

    $ sudo apt-get install freetds-bin
    • 4

  3. Hope you have copied the content in the link to the file. You need to update your SQL server details in "/etc/odbc.ini" file. You can you refer the link http://guywyant.info/log/206/connecting-to-ms-sql-server-from-ubuntu/

    • 0

  4. I had to do this for Ubuntu 14.04 and 17.04, both LTS 64-bit. Probably works for later versions.

    Install unixodbc (ODBC connections), freetds (SQL Server connections) and tdsodbc (bridge between the former two)

    sudo apt install unixodbc freetds-bin tdsodbc

    Add your ODBC drivers to /etc/odbcinst.ini (should be empty)

    [SQL Server]
Description = FreeTDS driver
Driver = /usr/lib/i386-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/i386-linux-gnu/odbc/libtdsS.so
FileUsage = 1
UsageCount = 1

    Add your DSNs to /etc/odbc.ini (should be also empty)

    [ACCOUNTING]
Description = Accounting database
Driver = /usr/lib/i386-linux-gnu/odbc/libtdsodbc.so
Server = 123.123.123.123
Database = your_database_name
Port = 1433

    And that was all.

    • 0

  5. To answer the question implied by:

    I need an easy and complete tutorial for making a MSSQL connection from Ubuntu

    ... and ignoring all the FreeTDS setup stuff.

    Use mssql-cli (install, usage).

    • 0

  6. I work at a company that heavily uses Microsoft products. However, I prefer working on a Linux machine (Ubuntu). Specifically, I use Ubuntu 16.04 LTS.

    I use JetBrains' DataGrip or SQL Electron as SQL GUIs. DataGrip will require Microsoft JDBC Driver for SQL Server which you need to point to manually when you open DataGrip for the first time. I put this in /usr/share/java/, but anywhere will work.

    Connecting is simple on my Windows 10 laptop. I simply open SQL Server Management Studio, and use my credentials to log in (username + password); I don't use Windows Authentication.

    Prerequisites

    In order to connect from Ubuntu, it's a little (just a little bit) extra work. First, on my Windows 10 machine, I open up a Powershell terminal, and type in the following

    nslookup <server name here>

    and it returns something like

    Non-authoritative answer:
Name: <server name here>
Address: <server's address>

    The server's name and address will be static, so the above will only be needed to run once per server you try to access.

    DataGrip

    Then, I jump onto my Linux machine and open up DataGrip. For the Host I type in <server's address>, Port: 1433 (this is default for MS SQL Server). The URL should look something like,

    URL: jdbc:sqlserver://<server's address>:1433

    If you click on Test Connection, it will prompt you for login information if you haven't added that yet. And that's it for DataGrip!

    DataGrip also has the following blog, which may be helpful to new users: Connecting DataGrip to MS SQL Server

    SQL Electron

    If you don't want to purchase DataGrip, you can use SQL Electron. The steps are similar. Open up SQL Electron, click add to add a new server, and for Server Address type in <server's address>, Port: 1433, and finally your login credentials. And THAT'S IT!

    • 0

  7. To connect to MSSQL instance running on a remote server:

    Follow the applicable steps in this article https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/connection-string-keywords-and-data-source-names-dsns?view=sql-server-2017

    NOTE: To complete the tests, you have to do a few more things: Edit /etc/odbc.ini and add the following:

    [MSSQL] Driver=ODBC Driver 17 for SQL Server Server=[ServerIP][,1433]

    To perform the test, execute the following in terminal:

    isql -v MSSQL [username] [password]

    You should see a "connected" response.

    If you are struggling, check that the ports on your Linux and Windows server is open!

    • 0