Connecting to MS SQL Server from Unix

Disclaimer

I've written this page as I keep getting questions on how to access MS SQL Server from Unix. I have never had any reason talk to SQL Server from Unix myself, but since people kept asking me how to do it, I decided to compile an web page with the tiny fragments of information and I have. The main focus is in on Perl programming, but users of other languages should be able to find information here as well.

Beware that this page may not be updated with recent information. If you have any comments or corrections, please drop me a line on esquel@sommarskog.se.

What alternatives are there?

In terms of client libraries there are:

DBI/DBD

This solution applies to Perl programmers only. DBI/DBD is not really a client library in itself, but DBI is the de-facto standard in the Perl world for talking to database servers. DBI is a standardized high-level interface, and then you use a driver whose name always start with DBD::. There are non-DBI modules out there for legacy or other reasons. (My Win32:SQLServer module for instance.) But if you don't have any old code to support, using DBI is probably the right path for you to take. You find further information at the DBI home page.

ODBC

For a long time Microsoft did not provide any ODBC libraries for Unix, but this is changing. At PASS 2011, Ted Kummert announced that they will release an ODBC driver for Unix. A CTP (Community Technology Preview) is available for download. There is also a FAQ available.

There are also a number of third-party drivers out there. It appears that while you can find open-source driver managers, the drivers themselves are generally commercial products. Below is a list of ones that I have heard about. All seems to have free trials available. (Due credit goes to Reinaldo Kibel at Microsoft from whose newsgroup posting I have culled several of the links below.)

Some of these drivers appears to actually be bridges, that is they talk with a component on Windows, which in its turn talk to SQL Server, presumably through Microsoft's own ODBC drivers. This may be slower than a driver that talks directly to SQL Server.

In the list I have not considered the fact that Unix comes in many different flavours, and for binary distributions, you need a binary that fits your platform. You need to research this yourself.

See also Ken North's ODBC Portal at http://www.sqlsummit.com/ODBCVend.HTM.

When shopping a driver, make sure that it supports SQL 2008, so you can use all datatypes without restrictions.

On the Perl side you would use DBD::ODBC, and, it appears, sometimes also DBD::Proxy. This FAQ page gives some example on how to this. For UnixODBC from Easysoft (see above), there is a Perl module available on CPAN, http://www.cpan.org/authors/id/R/RK/RKIES/UnixODBC-x.yy.tar.gz (where x.yy is the available version).

Personally, this is the approach I would recommend, as you would use something which is fully supported in all ends. (Unless you are using Java, in which case you probably should look in the next section.)

JDBC

Just like ODBC, JDBC is a standardized API for use in Java programs. I know of the following offererings for connecting to SQL Server:

Ken North maintins a portal for JDBC as well. See http://www.sqlsummit.com/JDBCVend.htm.

Open Client from Sybase

DB-Library was originally developed by Sybase, and you can use their version of DB-Library or their newer CT-Library. Sybase bundles the two in a product called Open Client. As the Perl interface you would then use Michael Peppler's Sybperl. With regards to SQL7 and later versions, the same restrictions apply as when using MSSQL::*. With regards to SQL6.5 and SQL6.0, I am told that there are no restrictions in usability, but it is nothing I have verified.

Sybperl is the collective name for three different modules: DBD::Sybase, Sybase::CTlib and Sybase::DBlib. DBD::Sybase is a DBI driver using CT-Library, while the other two offers direct interfaces to the client library.

Generally, I recommend this option, since you would be limited to support for the data types that were available in SQL 6.5, and it is not likely that Microsoft considers this to be supported at all.

FreeTDS

TDS -- Tabular Data Stream -- is the protocol that SQL Server talks with its clients. This is a proprietary protocol, owned by Microsoft (and Sybase, who have their version). Nevertheless there is exists FreeTDS which originally was a reverse-engineering effort of TDS. Now when Microsoft has published the TDS specification, they should be able to repair any cracks they may have. Check out the FreeTDS home page for further details. There appears to be a DBD::FreeTDS that goes along with it.

A correspondent told me this:

We have used DBD::Sybase and FreeTDS to connect to SQL Server for over 3 years. We've found that this combination gives excellent results if used carefully. We achieved 400 transactions per second on our old SQL 2000 system, and we recently switched to SQL 2005 without any application or driver changes.

Others

Ken North has two more pages that can be of interest: .Net Providers (if you use the Mono project on Linux) and XQuery processors (if all you want to do is to run XQuery against SQL 2005.)

Back to the MSSQL home page.

Last updated 11-12-15 22:05