Connect to MS SQL server in PHP7 on Ubuntu Linux 16.04 LTS with FreeTDS

Working with SQL Server in php on Linux can be a pain. I use FreeTDS to solve this issue.

PHP7 odbc

To install this module run the following command in the terminal.

apt-get install php7.0-odbc

FreeTDS

To install and configure FreeTDS run the following commands in the terminal.

apt-get install freetds-bin freetds-common tdsodbc odbcinst unixodbc unixodbc-dev
mv /etc/odbcinst.ini /etc/odbcinst.ini.bak
cp /usr/share/tdsodbc/odbcinst.ini /etc/

Test the connection in the terminal

You can test the connection by using 'tsql'. When you see an error or some counter there is something wrong. If things go well you get an sql prompt where you can run queries. Don't forget to type 'go' and [enter] to execute the query. Type 'exit' and [enter] to close the prompt.

tsql -H my_server_host -p 1433 -U my_user -P my_password -D my_database

Test everything in PHP7

If the terminal test is OK then you can use this code to test the connection in php.

try {

    //connection params
    $dbCon = new PDO('odbc:Driver=FreeTDS; Server=my_server_host; Port=1433; Database=my_database; TDS_Version=7.4; ClientCharset=UTF-8', 'my_login', 'my_pass');

    //test query
    $result = $dbCon->query('SELECT TOP 10 * FROM dbo.my_table');

    //show the results
    foreach ($result as $row)
    {
        print_r( $row );
    }

    //close the connection
    $dbCon = null;

} catch (PDOException $e) {

    //show exception
    echo $e->getMessage();

}

Edit on the 5th of March 2018

After using this procedure I ran into problems. The FreeTDS driver is not able to process varchar(MAX) columns properly and doesn't support PDO very well. An update of this procedure you can find on Connect to MS SQL server in PHP7 on Ubuntu Linux 16.04 LTS with Microsoft Drivers

David Van De Walle