Ubuntu: MSSQL connection from Ubuntu



Question:

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.


Solution:1

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


Solution: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  


Solution: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/


Solution: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.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »