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.
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.
August 14, 2014 at 6:18 pm
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.
August 14, 2014 at 8:05 pm
Hello, have you tried $server = ‘servername\instance’; -> http://php.net/manual/en/function.mssql-connect.php
August 22, 2014 at 4:38 pm
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.
January 26, 2015 at 11:17 pm
You save my day! thank you! works perfectly!
November 25, 2015 at 10:02 am
Thanks a lot!
March 1, 2016 at 5:52 pm
Thanks brother!
April 6, 2016 at 3:29 pm
PHP 7 removes mssql_* functions. You’re going to need to get PDO working instead. Good luck with that.
September 8, 2016 at 8:45 am
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?
October 27, 2016 at 11:56 am
Thank you, man!