Cannot connect to SQL Server 2005 using PHP

Here is my configuration:

I have a virtual server set up called TestServer1.  On it is installed SQL Server 2005, IIS, and PHP.  In SQL Server I have set up a Guest account with password "password".  It has full privledges (which I know is dumb but I'm ruling out a permissions error with this.)

On my desktop I have SQL Server 2005 client tools installed.

Using the PHP code below I load the PHP file on my desktop (or on the server) in IE and die and get the message "Couldn't connect to sQL Server on TestServer1".  

But from my desktop using SQL Server's Management Studio, using the Guest account I can connect just fine and view my test database.

Any ideas as to where my point of failure is?

Thanks
<html>
<head>
<title> PHP Test Script </title>
</head>
<body>
<?php
 
$myServer = "TestServer1";
$myUser = "guest";
$myPass = "password";
$myDB = "PHPTest"; 
 
//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 fname, lname, city";
$query .= "FROM testData";
$query .= "WHERE city='Springfield'"; 
 
//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);
?>

Open in new window

thearniecAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Beverley PortlockCommented:
Change your message like this


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

What is the error message text?
0
Beverley PortlockCommented:
Sorry - old habits die hard - that should have read



//connection to the database
$dbhandle = mssql_connect($myServer, $myUser, $myPass)
  or die("Couldn't connect to SQL Server on $myServer - error was " . msql_error()  );
 
0
thearniecAuthor Commented:
It then only gives me a blank screen.  I also tried changing mysql_error() to mssql_error() and still just a blank screen.  
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Beverley PortlockCommented:
It is just "msql_error" and not "mssql_error" . Also it shoud be msql_connect and not mssql_connect
0
Beverley PortlockCommented:
If none of the above works try altering your code like so

<?php
 error_reporting(E_ALL);

to turn error reporting on, but it looks to me like you are doubling your "s"s msql to mssql. PHP will not report a "dud" function unless error logging instructs it to do so.
0
thearniecAuthor Commented:
Still no go, a blank screen.  I'm attaching my code in its new form to show the error logging and that I've removed all double s-s, replacing all mssql with msql statements.

But yeah, still getting a blank screen (and I tried basic PHP statements, functions, echos, and they work so it's not a PHP server config problem I don't think...)
<html>
<head>
<title> PHP Test Script </title>
</head>
<body>
<?php
error_reporting(E_ALL);
 
 
$myServer = "TestServer1";
$myUser = "guest";
$myPass = "password";
$myDB = "PHPTest"; 
 
//connection to the database
$dbhandle = msql_connect($myServer, $myUser, $myPass)
  or die("Couldn't connect to SQL Server on $myServer - error was " . msql_error()  );
 
 
//select a database to work with
$selected = msql_select_db($myDB, $dbhandle)
  or die("Couldn't open database $myDB"); 
 
//declare the SQL statement that will query the database
$query = "SELECT fname, lname, city";
$query .= "FROM testData";
$query .= "WHERE city='Springfield'"; 
 
//execute the SQL query and return records
$result = msql_query($query);
 
$numRows = msql_num_rows($result); 
echo "<h1>" . $numRows . " Row" . ($numRows == 1 ? "" : "s") . " Returned </h1>"; 
 
//display the results 
while($row = msql_fetch_array($result))
{
  echo "<li>" . $row["id"] . $row["name"] . $row["year"] . "</li>";
}
//close the connection
msql_close($dbhandle);
 
 
?>
</body>
</html>

Open in new window

0
thearniecAuthor Commented:
I should add I am getting HTTP 500 -- Internal server error.  I just now noticed it's at the head of my IE window.
0
thearniecAuthor Commented:
Upped to 500 points due to a 500 Internal Server Error...  Seems appropriate :)
0
nplibCommented:
2 things
use the IP address, cause it probably isn't resolving TestServer1 to the MS SQL Server

second thing

$query = "SELECT fname, lname, city";
$query .= "FROM testData";
$query .= "WHERE city='Springfield'";
will give an error,

it will output like this

SELECT fname, lname, cityFROM testDataWHERE city='Springfield'

notive the lack of spaces, so once you do get connected you will have another hurdle.

change it to this
$query = "SELECT fname, lname, city ";
$query .= "FROM testData ";
$query .= "WHERE city='Springfield'";
0
thearniecAuthor Commented:
same error using the IP address; HTTP 500 Internal server error.  No error message displayed.

Thank you for your help, BTW, I do appreciate the advice.  But not there yet.
0
nplibCommented:
try localhost,

your sql server may not be configured to listen on that IP address.

is the sql server and the web server on the same machine?
0
thearniecAuthor Commented:
Tried localhost and exactly the same problem.  

And yeah, SQL and IIS are both on the same box.
0
nplibCommented:
make a new blank php page

just have this.

http 500 means it can't find the page itself, the contents are irrelevant.

<?php
echo "Hello World";
?>

Open in new window

0
thearniecAuthor Commented:
Yeah, "hello world" works perfectly.

I even added in some echo statements in the original code, and it echos down to the line

$dbhandle = msql_connect($myServer, $myUser, $myPass)
  or die("Couldn't connect to SQL Server on $myServer - error was " . msql_error()  );

That's where everything bombs out, nothing after that line works, so I'm thinking that's the line causing it to throw the 500
0
nplibCommented:
$dbhandle = msql_connect($myServer, $myUser, $myPass)
  or die("Couldn't connect to SQL Server on $myServer - error was " . msql_error()  );

that should be
$dbhandle = mssql_connect($myServer, $myUser, $myPass)
  or die("Couldn't connect to SQL Server on $myServer - error was " . mssql_error()  );

the functions are mssql_x

make sure they all have the mssql_something
like msql_select_db
should be
mssql_select_db
0
Beverley PortlockCommented:
In php.ini, have you set

extension=msql.so

(or possibly mmsql.so or .dll for windows)
0
Beverley PortlockCommented:
I've done some digging at http://uk.php.net/mssql and many of the problems with ms-sql seem to revolve around ntwdblib.dll and there may be hints at the above URL

The extension BTW, appears to be

extension=mssql.dll

although the PHP pages says it should be extension=php_mssql.dll


You should also be aware of this....

 Requirements for Win32 platforms.

The extension requires the MS SQL Client Tools to be installed on the system where PHP is installed. The Client Tools can be installed from the MS SQL Server CD or by copying ntwdblib.dll from \winnt\system32 on the server to \winnt\system32 on the PHP box. Copying ntwdblib.dll will only provide access through named pipes. Configuration of the client will require installation of all the tools.
0
thearniecAuthor Commented:
In my php.ini I had extension=php_mssql.dll enabled, I just enabled php_msql.dll and rebooted the box.  Doing that changed the behaviorwhen using the msql_connect statements as instructed above by bportlock.  

But now I get a 500 error, but instead of a blank screen it says:  Couldn't connect to SQL Server on localhost - error was

(I didn't truncate that, that is all that appears).

I then switched to the mssql as advised by nplib (which was how I had them originally).  Same thing:  Couldn't connect to SQL Server on 170.27.7.81 - error was

(I tried the IP, the servername, and localhost on both msql_connect and mssql_connect).

And the debug echo statements I've inserted show it dying on the msql_connect (or mssql_connect) line
0
nplibCommented:
do you know what port your MS Sql server is listening on.

if you do then it should look like this.

$myServer = "170.27.7.81:5000"; ///or what ever port number

$dbhandle = mssql_connect($myServer, $myUser, $myPass)
  or die("Couldn't connect to SQL Server on $myServer - error was " . mssql_error()  );

then double check that there is no firewall blocking that port if so, open that port to that ip address.
0
thearniecAuthor Commented:
BPortlock,

I have SQL Server 2005 management studio on the server, which I believe comprises the client tools, yes?

I have ntwdblib.dll version 2000.2.8.0 in C:\PHP, C:\windows,  and C:\windows\system32

I had looked at that uk.php.net site before posting here...I just wasn't able to make any of their solutions work for me :(
0
Beverley PortlockCommented:
thearniec said "I have SQL Server 2005 management studio on the server, which I believe comprises the client tools, yes?"

I don't know - I'm a LAMP weenie rather than a Windows one, but it is hard to see what could be wrong. Further digging has turned this link up http://forum.umbraco.org/installing/problem-connecting-microsoft-sql-server-2005

and the principal "bit" seems to be

Have you enabled SQL Server Authentication? It's disabled by default...
If you haven't done it already, this is how you do:
1. Start SQL Server Management Studio Express
2. Select Security
3. In the Server Authentication section make sure you have marked SQL Server and Windows Authentication Mode.
4. Restart the SQL Server Service

If that doesn't make any difference check your logs files after a failed login attempt. They're located here by default:
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG

Look in the LOG file for anything like:
2006-08-16 09:31:16.33 Logon Error: 18456, Severity: 14, State: 8.

"State: 8" indicates that there's something wrong with a User Password, I had this one due to a whitespace after the password when i first wrote it ; )
0
thearniecAuthor Commented:
BPORTLOCK:  You're onto something!

When I enabled the msql.dll and rebooted the server it started to take longer to give me the 500 error.  But the log you pointed me to now shows an error that only started being recorded after the reboot.  I changed the variable back to

$myServer = "TestServer1";
$myUser = "guest";
$myPass = "password";
$myDB = "PHPTest";

echo "Variables Initialized";

//connection to the database
$dbhandle = mssql_connect($myServer, $myUser, $myPass)
  or die("Couldn't connect to SQL Server on $myServer - error was " . msql_error()  );
2008-02-05 16:04:56.44 Logon       Login failed for user TestServer1\IUSR_MMC-SHAREPOINT'. [CLIENT: <named pipe>]

But why is it not trying to log in as guest?
0
thearniecAuthor Commented:
I guess I should add the next line

 Error: 18456, Severity: 14, State: 11.
0
Beverley PortlockCommented:
See here

http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx



Which says "

States 11 and 12 simply mean the Windows user coming in does not have login access to the server.  SQL Server validated the Windows user's token, figured out who it is, but the Windows user has not been granted access to the server.
Thursday, April 19, 2007 12:57 PM by Matt Neerincx (MSFT)
# SQL Ranger - April 26th


For State 11 - the login ID did not have a profile...I logged in to create the profile and all is right with the world!
0
thearniecAuthor Commented:
Okay, I have fought this for an hour last night and 2.5 this morning.  Still stuck.

I really thought bportlock was onto something.  So I worked and worked on that IUSR_TestServer1 account.  I went through steps to change its password and then synch the password between the services, and even did a remote log on with that user after making that user admin of the box.  Still no matter what I do, I get:

2008-02-06 09:49:13.89 Logon       Error: 18456, Severity: 14, State: 11.
2008-02-06 09:49:13.89 Logon       Login failed for user 'TestServer1\IUSR_TestServer1'. [CLIENT: <named pipe>]

What do I need to do to get this account access, especially given that I've tried even using an admin username and password in the PHP code.
0
nplibCommented:
Um you don't want to give that account access.
That is an unsecured account, you give them access to your DB you are looking for trouble.

this may be of some help
***First Step***
Check your version of "ntwdblib.dll". If your version is 2000.2.8.0, then check the post below to see how to download the version 2000.80.194.0 of "ntwdblib.dll".

***Second Step***
Make sure your DB Server is in Mixed Mode Authentification. Enable "sa" account and give it a strong pass.

And the code here, that just worked for me:

<?php
    session_start();
    set_time_limit(0);
    error_reporting(E_ALL);

    //complete "station\sqlexpress" with your particular case
    $SERVER = "station\sqlexpress";
    $ADMIN_NAME = "sa";
    //complete pass with your pass
    $ADMIN_PASS = "pass";
    //complete database witht the name of database you whant to connect to
    $DATABASE = "database";

    $Conexion = mssql_connect($SERVER, $ADMIN_NAME, $ADMIN_PASS)
        or die ("Can't connect to Microsoft SQL Server");
     
    mssql_select_db($DATABASE, $Conexion)
        or die ("Can't connect to Database");
?>

and this
Also, if you're getting authentication errors (I was on a different server), check to make sure that mssql.secure_connection is set to 0 or commented out. A 1 setting will cause the web server to use its Windows authentication (IUSR_) to log in to the SQL Server.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
thearniecAuthor Commented:
It was that freaking secure connection Php.ini setting.  Sheesh...

All the stuff I was reading suggested I turn it on to make connecting easier.  I read your above post, remembered turning it on, turned it off, and viola.  Connection made.

Thank you.

And AAAARGHHH at the simple fix that has caused me such frustration the past 3 days.

THANK YOU!!!!!!!!
0
nplibCommented:
also, the whole part "Login failed for user 'TestServer1\IUSR_TestServer1'. [CLIENT: <named pipe>]"
just didn't click for me until this morning.

If I was on the ball yesterday, we could have gotten this thing finished then.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.