56

We have a Windows machine running SQL Server 2005, and we need to be able to run some database queries on it from a Linux box. What are some of the recommended ways of doing this? Ideally, we would want a command-line utility similar to sqlcmd on Windows.

cwick
  • 26,132
  • 12
  • 37
  • 40

15 Answers15

34

FreeTDS + unixODBC or iODBC

Install first FreeTDS, then configure one of the two ODBC engines to use FreeTDS as its ODBC driver. Then use the commandline interface of the ODBC engine.

unixODBC has isql, iODBC has iodbctest

You can also use your favorite programming language (I've successfully used Perl, C, Python and Ruby to connect to MSSQL)

I'm personally using FreeTDS + iODBC:

$more /etc/freetds/freetds.conf
[10.0.1.251]
    host = 10.0.1.251
    port = 1433
    tds version = 8.0

$ more /etc/odbc.ini
[ACCT]
Driver = /usr/local/freetds/lib/libtdsodbc.so
Description = ODBC to SQLServer via FreeTDS
Trace = No
Servername = 10.0.1.251
Database = accounts_ver8
Vinko Vrsalovic
  • 330,807
  • 53
  • 334
  • 373
  • 1
    In Ubuntu I use driver path `/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so`. I get [ISQL]ERROR: Could not SQLConnect. – Hrvoje T Oct 26 '15 at 08:58
  • ODBC is the driver that allows Linux to speak with MSSQL, and FreeTDS interprets the protocol, right? – orrymr Mar 04 '16 at 11:38
  • 1
    Microsoft published yesterday a public SQL Server for Linux with support for Docker, Ubuntu, etc. https://www.microsoft.com/en-us/sql-server/sql-server-vnext-including-Linux – gavioto Nov 17 '16 at 17:17
  • 2
    Any chance you could update this answer to reflect newer versions of code and packages, please? – Adrian Keister Aug 28 '19 at 18:20
  • I'm sorry @AdrianKeister, this may not even be the sanest approach today :-( – Vinko Vrsalovic Aug 29 '19 at 06:22
  • 1
    What would you recommend instead? – Adrian Keister Aug 29 '19 at 22:56
  • If you need to talk to old versions of SQL Server (e.g. 13.0) from new versions of Linux (e.g. Ubuntu 20.04), Microsoft's ODBC driver for Linux **doesn't work** due to TLS version mismatches. FreeTDS + unixODBC was the *only* option. It was a bit of a struggle working through the documentation to get the configuration right, but I got it working in the end. – daviewales May 11 '21 at 06:26
  • My `/etc/odbc.ini` config was the same as above, but I had to use `Driver=/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so`. Also note that to pass username and password to SQL Server you need to specify `UID="username" PWD="password"` in your connection string somewhere. (Or in the odbc.ini if you don't need to worry about security.) – daviewales May 11 '21 at 06:29
21

sql-cli is a nodejs based cross platform command line interface for sql server. You can install it via npm https://www.npmjs.org/package/sql-cli

It can connect to both on-premise and sql azure instance.

Muhammad Hasan Khan
  • 34,648
  • 16
  • 88
  • 131
  • 3
    Thank you! **This should be the accepted answer!** This is by far the best solution for people used to the `mysql` CLI client. – dotancohen Jul 13 '16 at 13:04
10

sqsh + freetds.

sqsh was primarily an isql replacement for Sybase SQL Server (now ASE) but it works just fine for connecting to SQL Server (provided you use freetds).

To compile, simply point $SYBASE to freetds install and it should work from there. I use it on my Mac all day.

The best part of sqsh are the advanced features, such as dead simple server linking (no need to set up linked servers in SQL Server), flow control and looping (no more concatenating strings and executing dynamic SQL), and invisible bulk copy/load.

Anyone who uses any other command line tool is simply crazy! :)

Uwe Keim
  • 39,551
  • 56
  • 175
  • 291
Matt Rogish
  • 24,435
  • 11
  • 76
  • 92
  • Having a problem installing sqsh: http://stackoverflow.com/questions/18784242/install-sqsh-for-freetds-sql-server – noahlz Sep 13 '13 at 10:37
  • `sqsh.org` is no longer a good link (currently Japanese real estate?). It can be found [on SourceForge](https://sourceforge.net/projects/sqsh/), though there have been no updates since 2014. – r2evans Apr 27 '17 at 16:37
  • I whole heartedly agree - this is one of the most underrated pieces of software out there. But with no updates since 2014 things look a bit ominous. – Tiggyboo Mar 04 '19 at 20:46
6

pymssql is a DB-API Python module, based on FreeTDS. It worked for me. Create some helper functions, if you need, and use it from Python shell.

Constantin
  • 27,478
  • 10
  • 60
  • 79
  • `pymssql` does not appear to be as well-maintained as `pyodbc`. Indeed, it looks like the `pymssql` devs gave up because of `pyodbc`. – Adrian Keister Aug 28 '19 at 18:19
5

Mono contains an ADO.NET provider that should do this for you. I don't know if there is a command line utility for it, but you could definitely wrap up some C# to do the queries if there isn't.

Have a look at http://www.mono-project.com/TDS_Providers and http://www.mono-project.com/SQLClient

SteinNorheim
  • 2,197
  • 1
  • 15
  • 21
5

Since November 2011 Microsoft provides their own SQL Server ODBC Driver for Linux for Red Hat Enterprise Linux (RHEL) and SUSE Linux Enterprise Server (SLES).

It also includes sqlcmd for Linux.

mleu
  • 365
  • 3
  • 5
3

If you are using Java, have a look at JDBC.

http://msdn.microsoft.com/en-us/library/ms378672(SQL.90).aspx

http://en.wikipedia.org/wiki/Jdbc

borjab
  • 11,149
  • 6
  • 71
  • 98
2

You don't say what you want to do with the resulting data, but if it's general queries for development/maintenance then I'd have thought Remote Desktop to the windows server and then using the actual SQL Server tools on their would always have been a more productive option over any hacked together solution on Linux itself.

Cruachan
  • 15,733
  • 5
  • 59
  • 112
2

There is an abstraction lib available for PHP. Not sure what your client's box will support but if its Linux then certainly should support building a PHP query interface with this: http://adodb.sourceforge.net/ Hope that helps you.

Taptronic
  • 5,129
  • 9
  • 44
  • 59
2

Surprised no one has mentioned that, contrary to what other answers seem to suggest, FreeTDS is all you need. No need for unixODBC, iODBC or anything else on top of it.

run some database queries [...] command-line utility similar to sqlcmd on Windows

tsql does this and is part of the FreeTDS package (as are freebcp and other utilities). tsql does not have fancy UI, but if you want a command-line utility that is light, functional and performant, it'll do just fine.

See e.g. How to run a SQL script in tsql

And by light, I mean under 500kb in size (maybe under 60k depending on how it's compiled) and, as far as I have seen, extremely efficient with memory and CPU.

mwag
  • 3,557
  • 31
  • 38
1

I'd like to recommend Sqlectron. Besides being open source under MIT license it's multiplatform boosted by Electron. Its own definition is:

A simple and lightweight SQL client desktop with cross database and platform support

It currently supports PostgreSQL, MySQL, MS SQL Server, Cassandra and SQLite.

Moacir Rosa
  • 146
  • 2
  • 9
  • 1
    Kind of buggy but it simple and it works. Also has support for many linux distributions. Thanks – julianm Jan 12 '22 at 16:52
0

I was not confortable with the freetds solution, it's why i coded a class (command history, autocompletion on tables and fields, etc.)

http://www.phpclasses.org/package/8168-PHP-Use-ncurses-to-get-key-inputs-and-write-shell-text.html

François Neumann-rystow
  • 1
0

valentina-db it has free version for sql server
.rpm and .deb
serial id will be sent by email after registration

https://www.valentina-db.com/en/

https://valentina-db.com/en/store/category/14-free-products

valentina-db

mohsen.nour
  • 1,089
  • 3
  • 20
  • 27
0

If you use eclipse you can install Data Tools Platform plugin on it and use it for every DB engines including MS SQLServer. It just needs to get JDBC driver for that DB engine.

Mehdi
  • 746
  • 1
  • 10
  • 25
0

There is a nice CLI based tool for accessing MSSQL databases now.

It's called mssql-cli and it's a bit similar to postgres' psql.

Gihub repository page

Install for example via pip (global installation, for a local one omit the sudo part):

sudo pip install mssql-cli
helvete
  • 2,455
  • 13
  • 33
  • 37