SEO

Hi,
I am setting up new Oracle 9.2 DB on my laptop.
problem is that I can connect as SYSDBA using

connect sys/sys as sysdba

but when I am using

connect / as sysdba

I am always getting ORA-01031: insufficient privileges

I have created instance using
oradim -new -sid OPWVDB1L -startmode manual -pfile
c:\oracle\ora92\database\initOPWVDB1L.ora

in sqlnet.ora I have
SQLNET.AUTHENTICATION_SERVICES= (NTS)

my Win2K account is a member of ORA_DBA group

remote_login_passwordfile = EXCLUSIVE

password file has been created, before I created password I wasn't
able to login with sys/sys as well...

Any help would be appreciated!
Thanks.
Andrew

Andrew wrote:
> Hi,
> I am setting up new Oracle 9.2 DB on my laptop.
> problem is that I can connect as SYSDBA using
>
> connect sys/sys as sysdba
>
> but when I am using
>
> connect / as sysdba
>
> I am always getting ORA-01031: insufficient privileges
>
> I have created instance using
> oradim -new -sid OPWVDB1L -startmode manual -pfile
> c:\oracle\ora92\database\initOPWVDB1L.ora
>
> in sqlnet.ora I have
> SQLNET.AUTHENTICATION_SERVICES= (NTS)

Get rid of that line (or comment it out) and then see what happens.

Regards
HJR

On 29 Apr 2004 21:52:23 -0700, myfam@surfeu.fi (Andrew) wrote:

>Hi,
>I am setting up new Oracle 9.2 DB on my laptop.
>problem is that I can connect as SYSDBA using
>
>connect sys/sys as sysdba
>
>but when I am using
>
>connect / as sysdba
>
>I am always getting ORA-01031: insufficient privileges
>
>I have created instance using
>oradim -new -sid OPWVDB1L -startmode manual -pfile
>c:\oracle\ora92\database\initOPWVDB1L.ora
>
>in sqlnet.ora I have
>SQLNET.AUTHENTICATION_SERVICES= (NTS)
>
>my Win2K account is a member of ORA_DBA group
>
>remote_login_passwordfile = EXCLUSIVE

You appear to have read the doc. about O/S authentication, but have
got it wrong :

remote_login_passwordfile = EXCLUSIVE means

"Use the password file, thus disregard O/S authentication"

Change it to

remote_login_passwordfile = NONE

Which means "Ignore the password file, use O/S authentication".

Your sqlnet.ora setting is right (right, Howard ;-) ? )


- Kenneth Koenraadt



>
>password file has been created, before I created password I wasn't
>able to login with sys/sys as well...
>
>Any help would be appreciated!
>Thanks.
>Andrew

Kenneth Koenraadt wrote:

> On 29 Apr 2004 21:52:23 -0700, myfam@surfeu.fi (Andrew) wrote:
>
>
>>Hi,
>>I am setting up new Oracle 9.2 DB on my laptop.
>>problem is that I can connect as SYSDBA using
>>
>>connect sys/sys as sysdba
>>
>>but when I am using
>>
>>connect / as sysdba
>>
>>I am always getting ORA-01031: insufficient privileges
>>
>>I have created instance using
>>oradim -new -sid OPWVDB1L -startmode manual -pfile
>>c:\oracle\ora92\database\initOPWVDB1L.ora
>>
>>in sqlnet.ora I have
>>SQLNET.AUTHENTICATION_SERVICES= (NTS)
>>
>>my Win2K account is a member of ORA_DBA group
>>
>>remote_login_passwordfile = EXCLUSIVE
>
>
> You appear to have read the doc. about O/S authentication, but have
> got it wrong :
>
> remote_login_passwordfile = EXCLUSIVE means
>
> "Use the password file, thus disregard O/S authentication"
>
> Change it to
>
> remote_login_passwordfile = NONE
>
> Which means "Ignore the password file, use O/S authentication".
>
> Your sqlnet.ora setting is right (right, Howard ;-) ? )
>
>
> - Kenneth Koenraadt

We are all allowed to make mistakes, but that, unfortunately, is an
absolute howler, and completely and utterly untrue. Whatever the setting
for remote_login_passwordfile, Oracle *always* checks the O/S for
appropriate group membership, and if it finds it, doesn't bother
checking the password file, even if the parameter tells it to.

Remote_login_passwordfile=NONE means "there is no password file". It
does NOT mean 'use O/S authentication'.

Proof? Well, how about this?

C:\>sqlplus "/ as sysdba"

SQL*Plus: Release 8.1.7.0.0 - Production on Fri Apr 30 16:53:13 2004

(c) Copyright 2000 Oracle Corporation. All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
With the Partitioning option
JServer Release 8.1.7.3.0 - Production

SQL> show parameter remote_login

NAME TYPE VALUE
------------------------------------ ------- ------------------------
remote_login_passwordfile string EXCLUSIVE

Now, I can do that on 9i and 10g too if you want me to. It's been that
way since version 7, in fact.

Incidentally, I got it wrong too. The above example is taken from a
system on which SQLNET.AUTHENTICATION_SERVICES has indeed been set to
NTS, so my earlier advice to get rid of the line was just daft. I can
only ask of the original poster: are you sure you're looking at the
right SQLNET.ORA? And are you certain that your user account you use to
log onto the server is a member of the ORA_DBA LOCAL group (domain
groups won't do it).

Regards
HJR

On Fri, 30 Apr 2004 16:59:23 +1000, "Howard J. Rogers"
<hjr@dizwell.com> wrote:

>Kenneth Koenraadt wrote:
>
>> On 29 Apr 2004 21:52:23 -0700, myfam@surfeu.fi (Andrew) wrote:
>>
>>
>>>Hi,
>>>I am setting up new Oracle 9.2 DB on my laptop.
>>>problem is that I can connect as SYSDBA using
>>>
>>>connect sys/sys as sysdba
>>>
>>>but when I am using
>>>
>>>connect / as sysdba
>>>
>>>I am always getting ORA-01031: insufficient privileges
>>>
>>>I have created instance using
>>>oradim -new -sid OPWVDB1L -startmode manual -pfile
>>>c:\oracle\ora92\database\initOPWVDB1L.ora
>>>
>>>in sqlnet.ora I have
>>>SQLNET.AUTHENTICATION_SERVICES= (NTS)
>>>
>>>my Win2K account is a member of ORA_DBA group
>>>
>>>remote_login_passwordfile = EXCLUSIVE
>>
>>
>> You appear to have read the doc. about O/S authentication, but have
>> got it wrong :
>>
>> remote_login_passwordfile = EXCLUSIVE means
>>
>> "Use the password file, thus disregard O/S authentication"
>>
>> Change it to
>>
>> remote_login_passwordfile = NONE
>>
>> Which means "Ignore the password file, use O/S authentication".
>>
>> Your sqlnet.ora setting is right (right, Howard ;-) ? )
>>
>>
>> - Kenneth Koenraadt
>
>We are all allowed to make mistakes, but that, unfortunately, is an
>absolute howler, and completely and utterly untrue. Whatever the setting
>for remote_login_passwordfile, Oracle *always* checks the O/S for
>appropriate group membership, and if it finds it, doesn't bother
>checking the password file, even if the parameter tells it to.
>
>Remote_login_passwordfile=NONE means "there is no password file". It
>does NOT mean 'use O/S authentication'.
>
>Proof? Well, how about this?
>
>C:\>sqlplus "/ as sysdba"
>
>SQL*Plus: Release 8.1.7.0.0 - Production on Fri Apr 30 16:53:13 2004
>
>(c) Copyright 2000 Oracle Corporation. All rights reserved.
>
>
>Connected to:
>Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
>With the Partitioning option
>JServer Release 8.1.7.3.0 - Production
>
>SQL> show parameter remote_login
>
>NAME TYPE VALUE
>------------------------------------ ------- ------------------------
>remote_login_passwordfile string EXCLUSIVE
>
>Now, I can do that on 9i and 10g too if you want me to. It's been that
>way since version 7, in fact.
>
>Incidentally, I got it wrong too. The above example is taken from a
>system on which SQLNET.AUTHENTICATION_SERVICES has indeed been set to
>NTS, so my earlier advice to get rid of the line was just daft. I can
>only ask of the original poster: are you sure you're looking at the
>right SQLNET.ORA? And are you certain that your user account you use to
>log onto the server is a member of the ORA_DBA LOCAL group (domain
>groups won't do it).
>
>Regards
>HJR
>
>
>

Hi Howard,

Can't agree.

It's true that you can "connect / as sysdba" even with
remote_login_passwordfile =exclusive,
but only as long as your user is a *LOCAL* W2K user. If you logon to
the server *remotely* with e.g. a Domain user account, which is also
a member of the local ORA_DBA group you *won't* be able to "connect /
as sysdba". I guess that's why it is called
"remote_login_passwordfile" and not "local_login_passwordfile"

The doc also states that you must set remote_login_passwordfile =NONE
to use OS-authentication on W2k. The fact that a *local* user can
somehow bypass it does not affect that.

<quote>
Set the REMOTE_LOGIN_PASSWORDFILE parameter to NONE in the
INIT<SID>.ORA
file. This parameter enables operating system authenticated
logins for the
INTERNAL user.
</quote>

- Kenneth Koenraadt

Kenneth Koenraadt wrote:
[snip]
>
>
> Hi Howard,
>
> Can't agree.
>
> It's true that you can "connect / as sysdba" even with
> remote_login_passwordfile =exclusive,
> but only as long as your user is a *LOCAL* W2K user.

Which is, of course, exactly the case for our original poster, since
he's doing all of this on his laptop. So even if the rest of what you
write is true, it's not of relevance to him, is it?

> If you logon to
> the server *remotely* with e.g. a Domain user account, which is also
> a member of the local ORA_DBA group you *won't* be able to "connect /
> as sysdba". I guess that's why it is called
> "remote_login_passwordfile" and not "local_login_passwordfile"

Well, since it's a remote connection, you won't be able to connect / as
sysdba *at all* because there needs to be a tnsnames alias in there
somewhere (somewhere I can never get right in any case: sqlplus "/@win92
as sysdba" isn't doing it for me!).

> The doc also states that you must set remote_login_passwordfile =NONE
> to use OS-authentication on W2k. The fact that a *local* user can
> somehow bypass it does not affect that.
>
> <quote>
> Set the REMOTE_LOGIN_PASSWORDFILE parameter to NONE in the
> INIT<SID>.ORA
> file. This parameter enables operating system authenticated
> logins for the
> INTERNAL user.
> </quote>

Yup, Oracle's course notes always said you had to set R_L_P to NONE too.
But it isn't true. And this isn't a Windows thing, either, since I used
to show my students the folly of the 'must set it to NONE' by doing
exactly the same test as I showed in my last post, but on a Solaris box.

Regards
HJR

Howard J. Rogers wrote:

> Kenneth Koenraadt wrote:
> [snip]
>
>>
>>
>> Hi Howard,
>>
>> Can't agree.
>>
>> It's true that you can "connect / as sysdba" even with
>> remote_login_passwordfile =exclusive,
>> but only as long as your user is a *LOCAL* W2K user.
>
>
> Which is, of course, exactly the case for our original poster, since
> he's doing all of this on his laptop. So even if the rest of what you
> write is true, it's not of relevance to him, is it?
>
>> If you logon to
>> the server *remotely* with e.g. a Domain user account, which is also
>> a member of the local ORA_DBA group you *won't* be able to "connect /
>> as sysdba". I guess that's why it is called
>> "remote_login_passwordfile" and not "local_login_passwordfile"
>
>
> Well, since it's a remote connection, you won't be able to connect / as
> sysdba *at all* because there needs to be a tnsnames alias in there
> somewhere (somewhere I can never get right in any case: sqlplus "/@win92
> as sysdba" isn't doing it for me!).
>
>> The doc also states that you must set remote_login_passwordfile =NONE
>> to use OS-authentication on W2k. The fact that a *local* user can
>> somehow bypass it does not affect that.
>>
>> <quote>
>> Set the REMOTE_LOGIN_PASSWORDFILE parameter to NONE in the
>> INIT<SID>.ORA file. This parameter enables operating system
>> authenticated
>> logins for the
>> INTERNAL user. </quote>
>
>
> Yup, Oracle's course notes always said you had to set R_L_P to NONE too.
> But it isn't true. And this isn't a Windows thing, either, since I used
> to show my students the folly of the 'must set it to NONE' by doing
> exactly the same test as I showed in my last post, but on a Solaris box.
>
> Regards
> HJR

Oh, by the way, just another test to make the point. Here's what my
server says:

SQL> show parameter remote_login

NAME TYPE VALUE
------------------------------------ ----------- ---------
remote_login_passwordfile string EXCLUSIVE

Here's my sqlnet.ora on the *CLIENT* machine:

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES)

And here's the acid test:

H:\>sqlplus "/@win92 as sysdba"

SQL*Plus: Release 10.1.0.2.0 - Production on Fri Apr 30 18:00:36 2004

Copyright (c) 1982, 2004, Oracle. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

So that's me finally managing to get a remote connection using O/S
authentication to a 9i database that's got R_L_P set to something other
than NONE.

Still think the docs are correct?

Regards
HJR

On Fri, 30 Apr 2004 18:03:11 +1000, "Howard J. Rogers"
<hjr@dizwell.com> wrote:

>Howard J. Rogers wrote:
>
>> Kenneth Koenraadt wrote:
>> [snip]
>>
>>>
>>>
>>> Hi Howard,
>>>
>>> Can't agree.
>>>
>>> It's true that you can "connect / as sysdba" even with
>>> remote_login_passwordfile =exclusive,
>>> but only as long as your user is a *LOCAL* W2K user.
>>
>>
>> Which is, of course, exactly the case for our original poster, since
>> he's doing all of this on his laptop. So even if the rest of what you
>> write is true, it's not of relevance to him, is it?
>>
>>> If you logon to
>>> the server *remotely* with e.g. a Domain user account, which is also
>>> a member of the local ORA_DBA group you *won't* be able to "connect /
>>> as sysdba". I guess that's why it is called
>>> "remote_login_passwordfile" and not "local_login_passwordfile"
>>
>>
>> Well, since it's a remote connection, you won't be able to connect / as
>> sysdba *at all* because there needs to be a tnsnames alias in there
>> somewhere (somewhere I can never get right in any case: sqlplus "/@win92
>> as sysdba" isn't doing it for me!).
>>
>>> The doc also states that you must set remote_login_passwordfile =NONE
>>> to use OS-authentication on W2k. The fact that a *local* user can
>>> somehow bypass it does not affect that.
>>>
>>> <quote>
>>> Set the REMOTE_LOGIN_PASSWORDFILE parameter to NONE in the
>>> INIT<SID>.ORA file. This parameter enables operating system
>>> authenticated
>>> logins for the
>>> INTERNAL user. </quote>
>>
>>
>> Yup, Oracle's course notes always said you had to set R_L_P to NONE too.
>> But it isn't true. And this isn't a Windows thing, either, since I used
>> to show my students the folly of the 'must set it to NONE' by doing
>> exactly the same test as I showed in my last post, but on a Solaris box.
>>
>> Regards
>> HJR
>
>Oh, by the way, just another test to make the point. Here's what my
>server says:
>
>SQL> show parameter remote_login
>
>NAME TYPE VALUE
>------------------------------------ ----------- ---------
>remote_login_passwordfile string EXCLUSIVE
>
>Here's my sqlnet.ora on the *CLIENT* machine:
>
>SQLNET.AUTHENTICATION_SERVICES= (NTS)
>
>NAMES.DIRECTORY_PATH= (TNSNAMES)
>
>And here's the acid test:
>
>H:\>sqlplus "/@win92 as sysdba"
>
>SQL*Plus: Release 10.1.0.2.0 - Production on Fri Apr 30 18:00:36 2004
>
>Copyright (c) 1982, 2004, Oracle. All rights reserved.
>
>
>Connected to:
>Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
>With the Partitioning, OLAP and Oracle Data Mining options
>JServer Release 9.2.0.1.0 - Production
>
>So that's me finally managing to get a remote connection using O/S
>authentication to a 9i database that's got R_L_P set to something other
>than NONE.
>
>Still think the docs are correct?
>
>Regards
>HJR


Your example does not prove anything.

I have experienced myself lots of time that I need to have R_L_L <>
EXCLUSIVE" in order for "connect / as sysdba" to work, when I am
logged on as a domain user being a member of ORA_DBA.

And the Doc just confirms my observations, and I see no reason not to
stick to it.

The fact that *you* might have been able to bypass it, possibly due to
a W2K bug, is unusable to me. Sorry.


BTW : How come that when you *think* others are wrong, it's a "howler"
and "utterly untrue". When YOU say something definetely incorrect (the
sqlnet.ora thing) it's : "Incidentally, I got it wrong".

I suspect you get furious now, so I'll end the discussion here.

- Kenneth Koenraadt

On Fri, 30 Apr 2004 17:55:48 +1000, "Howard J. Rogers"
<hjr@dizwell.com> wrote:

>Kenneth Koenraadt wrote:
>[snip]
>>
>>
>> Hi Howard,
>>
>> Can't agree.
>>
>> It's true that you can "connect / as sysdba" even with
>> remote_login_passwordfile =exclusive,
>> but only as long as your user is a *LOCAL* W2K user.
>
>Which is, of course, exactly the case for our original poster, since
>he's doing all of this on his laptop. So even if the rest of what you
>write is true, it's not of relevance to him, is it?

It most certainly is. The fact is that *whenever* you want to use
O/S-authentication, you *should* set R_L_P = NONE. The fact that you
can (sometimes) get away with not doing it, is merely a lucky punch.

R_L_P = NONE is the best, the safest and the recommended way. Period.


>
>> If you logon to
>> the server *remotely* with e.g. a Domain user account, which is also
>> a member of the local ORA_DBA group you *won't* be able to "connect /
>> as sysdba". I guess that's why it is called
>> "remote_login_passwordfile" and not "local_login_passwordfile"
>
>Well, since it's a remote connection, you won't be able to connect / as
>sysdba *at all* because there needs to be a tnsnames alias in there
>somewhere (somewhere I can never get right in any case: sqlplus "/@win92
>as sysdba" isn't doing it for me!).

Yes you will.
You logon to the server with a domain user being a member of the local
ORA_DBA group. With R_L_P=NONE, and sqlnet.ora properly set, I can
connect / as sysdba easily. Have done it hundreds of times.


>
>> The doc also states that you must set remote_login_passwordfile =NONE
>> to use OS-authentication on W2k. The fact that a *local* user can
>> somehow bypass it does not affect that.
>>
>> <quote>
>> Set the REMOTE_LOGIN_PASSWORDFILE parameter to NONE in the
>> INIT<SID>.ORA
>> file. This parameter enables operating system authenticated
>> logins for the
>> INTERNAL user.
>> </quote>
>
>Yup, Oracle's course notes always said you had to set R_L_P to NONE too.
>But it isn't true. And this isn't a Windows thing, either, since I used
>to show my students the folly of the 'must set it to NONE' by doing
>exactly the same test as I showed in my last post, but on a Solaris box.
>
>Regards
>HJR

Hi
is it possible to see what's going on, i.e. why OS authentication is
rejected from some log file? There is nothing in alert.log... I am
using domain account which anyway is a member of ORA_DBA. An wierd
thing that it worked some time ago but it doesn't work anymore...

Andrew

"Howard J. Rogers" <hjr@dizwell.com> wrote in message news:<4091f945$0$441$afc38c87@news.optusnet.com.au >...
> Kenneth Koenraadt wrote:
>
> > On 29 Apr 2004 21:52:23 -0700, myfam@surfeu.fi (Andrew) wrote:
>
> Regards
> HJR