perl DBI-ODBC for SQL Server on Linux

perl DBI is an amazingly versatile tool to support all sorts of database connectivity including Microsoft SQL Server. However, the driver for SQL Server is not included with any perl distribution. The connection topology for perl to SQL Server is:

perl --> DBI --> DBD::ODBC --> unixODBC --> Microsoft Driver --> SQL Server

First, DBI is required. Fortunately, every modern perl distributions come with DBI. So, nothing needs to be done for this one.

Next, an ODBC driver is required. One can use FreeTDS but it's not complete for SQL Server. (Note that FreeTDS is needed to connect to Sybase ASE.) It's better to get a driver from Microsoft. Thankfully, Microsoft offers a driver for SQL Server on Linux, which can be downloaded from Microsoft. URL is:

https://msdn.microsoft.com/en-us/library/hh568451%28v=sql.110%29.aspx

The driver is usually installed in /opt/microsoft but it can be installed just about anywhere.

Next, unixODBC is required. This can be obtained from unixodbc.org. Download the latest, compile, and install. For the illustration purpose, assume that it's installed under /usr/local.

unixODBC creates two files in its directory (e.g. /usr/local/etc): odbc.ini and odbcinst.ini. 

odbcinst.ini is where the lower driver is specified.
odbc.ini is where DSNs are specified.

unixODBC will first check the user's home directory for .odbc.ini. If not found, it checks for /etc/odbc.ini. If not found, it uses the one in its own directory (e.g. /usr/local/etc).

Since the low-level driver requires unixODBC libraries, the location of the libraries (e.g. /usr/local/lib) must be specified with environment variable LD_LIBRARY_PATH.

Without it specified, your program will experience "segmentation fault" with no warning or error messages. Sometimes you may also get "SQLGetPrivateProfilestringW" error.

Next, DBD::ODBC for perl is required. This can be installed using cpan command.

$ cpan
cpan> install DBD::ODBC

If everything is good, it should be installed successfully. Some of the tests probably will fail or skipped. But don't worry about them as long as they get installed.

A perl script can be written using DBI function. When connecting to a SQL Server, use the following:

$mssql = DBI->connect('DSN=dnsName;UID=sqlserver-login;PWD=password');

where dnsName is one of the names in a pair of square brackets defined in odbc.ini. An entry in odbc.ini includes the server name and driver name. This driver name must be defined in odbcinst.ini.