How To Login to Microsoft SQL Server through SQL Server Client on Linux (RedHat/Fedora/CentOS/Ubuntu)


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.

[root@server ~]# make install

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

Now issue make & make install commands to install it.

3. Define target SQL Server. Edit the file –

/usr/local/etc/freetds.conf

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 –

/usr/local/etc/odbc.ini

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 /usr/local/etc/odbcinst.ini
file and enter below text. Make sure libtdsodbc.so file exists at this location /usr/local/lib

[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!

Running a For Loop over SSH


Most of my tasks involve logging on to multiple servers & fetching details.  And sometimes, these details can be quite exhaustive to fetch 😦 One such scenario was logging on to a few servers & checking details of the users. There were many servers & many users. Logging in manually & checking would take a lot of time. So, I decided to make use of a “for loop” to login to the servers & another for loop to fetch details. The idea was to first enable password-less SSH for my ID across all the servers, get all the host-names in a text file & then getting list of all the users in another text file 😉

Below is how I did it 🙂 /etc/hostnames.txt contains all the servers & /home/shashank/id.txt contains all the users. Both the files need to be present on all servers. You may wish to run a for loop & pass the file to copied through scp. Below command prints user details only.

for hst in `cat /etc/hostnames.txt`; do ssh $hst 'for id in `cat /home/shashank/id.txt` ; do id -a $id; done'; done;

Below command prints hostnames & then the details of users.

for hst in `cat /etc/gridhosts`; do ssh $hst 'hostname; for id in `cat /home/tcs_admin/id.txt` ; do id -a $id; done'; done;

You can also use finger instead of id -a. Make sure it is installed on all the servers.