Configuring a Linux Server to Connect to an MSSQL Database Using ODBC

This article is also available in our new Knowledge Base: Configuring a Linux Server to Connect to an MSSQL Database Using ODBC

Applies To

Zend Server 8.5, 9
GNU / Linux (CentOS, RHEL)

Summary

To connect to an MSSQL database from a Linux server via PHP ODBC, along with the PHP extensions odbc and mssql, you must also install and configure additional libraries on your server - FreeTDS and unixODBC.

Instructions

Roughly, these are the steps you need to complete:

1. Installing FreeTDS and unixODBC

Note: You may need to install a third party repository such as EPEL in case your distribution's repository doesn't contain these packages:

• Install unixODBC and FreeTDS libraries:

# yum install unixodbc freetds

2. Registering the ODBC driver with freeTDS

• Locate the path for the libtdsodbc.so library on your server:

$ ldconfig -p | grep libtdsodbc
libtdsodbc.so.0 (libc6,x86-64) => /lib64/libtdsodbc.so.0

• Create and edit files /etc/odbcinst.ini and /etc/odbc.ini with following contents:

/etc/odbcinst.ini

[FreeTDS]
Description = Freetds v 0.95
Driver = /lib64/libtdsodbc.so.0

(Driver must be the exact path from the output of the command ldconfig)

/etc/odbc.ini

[MSSQLServer]
Driver = FreeTDS
Description = Any description
Trace = No
Server = 192.168.0.30
Port = 1433
TDS version = 0.95
Database = ApplicationDB

(use the correct values of Server IP, MSSQL Port and Database name)

Note: The name in square brakets is the Data Source Name (DSN) that you should use in your PHP script.

3. Testing MSSQL connection via command line

• Test the connection to the MSSQL database:

$ isql -v MSSQLServer <DBuser> <DBpass>

(use the correct values of Data Source Name, Database user and Database password)

This should open an SQL prompt where you can try some queries

4. Creating symlinks to the .ini files

• Create the following symlinks in Zend Server's etc directory to make the ODBC configuration work with PHP. Execute the following commands:

# ln -s /etc/odbc.ini /usr/local/zend/etc/odbc.ini
# ln -s /etc/odbcinst.ini /usr/local/zend/etc/odbcinst.ini

5. Testing MSSQL connection via PHP

• Following is the sample PHP code to test the MSSQL connection using odbc_connect ():

<?php

// Replace the value's of these variables with your own data:
    $dsn = "MSSQLServer"; // Data Source Name (DSN) from the file /usr/local/zend/etc/odbc.ini
    $user = "DBuser"; // MSSQL database user
    $password = "DBpass"; // MSSQL user password

$connect = odbc_connect($dsn, $user, $password);

//Verify connection
if ($connect) {
    echo "Connection established.";
    odbc_close($connect);
} else {
    die("Connection could not be established.");
}