Lazy access Microsoft SQL Server from our linux box

In order to connect to our SQL Server we will need to install the following packages:

• FreeTDS (http://www.freetds.org)
• unixodbc
• its dependencies

Once we have them installed we will proceed to modify the /etc/freetds.conf file:

[root@web-devel-01 ~]# cat /etc/freetds.conf
[mydatabase]
Description     = SQL_Instance
Driver          = FreeTDS
Server          = 10.10.10.1
Port            = 1433
tds version     = 8.0
Database        = mydatabase
Trace           = no

Now we create the temporary file tds.driver with the following content:

[root@web-devel-01 ~]# cat tds.driver
[FreeTDS]
Description     = v0.63 with protocol v8.0
Driver          = /usr/lib64/libtdsodbc.so.0.0.0 # this location might change depending your linux distribution, installation path, etc

This file contains the data needed to register the FreeTDS driver. Let's install it trough this command:

[root@web-devel-01 ~]#odbcinst -i -d -f tds.driver

Also, we will install the data source:

[root@web-devel-01 ~]#odbcinst -i -s -f /etc/freetds.conf

If everything went ok, the odbcinst should have returned 0 value after execution and the driver will be contemplated in the file /etc/odbcinst.ini and the data source in the /etc/odbc.ini file.

Now let's try our connection:

[root@web-devel-01 ~]# isql mydatabase sa pepito
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

It's working!

If we would like to add support to our apache + php server, we need to install the package php-odbc. We can use the Data Source Name from the ODBC, or we can use only the driver and do a DSN less connection. This is an example code of it:

<? putenv("ODBCINI=/etc/odbc.ini");
putenv("ODBCINSTINI=/etc/odbcinst.ini");
$data_source='mydatabase';
$user='sa';
$password='pepito';
$database = 'mydatabase';

#$cn = odbc_connect("$data_source","$user","$password");  #DSN connection
$cn = odbc_connect("Driver=FreeTDS;Server=10.10.10.1;Database=mydatabase;UID=$user;PWD=$password;","$user","$password"); # DSN-less

print_r($cn);
die;
if (!$conn){
    if (phpversion() < '4.0'){
        exit("Connection Failed: . $php_errormsg" );
    }
    else{
        exit("Connection Failed:" . odbc_errormsg() );
    }
} ?>