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 [email-masked].

What alternatives are there?
In terms of client libraries there are:

DBI/DBD

ODBC

JDBC

Open Client from Sybase

FreeTDS

Others

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 MSSQL modules 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
Microsoft does not provide any ODBC libraries for Unix, but there
are a couple third-party drivers out there. It appears that while you can find
open-source dirver 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.

DataDirect
Technologies (formerly a division or Merant) have been around long time in
this game.

OpenLink Software. OpenLink
supports the iODBC project, which includes
an open-source driver manager. There several useful links on the iODBC home page, including one with a long
list of ODBC drivers for Unix (not only for MS SQL Server).

Automation Technology provides the
OpenRDA ODBC Driver. As I understand their illustrations, this driver talks to
their server on the Windows side, which in its turn accesses the real ODBC
driver.

ODBTP (Open Database Transport
Protocol) describes itself as a TCP/IP protocol for connecting to
Win32-based databases from any platform. ODBTP is only half an ODBC-ODBC
bridge. That is, while it talks an ODBC service on Win32, the interface on the
Unix side is not ODBC. This appears to be an open-source project.

UnixODBC is another open-source
project that provides driver manager. UnixODBC is supported by Easysoft who provides a ODBC-ODBC bridge
as a commerical product.

Inline Internet Systems has
contributed with a TDS driver, which appears to be available free of charge
from the FreeODBC project. As of
this writing the upload date for this driver is in March 2000, so I would not
expect support for features added to SQL2000. I also get a feeling of that
they have reverse-engineered the TDS protocol, in which case the same caveates
as for FreeTDS
below applies.

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

When shopping a driver, make sure that it supports SQL2000, so you can use
all datatypes without restrictions. Even better, these days you should start
looking for one that supports SQL2005.

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/
(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:

Microsoft provides a free JDBC driver for SQL Server that runs on both
Unix and Windows. Go to Microsoft's Download Centre and
select SQL Server as Product/Technology and JDBC as keyword. You probably want
the most recent service pack too.

DataDirect
Technologies has a JDBC driver. (I am told the MS driver is based on
version 2.0 of DataDirect's driver.)

OpenLink Software offers JDBC
drivers.

Automation Technology tells me they
have too.

JNetDirect have a couple of
products in this arena.

Also for JDBC Ken North maintins a portal. 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. As
MSSQL::DBlib evolved from Sybase::DBlib, it would not be too much of a task to
retarget MSSQL::Sqllib to use this library instead. It's not on my todo list,
but see this
page for some hints.

Note: I believe that with early versions of SQL7 there was some problems
which prevented use of CT-Library with SQL Server. They were fixed in SP1 or
SP2.

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 is a reverse-engineering effort of TDS. The good thing with
FreeTDS is that it does not cost anything, but personally I would be very wary
of using something like this in business-critical code. Check out the FreeTDS home page for further details. There
appears to be a DBD::FreeTDS that goes along with it.

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 05-08-28 21:01