10

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.

4
  • have you create entry on ODBC file (/etc/odbc.ini)? and also check the drives in "/etc/odbcinst.ini" file
    – BDRSuite
    Jan 28, 2015 at 18:47
  • Both of these files are empty what should I do exactly? Can you share a link or some details? Jan 28, 2015 at 18:57
  • I believe you have missed the "Configure UnixODBC" step available in the link which you followed. Kindly update those two files like in the URL: github.com/rails-sqlserver/activerecord-sqlserver-adapter/wiki/… and check whether problem resolved.
    – BDRSuite
    Jan 28, 2015 at 19:06
  • Nothing has changed I edited the question you can see above Jan 28, 2015 at 19:27

7 Answers 7

11

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

12
  • after "tsql -S 192.168.0.1 -U devuser": Connection closed by foreign host. What should I do? Jan 28, 2015 at 20:00
  • use you local ip Jan 28, 2015 at 20:01
  • I used actually "tsql -S 77.223.141.204 -U bacon" MSSQL server's IP address Jan 28, 2015 at 20:02
  • would it not be localhost then? because you cant connect to your own external ip. Jan 28, 2015 at 20:02
  • Yes it is an external IP isn't possible to access via this method? What should I do? Jan 28, 2015 at 20:05
4

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
0

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/

2
  • after "tsql -S 192.168.0.1 -U devuser": Connection closed by foreign host. What should I do? Jan 28, 2015 at 20:01
  • Can you check whether the port 1433 is open in the MSSQL server. You need to make sure you can access the port 1433 of your MSSQL server. If it is not open kindly open the port and then check
    – BDRSuite
    Jan 28, 2015 at 20:21
0

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.

1
  • libtdsodbc.so and libtdsS.so may also be found in /usr/lib/x86_64-linux-gnu/odbc/
    – Strixy
    Sep 19, 2017 at 16:29
0

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

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

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!

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .