Oracle, PHP5, Apache2, oci_connect fails but sql*plus works

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi -

after I got sql*plus to work on my Linux box and I recompiled PHP 5 to
include the oracle instant client, I run into a weird problem:

I get 'ORA-01017: invalid username/password; logon denied' when using the
same combination of username/password and Oracle instance that works in
sql*plus on the same machine.

The PHP page finds the tnsnames.ora alright cause when I use a different
name that is not in there, it gives me an error that it couldn't be found,
so I don't think that the tnsnames.ora is the reason here.

I also tried all weird combination of upper and lowercase, which doesn't
seem to matter to sql*plus, so I figure it doesn't make a difference in
PHP.

Has anybody in this newsgroup ever run into the same problem?

Thanks,
Ruben
- --
Ruben Schoenefeld, ICQ 1-971-310, http://support.uni-oldenburg.de/~ruben/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (GNU/Linux)

 iD8DBQFCScNuKxQwayh8oDERAmA5AJsHJAHPHbGq
1wiBjvNJ/69fxEMPAQCfcF+2
NEJQnpljYN6skmVP+nHYf9E=
=o7vr
-----END PGP SIGNATURE-----

On Tue, 29 Mar 2005 23:06:49 +0200, Ruben Schoenefeld
<Ruben.Schoenefeld@uni-oldenburg.de> wrote:

>after I got sql*plus to work on my Linux box and I recompiled PHP 5 to
>include the oracle instant client, I run into a weird problem:
>
>I get 'ORA-01017: invalid username/password; logon denied' when using the
>same combination of username/password and Oracle instance that works in
>sql*plus on the same machine.
>
>The PHP page finds the tnsnames.ora alright cause when I use a different
>name that is not in there, it gives me an error that it couldn't be found,
>so I don't think that the tnsnames.ora is the reason here.
>
>I also tried all weird combination of upper and lowercase, which doesn't
>seem to matter to sql*plus, so I figure it doesn't make a difference in
>PHP.

Yes, usernames and passwords are case-insensitive in Oracle.

You're not trying to log on as SYS, are you? Running SQL*Plus from a user th
at
is the member of the dba group (which can log without getting the password
correct in certain circumstances)?

They're the only situations that spring immediately to mind that could cause
this, assuming the network config is actually correct and you _are_ connecti
ng
to the same database...

--
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Today Andy Hassall wrote in group alt.php.sql:

> On Tue, 29 Mar 2005 23:06:49 +0200, Ruben Schoenefeld
> <Ruben.Schoenefeld@uni-oldenburg.de> wrote:
> 
>
> Yes, usernames and passwords are case-insensitive in Oracle.
>
> You're not trying to log on as SYS, are you? Running SQL*Plus from a user 
that
> is the member of the dba group (which can log without getting the password
> correct in certain circumstances)?
>
> They're the only situations that spring immediately to mind that could cau
se
> this, assuming the network config is actually correct and you _are_ connec
ting
> to the same database...

Hi -
I am not trying to login as SYS, or anybody in the dba group. It's a
normal user that works fine in sql*plus, but not in php.

I figured that I am connecting to the same database because when I changed
the Oracle instance to bogus, it told me that the tns-entry could not be
found (which was expected).

I found a few things online that made me look into the NLS_LANG-setting a
little more and they found that when it is the default
(AMERICAN_AMERICA.WE8ISO8859P1) it would work ... but that's not the case
here, even the default doesn't improve the situation.
I have all the environment variables right and I even reset them all in
the PHP-file, using putenv, but even that does not help.

Anyone with some more ideas?

Thanks,
Ruben
- --
Ruben Schoenefeld, ICQ 1-971-310, http://support.uni-oldenburg.de/~ruben/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (GNU/Linux)

 iD8DBQFCSclLKxQwayh8oDERAoACAJ46SRIQJZOi
mZEfwa79DFBp56cKaQCcCUzo
1c8tR+H9aJxv7itEXT4OU94=
=OWeJ
-----END PGP SIGNATURE-----

On Tue, 29 Mar 2005 23:31:51 +0200, Ruben Schoenefeld
<Ruben.Schoenefeld@uni-oldenburg.de> wrote:

>I am not trying to login as SYS, or anybody in the dba group. It's a
>normal user that works fine in sql*plus, but not in php.
>
>I figured that I am connecting to the same database because when I changed
>the Oracle instance to bogus, it told me that the tns-entry could not be
>found (which was expected).

But that's not conclusive that you're connecting to the same database... are
there other databases around on your network?

If you want to be more sure, you can skip the tnsnames.ora and connect
directly, e.g. take all the spaces and carriage returns out of the tnsnames.
ora
entry and use it as the database name, so it looks something like:

$conn = oci_connect('user', 'password',
 '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PR
OTOCOL=TCP)(HOST=testbox)(PORT=1521)
 ))(CONNECT_DATA=(SERVICE_NAME=TEST101)))
');

>I found a few things online that made me look into the NLS_LANG-setting a
>little more and they found that when it is the default
>(AMERICAN_AMERICA.WE8ISO8859P1) it would work ... but that's not the case
>here, even the default doesn't improve the situation.

NLS_LANG shouldn't affect authentication, so it's unlikely to be the culprit
.

>I have all the environment variables right and I even reset them all in
>the PHP-file, using putenv, but even that does not help.

What's TNS_ADMIN set to, both in the session where SQL*Plus works, and under
PHP?

And just checking the obvious - you've not accidentally transposed the
username and password parameters in the call to oci_connect?

--
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Today Andy Hassall wrote in group alt.php.sql:

> But that's not conclusive that you're connecting to the same database... a
re
> there other databases around on your network?
>
> If you want to be more sure, you can skip the tnsnames.ora and connect
> directly, e.g. take all the spaces and carriage returns out of the tnsname
s.ora
> entry and use it as the database name, so it looks something like:
>
> $conn = oci_connect('user', 'password',
>  '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PR
 OTOCOL=TCP)(HOST=testbox)(PORT=1521)))(C
ON
NECT_DATA=(SERVICE_NAME=TEST101)))');

That's one of the things I've already tried ... same result though. I am
pretty certain that it accesses the same database.

> NLS_LANG shouldn't affect authentication, so it's unlikely to be the culprit.[/col
or]

Yup, everything I tried with NLS_LANG gave me the same error, too. But it
was worth a try.

> What's TNS_ADMIN set to, both in the session where SQL*Plus works, and und
er
> PHP?
It's the same in both the PHP and the SQL*Plus environment as:
/usr/lib/oracle/10.1.0.3/client/network/admin
That directory contains both the tnsnames.ora and sqlnet.ora.

> And just checking the obvious - you've not accidentally transposed the
> username and password parameters in the call to oci_connect?

Nope, checked that a few times too. I'll just copy and paste the simpliest
php file that I am using for testing this.

<?php
$connection=oci_connect("XuserX", "XpassX", "XtnsnameX");
?>
does not work

in the shell:
sqlplus XuserX/XpassX@XtnsnameX
does work

I'm a little lost ...

Ruben
- --
Ruben Schoenefeld, ICQ 1-971-310, http://support.uni-oldenburg.de/~ruben/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (GNU/Linux)

 iD8DBQFCSdC8KxQwayh8oDERAhRBAKC2J4jD5kc5
2aYzbI4YThO4ssJhmACfZppU
M+7sYQBmslBG+S4jwLVXDss=
=tNlS
-----END PGP SIGNATURE-----

On Wed, 30 Mar 2005 00:03:37 +0200, Ruben Schoenefeld
<Ruben.Schoenefeld@uni-oldenburg.de> wrote:

>Today Andy Hassall wrote in group alt.php.sql:
>
>Nope, checked that a few times too. I'll just copy and paste the simpliest
>php file that I am using for testing this.
>
><?php
>   $connection=oci_connect("XuserX", "XpassX", "XtnsnameX");
>?>
>does not work
>
>in the shell:
>sqlplus XuserX/XpassX@XtnsnameX
>does work

Does it work if you use the command-line PHP from the same shell where sqlpl
us
works? i.e.

php -q yourscript.php

At least then you know that both will have the same environment, which narro
ws
things down a little if it works.

--
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Tuesday the 29th day of March 2005 Andy Hassall wrote in group alt.php.sq
l:

> On Wed, 30 Mar 2005 00:03:37 +0200, Ruben Schoenefeld
> <Ruben.Schoenefeld@uni-oldenburg.de> wrote:
> 
>
> Does it work if you use the command-line PHP from the same shell where sql
plus
> works? i.e.
>
> php -q yourscript.php
>
> At least then you know that both will have the same environment, which nar
rows
> things down a little if it works.

That was a good hint, thanks ...

from the command line, it works, I get a connection.

Now to narrow things down a little more:
- - apache runs as user wwwrun and the .ora config files are readable for
all users (tried executable too, no change)
- - using phpinfo() to create a webpage that includes the environment shows
the following for oci8:
OCI8 Support 	enabled
Revision 	$Revision: 1.257.2.5 $
Active Persistent Links 	0
Active Links 	-1
Oracle Version 	10.1
Compile-time ORACLE_HOME 	/usr/lib/oracle/10.1.0.3/client/lib
Libraries Used 	no value
Temporary Lob support 	enabled
Collections support 	enabled
- - same phpinfo() output for Environment (Oracle-relevant fields only):
LD_LIBRARY_PATH 	/usr/lib/oracle/10.1.0.3/client/lib
TNS_ADMIN 	/usr/lib/oracle/10.1.0.3/client/network/admin
SQLPATH 	/usr/lib/oracle/10.1.0.3/client/lib
ORACLE_HOME 	/usr/lib/oracle/10.1.0.3/client

... Getting somewhere I guess, since the script itself works from the
command line, but not on the webserver ...
- --
Ruben Schoenefeld, ICQ 1-971-310, http://support.uni-oldenburg.de/~ruben/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (GNU/Linux)

 iD8DBQFCSdcvKxQwayh8oDERAmmXAJ9oXXU6NsES
yEud+PGsHgXLdX0OPgCeL608
FgN7n2+aeCR2XiVt1nbIvcs=
=+lFP
-----END PGP SIGNATURE-----

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Tuesday the 29th day of March 2005 Andy Hassall wrote in group alt.php.sq
l:

> On Wed, 30 Mar 2005 00:03:37 +0200, Ruben Schoenefeld
> <Ruben.Schoenefeld@uni-oldenburg.de> wrote:
> 
>
> Does it work if you use the command-line PHP from the same shell where sql
plus
> works? i.e.
>
> php -q yourscript.php
>
> At least then you know that both will have the same environment, which nar
rows
> things down a little if it works.
>
>

I can't believe it but I just restarted the server (again) and now it
seems to work. Thought shutting down the whole system to restart only one
service are a thing of the past (Windows), but this seemed to have helped.

Thank you for helping, Andy. The thing about it running on command line
and not on apache really was a good hint.

Bye for now,
Ruben
- --
Ruben Schoenefeld, ICQ 1-971-310, http://support.uni-oldenburg.de/~ruben/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (GNU/Linux)

 iD8DBQFCSdtsKxQwayh8oDERAmucAJ0QymNEKlvs
Otk93kEwfzTe8QglZgCgpITf
3rCZjqdKm4Ushhjat/f0wnM=
=FPty
-----END PGP SIGNATURE-----