At times, we need to interact with Microsoft SQL server from Linux servers & run SQL queries. This tutorial will show you how to do it. But before we kick-off, lets first understand a few terms 🙂
DSN = Data Source Name. Think of it as the target MS SQL Server database you want to connect to from your Linux machine.
Driver = ODBC drivers to help establish connection. We are using two packages FreeTDS & unixODBC that will help achieve our task.
FreeTDS = From their official web-site, “FreeTDS is a set of libraries for Unix and Linux that allows your programs to natively talk to Microsoft SQL Server and Sybase databases”.
unixODBC = From their official web-site, “The unixODBC Project goals are to develop and promote unixODBC to be the definitive standard for ODBC on non MS Windows platforms. This is to include GUI support for both KDE and GNOME”.
Now that we know the terms, lets start with the tutorial 😉
An important note – This tutorial is based on default SQL Server port 1433. If you are using a different port, please specify that one. I am demonstrating with default one.
1. Download unixODBC from http://www.unixodbc.org/. You will need to click the “download” button for it. If it is already installed using YUM, I’d suggest uninstalling it. Better approach here will be to install from source. Untar it to any location of your choice. Then go to the unpacked directory & issue command : –
[root@server ~]# ./configure --prefix=/usr/local/unixODBC
It will configure the build process. Once it’s done, issue this command to make the installer.
[root@server ~]# make
Then issue this command to install it.
It must have been installed in
/usr/local/unixODBC location. Make note of this location.
2. Download FreeTDS from http://www.freetds.org/ . Click the Software link to go its FTP site. Its a bit slow 😛 We need to install it by telling about the location of unixODBC package. For this, untar it & move to its unpacked directory. Then issue this command. You may skip
--with-tdsver part : –
[root@server ~]# ./configure --with-unixodbc=/usr/local/unixODBC --with-tdsver=8.0
make install commands to install it.
3. Define target SQL Server. Edit the file –
Make sure this contains below text. Make sure the host value matches the text in square brackets. As you might have guessed it, this is the name of SQL server we will connect to. Make changes accordingly.
# A typical Microsoft server [sqlserver.shashank.com] host = sqlserver.shashank.com port = 1433 tds version = 8.0 client charset = UTF-8>/pre>
4. Define DSN. Edit the file –
Make sure it has below text. Text in square brackets defines DSN name. Enter anything but ensure it is single word only. Driver value is the driver to be used. We will define FreeTDS driver location in a separate file. Database value must be set to the database to be connected to. Its recommended to do a
telnet sqlserver.shashank.com 1433 to ensure proper connectivity. Change it to IP address/hostname of your SQL server.
[TEST_DB] Driver=FreeTDS Servername=sqlserver.shashank.com Port=1433 Database=TEST_DB
5. Define FreeTDS driver location. Edit
file and enter below text. Make sure
libtdsodbc.so file exists at this location
[FreeTDS] Description=TDS driver (Sybase/MS SQL) Driver=/usr/local/lib/libtdsodbc.so
6. Export a few variables.
- export ODBCSYSINI=/usr/local/etc
- export LD_LIBRARY_PATH=/usr/local/unixODBC/lib:/usr/local/lib:
- export ODBCINI=/usr/local/etc/odbc.ini
7. Test settings. Issue the below commands.
[root@server ~]# tsql -S sqlserver.shashank.com -U db_user Password: locale is "en_US.UTF-8" locale charset is "UTF-8" using default charset "UTF-8" 1>
[root@server ~]# odbcinst -j unixODBC 2.3.2 DRIVERS............: /usr/local/etc/odbcinst.ini SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources USER DATA SOURCES..: /root/.odbc.ini SQLULEN Size.......: 8 SQLLEN Size........: 8 SQLSETPOSIROW Size.: 8 [root@server ~]# odbcinst -q -d [FreeTDS]
[root@server ~]# odbcinst -q -s [TEST_DB]
8. Connect to SQL server now. Issue below command. TEST_DB is DSN name. Next is user name & last is password.
[root@server ~]# isql -v TEST_DB db_user password +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL>
9. Diagnosis & Troubleshooting. Issue below command & check errors, if any. This dumps a lot of useful information to screen that will help you find the problems & resolve them. I got a lot of help from this command.
[root@server ~]# osql -S TEST_DB -U db_user -P passwd
Thats it 🙂 We can now connect to our Windows counter part, SQL Server from Linux server 😉 Hope, you found this post useful!