Solved

# URGENT connect to SQL server

Posted on 2006-05-23
322 Views
im trying to connect to SQL server in our office using PHP.

im currently logged onto the network and all of our websites including PHP and IIS is installed on our SERVER01.

our SQL SERVER is installed on SERVER04 but does not have PHP or IIS installed on it.  I can get a connection suing ODBC's DSN configuration, but i dont know how to do this in PHP and query table

i have enabled php_msql.dll on SERVER01 but i get

Fatal error: Call to undefined function mssql_connect() in c:\Inetpub\wwwroot\admin\Personal\index.php on line 10

if i check the php.ini setting SQL is supported so im confused... my SERVER01 is windows 2000....

code:

echo (mssql_connect("Personell","ellanndrd","")) ? "YES": "NO";

0
Question by:ellandrd

LVL 142

Expert Comment

>i have enabled php_msql.dll on SERVER01 but i get
how?

usually, the php.ini file is the one in the windows folder...
0

LVL 49

Assisted Solution

Have you restarted the server after enabling msql.dll?

-r-
0

LVL 142

Accepted Solution

well, you might also want to enable php_mssql.dll
0

LVL 16

Author Comment

>>how?

i uncommented it.

my php.ini lives in c:\winnt\php.ini

i have copied msql.dll into winnt folder and system32 too...

>>Have you restarted the server after enabling msql.dll?

im in a office full of engineers all working off the server01 so its isnt really possible until...

php_mssql.dll & php_msql.dll is there a different?  Mmmm hold on i might i selected the wrong one...
0

LVL 16

Author Comment

am annoyed - i have php_msql.dll uncommented not php_mssql.dll...

let me try my code again and i'll get back to you...
0

LVL 142

Expert Comment

>php_mssql.dll & php_msql.dll is there a different?  Mmmm hold on i might i selected the wrong one...

yes, they are different. please enable php_mssql.dll and try again
0

LVL 16

Author Comment

OK better progress made - now im getting:

Warning: mssql_connect() [function.mssql-connect]: Unable to connect to server: SERVER04 in c:\Inetpub\wwwroot\admin\Personal\index.php on line 10
NO
0

LVL 16

Author Comment

my ODBC DSN pointer is called: Personell and it can connect to SQL server OK, but it in PHP that i need to get working,..
0

LVL 142

Assisted Solution

http://lu2.php.net/function.mssql-connect

you cannot use DSN entries, you have to put the servername directly
0

LVL 16

Author Comment

ok SQL server name is FILE04COMPANYNAME

so my code will be:

mssql_connect("FILE04COMPANYNAME","user","pwd")

??

whats this boolean link thingy at the end?  what goes there?
0

LVL 142

Expert Comment

you can ignore that for the moment (optional parameter)
0

LVL 16

Author Comment

well it still dont work...
0

LVL 16

Author Comment

the database im trying to access it highlighted:

www.seandelaney.co.uk/ee.bmp

the name of the server SQL is installed on is \\SERVER04

still not working

0

LVL 16

Author Comment

ok guys i still dont have it working! i need more help - i have never played with SQL server before - so i dont know how to work it or connect to it...
0

LVL 142

Assisted Solution

>the name of the server SQL is installed on is \\SERVER04

$db = mssql_connect("SERVER04","administrator","pwd"); or use the IP Address of that server... (port 1433 is the default) then, to select the database Personnel: http://lu.php.net/mssql_select_db mssql_select_db('Personnel',$db);

>still not working
please be more specify by posting error messages...
0

LVL 16

Author Comment

Warning: mssql_connect() [function.mssql-connect]: Unable to connect to server: SERVER04 in c:\Inetpub\wwwroot\admin\Personal\index.php on line 10
0

LVL 16

Author Comment

IP address of server01 (PHP and IIS is installed) is 192.168.0.2
IP address of server04 (SQL server is installed) is 192.168.0.5

my code:

dont work either...
0

LVL 142

Assisted Solution

>Warning: mssql_connect() [function.mssql-connect]: Unable to connect to server: SERVER04 in

please try this from command line (dos prompt):
telnet 192.168.0.5 1433

if that gives you any error, there is a problem on the network communication level, for example on SERVER04 a firewall or the like (ps: do you have SP3/SP4 of sql server 2000 installed, resp, what is the version?)

if that gives you a blank screen, the communication is well (the blank screen is because sql server does not really respond to the telnet commands, but the connection is done)

if you have installed any mssql client tools, try to connect with those...
0

LVL 16

Author Comment

>>telnet 192.168.0.5 1433

TELNET dialog appear but nothing is happening... no message appearing...

i will search for client tool...
0

LVL 142

Assisted Solution

>TELNET dialog appear but nothing is happening... no message appearing...
this is fine so far, as explained.

echo mssql_get_last_message();
0

LVL 16

Author Comment

i get

Warning: mssql_connect() [function.mssql-connect]: Unable to connect to server: 192.168.0.5 in c:\Inetpub\wwwroot\admin\Personal\index.php on line 12
0

LVL 16

Author Comment

from the client tool i get this:

--------------------------------------------------------------------------------

Impersonation failure.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Web.HttpException: Impersonation failure.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[HttpException (0x80004005): Impersonation failure.]
System.Web.ImpersonationSuspendContext.GetCurrentToken() +197
System.Web.ImpersonationSuspendContext.Suspend() +53
System.Web.HttpContext.GetConfig(String name) +104
System.Web.CustomErrors.GetSettings(HttpContext context, Boolean canThrow) +19
System.Web.HttpResponse.ReportRuntimeError(Exception e, Boolean canThrow) +39
System.Web.HttpRuntime.FinishRequest(HttpWorkerRequest wr, HttpContext context, Exception e) +485
--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573

does this mean im connected but there another problem?
0

LVL 142

Expert Comment

0

LVL 16

Author Comment

i didnt create it? i assume the username and password was that of the admin account for the whole network? no?
0

LVL 142

Expert Comment

>i assume the username and password was that of the admin account for the whole network? no?

in sql server, you have to explicitely create the logins you want to grant access to for connection.
I am not sure about php (did not try that yet) if it allows windows authentication, but I doubt.

you should create a SQL Logins, grant it permissions in the Personnel database, and use that login+password in the connection
0

LVL 16

Author Comment

ok i will try that.. but how?

i mean for "you should create a SQL Logins"? how?
0

LVL 142

Assisted Solution

well, on the box with sql server installed on it, you should find SQL Server Enterprise Manager (still assuming SQL Server 2000, if you use another version, please tell us)
In the enterprise manager, the (local) sql server is probably already registered, open it, and you should find a folder called Security, with a subfolder Logins.
in that folder (logins), you can manage the logins for that sql server instance.
grant the permissions to the database Personnel (that should be the third tab of the login settings)
once the login is created, the connection should work (at least I got that working in my environment)
0

LVL 16

Author Comment

OK i created new login and set up password and selected personnel database.  grant access was already selected so i left it alone...

i'll try connecting again so i'll brk
0

LVL 16

Author Comment

I still get unable to connect to server: SERVER04.

here is printscreen of the instance...

http://www.seandelaney.co.uk/mssql.JPG

0

LVL 142

Expert Comment

>SERVER04
the screenshot shows FILE04ABZPI and not SERVER04 ?!

what is this name of SERVER04?
0

LVL 16

Author Comment

the server mssql is installed on.

i have done so and still get same message
0

LVL 142

Expert Comment

and what is the name FILE04ABZPI shown in the screenshot?
0

LVL 16

Author Comment

is that not the name of mssql? i have never set up connections before or used mssql.  this is my first time and i was asked to connect to mssql using php as apart of a task...

0

LVL 142

Expert Comment

what is the output of (from dos command line):
PING FILE04ABZPI
PING SERVER04

0

LVL 16

Author Comment

here is another print screen - maybe this is more help.. as im getting confused

http://www.seandelaney.co.uk/mssql2.JPG
0

LVL 16

Author Comment

http://www.seandelaney.co.uk/pingfile04abzpi.JPG

pinging server04 nothing happen - could not find host...
0

LVL 142

Assisted Solution

in the service manager window, what is listed in the "server" list?

in the enterprise Manager, I see that you have a BKUPEXEC instance on that server, possibly (local) represents the default instance?

what are the properties of (local) in the enterprise Manager?
0

LVL 16

Author Comment

>>in the service manager window, what is listed in the "server" list?

there is load of items listed...

http://www.seandelaney.co.uk/list.JPG

>>in the enterprise Manager, I see that you have a BKUPEXEC instance on that server, possibly (local) represents the default instance?

i dont know what BKUPEXEC is - i assume my boss set this up to carry out backups....

the database i need to access is called "Personnel" and as you can see it is in default instance (local)(NT Windows).

>>what are the properties of (local) in the enterprise Manager?

when i do this, i get a dialog with lots of tabs - what info are you looking for?
0

LVL 16

Author Comment

in the enterprise manager, the (local) sql server. when i expand the folder called Security and views subfolders:

Server Roles
Remote Servers

under remote servers its name is FILE04ABZPI...

my PHP page exists on a different server than where the MS SQL server is on - you know this right?  (as i said this at the start of the question...)
0

LVL 142

Expert Comment

well, the connection with FILE04ABZPI should then work, can you show me how you defined the login?
0

LVL 16

Author Comment

what do you mean?

my code
0

LVL 142

Expert Comment

0

LVL 16

Author Comment

<?php
$server = "file04abzpi";$username = "sd";
$password = "???????";$sqlconnect = mssql_connect($server,$username, $password); #$sqldb = mssql_select_db("Personnel",$sqlconnect); #$sqlquery = "SELECT firstName FROM tblPersonnel;";

#$results = mssql_query($sqlquery);

#while ($row = mssql_fetch_array($results))
#{
#      echo $row['firstName']."<br>\n"; #} #mssql_close($sqlconnect);
?>
0

LVL 16

Author Comment

0

LVL 16

Author Comment

any luck?
0

LVL 142

Expert Comment

can you show the database tab on the same form, please
0

LVL 16

Author Comment

0

LVL 142

Assisted Solution

fine.
well, not alot of options left...

what does this return (run from command line)
0

LVL 16

Author Comment

osql not recognised... ive tried this on my machine OK?  do i do this on the FILE04ABZPI server?
0

LVL 16

Author Comment

if i do it on FILE04ABZPI
0

LVL 16

Author Comment

sorry pages submitted for some reason...?

if i do it on file04abzpi, its like it freezes and does nothing?
0

LVL 142

Expert Comment

does it give you a command prompt like (SQL>)?
possibly post the screenshot.

0

LVL 16

Author Comment

0

LVL 142

Assisted Solution

yes, this is fine (means it is connected).
you could type in the following 2 lines:

SELECT @@version
GO

and post here what you get.

Anyhow, now from the machine where your php should run on, run this (assuming that at least the sql client tools are installed):

0

LVL 16

Author Comment

>>assuming that at least the sql client tools are installed

no client tools is installed on the server where PHP is!
0

LVL 16

Author Comment

the tool was installed on my laptop...
0

LVL 142

Expert Comment

well, then please try the new osql as posted from your laptop
0

LVL 16

Author Comment

i get "osql" not recognised
0

LVL 142

Expert Comment

You just said it was installed on you laptop
0

LVL 16

Author Comment

yes and i cant connect to personnel database fine and query it but not using PHP...

the tool used OBDC DSN though... as ive one set up...
0

LVL 16

Author Comment

the command osql -Sfile04abzpi  -Usd -P<password> dont work - i get osql not recognised...
0

LVL 16

Author Comment

http://www.experts-exchange.com/Web/Web_Languages/PHP/Q_20814437.html

Extract below:

>>from the manual:

>>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. Configuration of the client will require >>installation of all the tools.

do i need to copy ntwdblib.dll from FILE04ABZPI to our server where PHP is installed as i done a search on our server (MAIL01ABZPI) where PHP is installed and i cant find that file.  if i search for it on FILE04ABZPI i can find it...
0

LVL 142

Assisted Solution

yes, you can copy that file.
that's new info for me, as I had already sql server installed on that box I didn't run into that problem.
0

LVL 16

Author Comment

it worked!
0

LVL 16

Author Comment

i cant believe this - it worked.... excellent! can eat now and pull myself out from this chair...
0

LVL 16

Author Comment

thank you!
0

LVL 142

Expert Comment

0

LVL 16

Author Comment

yes i know, but we didnt give up...
0

## Featured Post

This article will explain how to display the first page of your Microsoft Word documents (e.g. .doc, .docx, etc...) as images in a web page programatically. I have scoured the web on a way to do this unsuccessfully. The goal is to produce something …
Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
The viewer will learn how to dynamically set the form action using jQuery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.