Re: W2000 connect / as sysdba problem

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Fri, 30 Apr 2004 16:59:23 +1000
Message-ID: <4091f945$0$441$afc38c87@news.optusnet.com.au>

Kenneth Koenraadt wrote:

> On 29 Apr 2004 21:52:23 -0700, myfam_at_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

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 Received on Fri Apr 30 2004 - 01:59:23 CDT