ORA-01031 Insufficient privilege when connecting from client

Hi Guys,

Oracle 81730 on Win2000 platform.

ORA-01031 error is somehow always haunting me. Today, I found out that from client, if I say connect sys/@xyz as sysdba, it gives ORA-01031 Insufficient privilege.

But from server, the same thing works.

remote_login_passwordfile = exclusive

select * from v$pwfile_users

USERNAME SYSDBA SYSOPER
------------------------------
INTERNAL TRUE TRUE
SYS TRUE TRUE

In SQLNET.ORA on server,
#
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH=(TNSNAMES, ONAMES, HOSTNAME)

Pl. help....

Thanks in Adv.

Could you try
comment out SQLNET.AUTHENTICATION_SERVICES= (NTS)
In SQLNET.ORA on server,
#SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH=(TNSNAMES, ONAMES, HOSTNAME)

Do you mean "connect sys/@xyz as sysdba"? This command is invalid for me in either server or client. But "connect /@xyz as sysdba" or "connect sys/passwd@xyz as sysdba" works. Pls advice.

Quote:

---

Originally posted by ly
Do you mean "connect sys/@xyz as sysdba"? This command is invalid for me in either server or client. But "connect /@xyz as sysdba" or "connect sys/passwd@xyz as sysdba" works. Pls advice.

---

LY :

He says hez getting ORA-01031 Insufficient privilege.

Had he jus given conn Sys/@connect string as sysdba, then he wud have got

Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where ::= [/][@] | /

Probalabaly he forgot to type "PSWD"

Its suerly the problem in SQL * NET. NTS authentiation should be commented.....

Abhay.

Hi Guys,

That was the typing error. After I commented SQLNET.AUTHENTICATION_SERVICES= (NTS) in sqlnet.ora, now it gives ORA-

ORA-1017: Invalid Username/password error; logon denied error

Pl. help me resolve this problem.

Thanks,

Now you put invalid sysdba password. To solve the problem

1) local connect in as sysdba ( / as sysdba, do not need password)
2) shutdown the database
3) create new password file by
$ orapwd file=$ORACLE_HOME/dbs/orapwSID password=new_password entries=6
4) restart database
5) alter user sys identified by new_password; (sometimes the password in passwd file does not match passwd inside database)
6) remotely connect as sysdba
sys/new_password@SID as sysdba

Hi abhaysk,

I definitely understand what his question is. But I need to know what he really want to do. And I don't think NTS authentiation should be commented.

samdba,
If you want to use "connect /@xyz as sysdba" to conn, you may refer to http://216.239.53.100/search?q=cache...N_SERVICES%3D+(NTS)&hl=en&ie=UTF-8 .

Otherwise, you can try supower's method.

Quote:

---

Originally posted by ly
Hi abhaysk,

I definitely understand what his question is. But I need to know what he really want to do. And I don't think NTS authentiation should be commented.

---

LY,

If u give NTS Authentication, then u need to add the member's NTS account in the ORA_DBA group on server side.

If u comment NTS authentication....then the pswd of SYS can be used to connect as SYSDBA.

If u dont comment and try to connect as SYS with it pswd and the member NTS account not included in ORA_DBA group, then no matter if u give correct pswd it will not login into oracle database as SYS.


I belive this clears why we should not give NTS authentcation.....

Regards
Abhay.

Hi Guys,

Thanks for useful contribution to my thread.

Now, here is the situation.

in SQLNET.ORA, SQLNET.AUTHENTICATION_SERVICES= (NTS).

I recreated password file with foll. command.

orapwd file=PWDTEST.ORA password=syspwd entries=30.

After this, I am now able to connect to sys user as sysdba but now I am not able to connect to internal user as sysdba, which I was able to do before recreating the password file.

Supower's link is really good for understanding. How can I check whether the user is a member of the ORA_DBA local group though?

Could someone pl. explain?

Thanks,

Comment out SQLNET.AUTHENTICATION_SERVICES= (NTS)
in SQLNET.ORA. Now you should be able to connect sysdba, internal, and remote sysdba (if remote_login_passwordfile = exclusive).

General speaking (my experience):
set SQLNET.AUTHENTICATION_SERVICES= (NTS) in Wins
so you do not need sys passwd to start db.

comment out SQLNET.AUTHENTICATION_SERVICES= (NTS)
in Unix, linux so you do not need sys passwd to start db.

Hi Supower,

Nope. It still does not work. I have 'Exclusive' in init.ora.

I bounced the listener also.

Still I am able to connect to sys as sysdba but with internal, it gives 'incorrect username/password' and with system, it gives 'insufficient privilege'.

Pl. help.

Thanks,

I forgot you are in win2000. Please set
SQLNET.AUTHENTICATION_SERVICES= (NTS)
so you can locally connect to internal w/o passwd if you login in as oracle owner or local admin.

There is security concern here, if set
SQLNET.AUTHENTICATION_SERVICES= (NTS)
and REMOTE_LOGIN_ PASSWORDFILE=exclusive in Wins.
You can connect sys/ANYPASSWD@SID as sysdba.
So oracle suggest that, if set
SQLNET.AUTHENTICATION_SERVICES= (NTS) in Wins,
set REMOTE_LOGIN_PASSWORDFILE=NONE to avoid remote connection as sysdba.

Hi Supower,

Here is the situation.

#SQLNET.AUTHENTICATION_SERVICES= (NTS) in sqlnet.ora.

I had to recreate password file with internal password instead of sys because my database startup script stopped working as I am using internal for starting the DB.

Now, I am able to connect internal as sysdba but not to sys as sysdba. So, what happens is whatever password I use in creating the oracle password file, it is able to connect as sysdba.

Now even with commented SQLNET.AUTHENTICATION... I am not able to connect to sys or system as sysdba.

I think the password file is more important here.

What should I do now?

Thanks,

*******************************************
Now, I am able to connect internal as sysdba but not to sys as sysdba. So, what happens is whatever password I use in creating the oracle password file, it is able to connect as sysdba.
**********************************************

It looks ok.

**************************************
Now even with commented SQLNET.AUTHENTICATION... I am not able to connect to sys or system as sysdba.
***************************************

Did you shutdown oracle service when you create passwd file ?

Try shutdwon database, shutdwon oracle service (wins), recreate passwd file, start oracle service, start db and
sqL> alter user sys identifiy by PASSWD(same as your passwd file).

Hi Supower,

After altering the SYS pwd as you said, now I am able to connect to internal and SYS both as sysdba but with system, it still gives 'insufficient privileges'. I altered SYSTEM password also but why it gives this error?

Thanks for your help.

If you set REMOTE_LOGIN_PASSWORDFILE=exclusive
Grant sysdba to system;

Hi Supower,

Thank you very much.

My problem got resolved with your help.

I have also kept the SQL.AUTHENTICATION... in sqlnet.ora because without that it was giving problem in starting the database where I use connect internal/intpwd as sysdba;

I did the foll. to fix the problem.

1. Shutdown database, its services
2. Rename existing password file.
3. create the new password file with internal password.
4. Connect to internal as sysdba.
5. alter user sys identified by syspwd;
6. grant sysdba to sys;
7. alter user system identified by systempwd;
8. grant sysdba to system;

After this, I am now able to connect to sys and system as sysdba.

Thanks a lot.