Re: insufficient privileges as sysdba 8.1.7 on Win2k

Thanks to everyone for the help. I've spent a bit of time looking at everyone's answers & tested out a few things.

I tried deleting the service and then recreating it just to see if it would make a difference. I also set
remote_login_passwordfile = NONE. (it was EXCLUSIVE) Is it true that this only affects remote connections & local is always supposed to do OS auth?

oradim -delete -sid PPRDDM
oradim -delete -srvc OracleServicePPRDDM

Then I rebooted, deleted the old password file & recreated the service. oradim -new -sid PPRDDM -PFILE <pfilepath>

I assumed that because I did not specify the INTPWD parameter that there would not be one.

I started the service & tried connect internal. I was prompted for a password. Trying the old internal password or any other password including just hitting return, produced the error:

C:\WINNT\system32>sqlplus /nolog
SQL*Plus: Release 8.1.7.0.0 - Production on Thu Oct 3 13:32:59 2002 (c) Copyright 2000 Oracle Corporation. All rights reserved. SQL> connect internal
Enter password:
ERROR:
ORA-01031: insufficient privileges

connect / as sysdba gives the same ERROR: ORA-01031: insufficient privileges

I tried both of these with a domain account that is a member of the local(oracle server machine) "ORA_DBA" and "ORA_PPRDDM_DBA" (PPRDDM is the instance) groups. I also created a local account, added it to the same groups & tried that. The result is the same.

I can however connect with the system account (not as sysdba) & password locally & remotely.

Now I thought I'd try to create a new password file just to see if that works again. Just a reminder for those jumping in late here, I can connect to other instances on this box locally with connect internal & supplying the correct password. What I could not do was connect / as sysdba or connect sys as sysdba from a remote system.

C:\Documents and Settings\james_su>orapwd file=E:\oracle\ora81\database\PWDpprddm.ora password=password entries=5

--the password file is created fine. Then using the password I just created in orapwd

SQL*Plus: Release 8.1.7.0.0 - Production on Thu Oct 3 14:31:06 2002 (c) Copyright 2000 Oracle Corporation. All rights reserved. SQL> connect internal
Enter password:
ERROR:
ORA-01031: insufficient privileges

Just for luck, I restarted the service. Tried again with connect internal & I get the same error.

Finally, I went back & set remote_login_passwordfile = EXCLUSIVE & restarted the service. At this point connect internal with the correct password specified in orapwd gets me connected. An incorrect password does not work and connect / as sysdba does not work. For an internal connection isn't it supposed to ignore the password?

SQL> connect internal
Enter password:
ERROR:
ORA-01031: insufficient privileges

SQL> connect / as sysdba
ERROR:
ORA-01031: insufficient privileges

Again, Thanks for everyone's help & I appreciate any further ideas.

Tod

PS. I responded to some specific questions in the context of the thread below.

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:n2Sm9.45211$g9.128991_at_newsfeeds.bigpond.com...
>
> "todhsals" <todhsals_at_hotmail.com> wrote in message
> news:9APm9.21972$DN4.3891_at_sccrnsc01...
> >
> > "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message
> > news:k6Pm9.45140$g9.128706_at_newsfeeds.bigpond.com...
> > >
> > > "todhsals" <todhsals_at_hotmail.com> wrote in message
> > > news:RZOm9.20426$xI5.4066_at_sccrnsc02...
> > >
> > > "The OS
> > > account I'm using is a domain account the local ORA_DBA and
> > > Administrators groups"
> > >
> > > Noe that's appalling grammar, but never mind: are you saying that the
> > > account I've logged onto this Windows box is a domain account, and it
> > > happens to be a member of the *LOCAL* ORA_DBA group?
> >
> > Corrected by appaling spelling, "Noe" ; ) I saw the missing word right
> > after I sent it.
> >
>
> Actually, that's the correct spelling for about the 11th century.
>
> > Yes, domain user account that is a member of the local group named
> ORA_DBA.
> > I'm quite certain the group was created by the install process because
> even
> > the description line exactly matches the group description on functional
> box
> > I installed myself & the group was created by the installer.
> >
> > >
> > > If you log onto that box as Domain_Fred and Domain_Fred is a member of
> the
> > > local ORA_DBA group, then a connect / as sysdba should elicit a
> perfectly
> > > reasonable connection, and startups and shutdowns should be no
problem.
> > >
> >
> > This was my reading of the docs but it does not work.
>
> Probably the most irritating phrase in Christendom is "it doesn't work".
> WITH WHAT ERROR MESSAGE ARE YOU MET is the inevitable reply!
>
> ;-)

ERROR:
ORA-01031: insufficient privileges

>
> >I have another
> > 8.1.7/Win2k box & it works fine.
> >
> [Snip]
> >
> > My reading of the docs is that remote_login_passwordfile has no affect
on
> > local connections.
> >This seems to be the case on my other 8.1.7/Win2k box.
> > From the local console I can connect / as sysdba no password to startup,
> > shutdown etc. From a remote box I can shutdown by connect sys as sysdba
> with
> > the password.
> >
> > The one difference is that on the system I'm having the trouble with,
> > sqlnet_authentication_services is set to none (in sqlnet.ora), if I set
it
> > to NTS like the working box then I can't connect to it at all.
>
> And the error message you get is....?

--From a remote machine trying system
H:\>sqlplus system_at_proddm
SQL*Plus: Release 8.1.7.0.0 - Production on Thu Oct 3 13:17:25 2002 (c) Copyright 2000 Oracle Corporation. All rights reserved. Enter password:
ERROR:
ORA-03113: end-of-file on communication channel Enter user-name:

--From the local machine trying internal C:\Documents and Settings\james_su>sqlplus /nolog SQL*Plus: Release 8.1.7.0.0 - Production on Thu Oct 3 13:20:43 2002 (c) Copyright 2000 Oracle Corporation. All rights reserved. SQL> connect internal_at_proddm
ERROR:
ORA-24327: need explicit attach before authenticating a user SQL> exit

--Then I set sqlnet.ora back to not include NTS

--From the local machine again as internal C:\Documents and Settings\james_su>sqlplus /nolog SQL*Plus: Release 8.1.7.0.0 - Production on Thu Oct 3 13:21:27 2002 (c) Copyright 2000 Oracle Corporation. All rights reserved. SQL> connect internal_at_proddm
Enter password:
Connected.

>
> >This might
> > not be relevant but I thought the problems might be related. Eventually
I
> > need to get OS auth working for users so I need to solve this part as
> well.
> >
>
> Forget the remote connections. That's just another level of complexity.
Get
> the local connections working properly first, and then we can get fancy.
>
> So, enable NTS in sqlnet.ora, and then tell me the EXACT error message you
> get when you then try and connect, locally, as "/ as sysdba".
>
> Regards
> HJR
>
>
> > >
> [Snip]
>
>
Received on Thu Oct 03 2002 - 16:47:17 CDT