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.

  • have you create entry on ODBC file (/etc/odbc.ini)? and also check the drives in "/etc/odbcinst.ini" file – vembutech Jan 28 '15 at 18:47
  • Both of these files are empty what should I do exactly? Can you share a link or some details? – mertyildiran Jan 28 '15 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. – vembutech Jan 28 '15 at 19:06
  • Nothing has changed I edited the question you can see above – mertyildiran Jan 28 '15 at 19:27
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

  • after "tsql -S 192.168.0.1 -U devuser": Connection closed by foreign host. What should I do? – mertyildiran Jan 28 '15 at 20:00
  • use you local ip – Trevor Clarke Jan 28 '15 at 20:01
  • I used actually "tsql -S 77.223.141.204 -U bacon" MSSQL server's IP address – mertyildiran Jan 28 '15 at 20:02
  • would it not be localhost then? because you cant connect to your own external ip. – Trevor Clarke Jan 28 '15 at 20:02
  • Yes it is an external IP isn't possible to access via this method? What should I do? – mertyildiran Jan 28 '15 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/

  • after "tsql -S 192.168.0.1 -U devuser": Connection closed by foreign host. What should I do? – mertyildiran Jan 28 '15 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 – vembutech Jan 28 '15 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.

  • libtdsodbc.so and libtdsS.so may also be found in /usr/lib/x86_64-linux-gnu/odbc/ – Strixy Sep 19 '17 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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.