Hey Everyone...

I am try to move application to a location running MS SQL Server 2005. The connection script works as is for SQL 2000 but connection fails with 2005. I thought I had heard that you needed to include an additional library file (.dll) to make PHP scripts connect to SQL 2005. Does anyone know if this is true and perhaps know where this is documented? I have checked my settings at the server several times and they seem to be correct.

Thanks in advance!

Nick

How are you connecting? The rules about named pipes have changed between 2000 and 2005 versions.

PHP Code:

$server="Server_Name,1433";

$username="user_name";

$password="Pass";

$sqlconnect=mssql_connect($server, $username, $password); 


I know the port is correct. It is the default and I double check this with a port scanner. I have also tried creating and ODBC connection.. neither are working for me.

Is 1433 the port number? Are you using an IP address to specify the server?

Normally one would use:

SqlConnection conn = new SqlConnection("Server=myServer; Database=someDatabase; Integrated_Security=SSPI;");

Or of course you could hard code a specific uid and pwd.

If you're not specifying a database in your connection string - the one that must be generated by your mssql_connect fucntion - SQL is going to send you to the user's default database, which if you haven't set is master. But the user may not have permission to access the master database. That would provoke the error you're seeing. I'd look into the security within the database.

Do you have an error string?

Thanks for your reply. The user will always login to this database using a default user and pass. So, yes, these values are hardcoded as shown. Also this is a basic connection test and is not creating an instance of any class, 'SqlConnection'. This is strictly procedural and it works with a previous version of SQL server. I am specifying the database in the very next line (this wasn't included in last snippet).

PHP Code:

$sqldb=mssql_select_db("Audit",$sqlconnect); 


Basically all of the DB functions fail because of the failed connection.
The error strings are as follows:


Warning: mssql_connect() [function.mssql-connect]: Unable to connect to server: SUPPORTSQL,1433 in C:\xampp\htdocs\test_sqlserv.php on line 13

Warning: mssql_select_db(): supplied argument is not a valid MS SQL-Link resource in C:\xampp\htdocs\test_sqlserv.php on line 16

Warning: mssql_query() [function.mssql-query]: Unable to connect to server: (null) in C:\xampp\htdocs\test_sqlserv.php on line 22

Warning: mssql_query(): A link to the server could not be established in C:\xampp\htdocs\test_sqlserv.php on line 22

Warning: mssql_fetch_array(): supplied argument is not a valid MS SQL-result resource in C:\xampp\htdocs\test_sqlserv.php on line 25

Warning: mssql_close(): supplied argument is not a valid MS SQL-Link resource in C:\xampp\htdocs\test_sqlserv.php on line 31

I forgot to add...

I have tried both the IP and the DNS.

Originally Posted by SpudNik

Also this is a basic connection test and is not creating an instance of any class, 'SqlConnection'. This is strictly procedural and it works with a previous version of SQL server. I am specifying the database in the very next line (this wasn't included in last snippet).

I'm not really sure what this means? In any case, a connection is always made more or less the same way. The SQL Server locks its data files exclusively, and the only way you can get at the data is by talking to the server. If you're trying to avoid using the SQL Native Client, I'm not sure why?

What's the code in C:\xampp\htdocs\test_sqlserv.php at the lines suggested in the error dump? The one in particular about unable to connect to server (null) might be causing your problem?

Have you tried not giving a port number?

What I meant is that my connection logic may look a bit differant because this connection test application is not in an OOP style. Either would work. In any case this is the code:

PHP Code:

<?php

$server="10.0.0.55, 2433";

$username="audit";

$password="rp4dba";

$sqlconnect=mssql_connect($server, $username, $password);

$sqldb=mssql_select_db("Audit",$sqlconnect);

$sqlquery="SELECT User_Name FROM Users";

$results= mssql_query($sqlquery);

while ($row=mssql_fetch_array($results)){

echo $row['User_Name']."<br>\n";}

mssql_close($sqlconnect);

?>

As you can see it is just a simple app to test connectivity with the server. Line 22 is complaining that the argument is NULL which makes sence because I haven't established a connection with the server yet. I am beginning to think the problem must be at the server though I can't seem to track what that might be.

Do you need to specify a port number? I don't know much about the connections, but mine have always worked without specifying a port number, like this. Maybe it's a change from 2000 - 2005, I don't know.

$server="Server_Name";
$username="user_name";
$password="Pass";
$sqlconnect=mssql_connect($server, $username, $password);  

Hope this helps.

I don't use PHP but I've never had a problem connecting to a SQL Server v 2005 aka 9 using just the server name with no port.

Looks like we have a winner?


Accessing SQL Server 2005 Databases with PHP

SQL Server Technical Article
Deepak Vohra
September 2006
Applies to:
SQL Server 2005 Express Edition Database
Summary: PHP:Hypertext Preprocessor (PHP) is a scripting language that is suited for developing Web applications. A PHP script can be embedded in an HTML page and run as a .php script or as a Windows Script Host script (.wsf file). PHP 5.1.6 is the latest version of PHP and includes extensions for various databases including the SQL Server database. The SQL Server database extension in PHP 5 is installed by default when PHP is installed. With the SQL Server database extension, a connection can be established with the SQL Server 2005 database and SQL statements run on the database. The PHP extension supports databases created in different versions of SQL Server. This paper covers configuring the PHP extension with SQL Server 2005 Express Edition databases only. (18 printed pages)
Click here to download the Word document version of the article, AccessSQLwPHP.doc.

So you think SQL 2005 requires PHP 5?! Perhaps...

Thanks for the article. I'll give it a read. Thanks for your help!


-Nick

Could be. I'm not a PHP guy so I don't really know, but I guess the newness could explain things?