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.

mertyildiran
  • 1,956
  • 2
  • 17
  • 21
  • have you create entry on ODBC file (/etc/odbc.ini)? and also check the drives in "/etc/odbcinst.ini" file – BDRSuite 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: https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/wiki/Platform-Installation---Ubuntu and check whether problem resolved. – BDRSuite Jan 28 '15 at 19:06
  • Nothing has changed I edited the question you can see above – mertyildiran Jan 28 '15 at 19:27

7 Answers7

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

Trevor Clarke
  • 892
  • 6
  • 18
  • 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
  • put localhost as the ip – Trevor Clarke Jan 28 '15 at 20:06
  • do this : tsql -S localhost -U bacon or if that does not work do this: tsql -S 127.0.0.1 -U bacon – Trevor Clarke Jan 28 '15 at 20:07
  • Both: Connection closed by foreign host. We are doing this tsql thing after telnet 77.223.141.204 1433. Am I right? – mertyildiran Jan 28 '15 at 20:09
  • Ok, im not sure then. DO this:

    Update your question, write what you have done so far and state the new error. Or if you feel you are a a different problem, ask a new question. Sorry I could not help.

     – Trevor Clarke Jan 28 '15 at 20:10
  • Yes try to telnet your MSSQL server from your Ubuntu machine. If it is success then try the tsql command, also make sure the user bacon have access to your MSSQL server. – BDRSuite Jan 28 '15 at 21:12
  • Excellent instructions actually. But when I'm testing with "isql -v MSSQL devuser 'devpass'" this command. It is giving me the following error : [IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified [ISQL]ERROR: Could not SQLConnect

    Please help me. How can I overcome this issue.

     – tuxtu Oct 20 '15 at 04:32
  • Hi, for me it return this error : isql: error while loading shared libraries: libodbc.so.2: cannot open shared object file: No such file or directory when trying with isql!

    Maybe I installed the library twice ? How to fix it ?

     – Meloman Jun 06 '17 at 13:47
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
frogatto
  • 904
  • 1
  • 12
  • 32
decibel.places
  • 141
  • 5
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).

oeuftete
  • 101
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!

Jon
  • 320
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!

Atron Seige
  • 101
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/

BDRSuite
  • 3,156
  • 1
  • 12
  • 11
  • 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 – BDRSuite 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.

ASalazar
  • 381