What are some ways of accessing Microsoft SQL Server from Linux?

sql-server

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.

Solution 1:

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

Solution 2:

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.

Solution 3:

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! :)

Solution 4:

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.

Solution 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