The Sysadmins

Tips and tricks from the Sysadmins

Ubuntu Server – Connect to MSSQL via PHP

I was asked to look at querying a MSSQL database from an Ubuntu server. After some digging, bad information and trial & error it turned out to be very straight forward.

This has been tested on Ubuntu 8.10 and Microsoft SQL Server 2000/2008.

From the Ubuntu server

sudo apt-get install php5-sybase php5-odbc freetds-common

Configure /etc/freetds/freetds.conf, edit the entry at the bottom to point to your SQL server.

Ubuntu Freetds Conf

Test connection to the MSSQL server with this PHP script

<?php

$server = 'servername';
$username = 'sa';
$password = 'password';
$database = 'xxx';
$connection = mssql_connect($server, $username, $password);

if($connection != FALSE)
{
echo "Connected to the database server OK<br />";
}
else
{
die("Couldn't connect");
}

if(mssql_select_db($database, $connection))
{
echo "Selected $database ok<br />";
}
else
{
die('Failed to select DB');
}

$query_result = mssql_query('SELECT @@VERSION');
$row = mssql_fetch_array($query_result);

if($row != FALSE)
{
echo "Version is {$row[0]}<br />";
}
mssql_free_result($query_result);
mssql_close($connection);

You can browse to the location of the PHP via your browser, or run php filenameyouchose.php from the cli. This should return the version of the SQL server and give you confirmation that it worked.

Connected To MSSQL

9 Comments

  1. Hi !

    Thanks for your post. It helps me connect my php apps to sql server.

    Now, I want to connect to a server with an instance name. An example of my sql server name : 192.168.1.1\instance_name

    What to do to make the connection possible ? I’m checking my firewall to fix the problem, but nothing till now.

    Can you help me ?

    Thanks.

  2. Hi !

    I’ve tried $server = ‘servername\instance’; as suggested by nothing at all.
    My firewall tells me something connecting to the ‘servername’ on port 1434, but nothing else.

    I have another server with sql server 2005, without instance name (default instance). The snippet runs successfully. But as soon as I want to connect to a server with instance name, it fails.

    I’ll trie another way later. If it helps, you will be informed.

    Thanks. Enjoy your week-end.

  3. You save my day! thank you! works perfectly!

  4. Thanks a lot!

  5. Murillo Sampaio leite

    March 1, 2016 at 5:52 pm

    Thanks brother!

  6. PHP 7 removes mssql_* functions. You’re going to need to get PDO working instead. Good luck with that.

  7. Works perfectly on Ubuntu 15.10. But on 14.04, I have a problem with DateTimes…
    INSERT INTO tbl_test (mydate) VALUES (’12-3-2004′) fails with:
    message: Cannot insert the value NULL into column ‘mydate’ column does not allow nulls. INSERT fails.

    mydate doesn’t allow NULLs, but in the Query the date is clearly set. The Query runs fine on MS SQL Management Studio, but via PHP the query fails.
    Anyone know what could be at fault here?

  8. Thank you, man!

Leave a Reply

Your email address will not be published.

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.