How to connect to Microsoft SQL server from Linux via command line

Although 95% of my work is done in Linux and MySQL, there are those unfortunate times I need to manipulate some data on a Microsoft SQL server. I didn’t want to install any apps on my computer that weren’t open source and I didn’t want to use a Micro$uck app through wine either. So after a few searches I came across an open source project called Sqsh. In this tutorial I’ll show you how to install sqsh and access a Microsoft SQL server from the Linux command line.


Sqsh (pronounced skwish) is short for SQshelL (pronounced s-q-shell), it is intended as a replacement for ‘isql’, supplied by Sybase. Sqsh is intended to provide much of the functionality provided by a good shell, such as variables, redirection, pipes, back-grounding, job control, history, command completion, and dynamic configuration. Sqsh has quite a few features, this is just a short list, for detailed explanation and examples please visit their site here.

. Provides all commands provided by isql
. Variables are provided in sqsh, much in the same way they are used within a standard shell
. Redirection & Pipes
. As of release 1.2, sqsh supports full csh-style command aliasing
. Command substitution
. Backgrounding & Job Control
. SQL Batch History
. Configurable Exit Status
. Ability to transfer the result set from any command batch to another server (or even the same server) via the Sybase bcp protocol
. Remote Procedure Calls
. Simple Scripting

Now that you know what sqsh does, let’s go ahead and install and start using it. If you are running Ubuntu, it is very easy, just do:

sudo apt-get install freetds
sudo apt-get install sqsh

You might also need a couple of other libraries installed depending on your setup, but the 2 apps above should contain everything you need.
If not running Ubuntu, get sqsh’s latest source from sourceforge and follow the install steps in the Install document inside the tar file you will be downloading.

Now that both freetds and sqsh have been installed, go ahead and create an entry at the end of the file /etc/freetds/freetds.conf that includes the M$Sql server you’ll be accessing:

/etc/freetds/freetds.conf

[MSuck]
        host = msuckserver
        port = 1433
        tds version = 8.0

Now create the sqsh configuration file containing your credentials and any other setting you’d like to have predefined. This file must reside in your home directory, otherwise you’ll have to start sqsh with the -r flag, which specifies the path and file to be used instead:

-r [sqshrc[:sqshrc ...]]

Specifies an alternate .sqshrc file to be processed, rather than the default. If no sqshrc is
supplied following -r, then no initialization files are processed. This flag must be the
first argument supplied on the command line, all other instances will be ignored.

So, let’s do it, create the new file .sqshrc and add your settings (obviously change each of the settings to match your own):

~/.sqshrc

user@laptop:~$ vim ~/.sqshrc

\set username=msqlloginname
\set password=msqlloginpassword
\set database=msqldbname
\set style=vert

Now you have both freetds and sqsh installed and configured, just go ahead and run it. For more information, options and in depth usage see the manual, or as one of my bosses used to say RTFM (read the fine manual) ;-)

user@laptop:~$ sqsh -SMSuck

sqsh-2.1 Copyright (C) 1995-2001 Scott C. Gray
This is free software with ABSOLUTELY NO WARRANTY
For more information type '\warranty'
1> select field1, field2, field3 from table where field1 = 'test';
2> go
field1: test
field2: other data
field3: 

(1 row affected)
1> exit
user@laptop:~$

That’s it, you are now able to connect to a Microsoft SQL server from the command line in Linux and manipulate your data as you wish. Hopefully this tutorial helped you. Thank you for stopping by and please share with others, after all, code should be free.foscode.com | because code should be free

7 thoughts on “How to connect to Microsoft SQL server from Linux via command line

  1. @Nash I’m calling a contractor to install the kitchen sink :-) I’ll have it all next time ;-) Thanks for stopping by and I’m glad I was able to help. Please come back again.

  2. I have installed freetds & sqsh in the Red hat OS and able to connect the mssql database. I can only give the select query of the databse. I need to populate data to the mssql pls tell me how to do that.

Leave a Reply

Your email address will not be published. Required fields are marked *

*


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>