Unable to connect to msSQL database via PHP

Question

I am using the current code in attempt to access a msSQL 2005 db:

<?php
$myServer = "[server]";
$myUser = "[username]";
$myPass = "[password]";
$myDB = "[db]";

//connection to the database
$dbhandle = mssql_connect($myServer, $myUser, $myPass)
  or die("Couldn't connect to SQL Server on $myServer");

//select a database to work with
$selected = mssql_select_db($myDB, $dbhandle)
  or die("Couldn't open database $myDB");

//declare the SQL statement that will query the database
$query = "SELECT id, name, year ";
$query .= "FROM cars ";
$query .= "WHERE name='BMW'";

//execute the SQL query and return records
$result = mssql_query($query);

$numRows = mssql_num_rows($result);
echo "<h1>" . $numRows . " Row" . ($numRows == 1 ? "" : "s") . " Returned </h1>";

//display the results
while($row = mssql_fetch_array($result))
{
  echo "<li>" . $row["id"] . $row["name"] . $row["year"] . "</li>";
}
//close the connection
mssql_close($dbhandle);
?>

It is returning the following:

Warning: mssql_connect() [function.mssql-connect]: Unable to connect to server: XXXXXXX in D:\xxxxx.xxx\xxxx.php on line 16
Couldn't connect to SQL Server on XXXXXXX

What do you think the problem is?

Answer 1

Invalid credentials, if your not using localhost be sure you add the server's ip to the safe list.

Answer 2

Try calling mssql_get_last_message() to get the last error message:

$dbhandle = mssql_connect($myServer, $myUser, $myPass)
  or die("Couldn't connect to SQL Server on $myServer. Error: " . mssql_get_last_message());

Answer 3

It sounds to me like one of your DLLs is the wrong version. There was an issue of some sort with the move from SQL2000 to SQL2005 that the creators of PHP didn't resolve themselves. There are a variety of posts about it here: the following link

I believe the DLL is ntwdblib.dll and the version needs to be version 2000.80.194.0 at the very least. If you're running Apache or WampServer, there is an identical dll where the Apache DLLs are stored that needs to be overwritten. Unfortunately, I don't have a link to download the correct version of this DLL but the aformentioned page has several links to it.

Note: I was having this issue a few days ago and finding the correct DLLs and overwriting both allowed it to work.

Also: You may need to setup remote connections. Sql Server 2005 has remote connections disabled by default. You can allow remote connections by running the SQL Surface Area Configuration utility.

Answer 4

Where is the Safe List?

Answer 5

I had some difficulties with this a few months ago, and I found that the only way to make it work was to include the instance name when specifying the server. For example:

$myServer = "SERVER\INSTANCENAME";

Specifying just the server would not work, even with TCP/IP enabled.

Answer 6

I am trying to connect the SQL Server 2008 from CentOs. I am not able to connect it from the command mssql_command. If I am using the tsql command from the terminal or shell i am able to connect to the database.

Can any one help

I have installed php-mssql php-devel freetds freetds-devel unixODBC unixODBC-devel

Anything I missed out. Please advice