ORA-01031 Insufficient privilege when connecting from client

[samdba]

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.

[supower]

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

[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.

[abhaysk]

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.

[samdba]

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,

[supower]

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

[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.

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.

[abhaysk]

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.

[samdba]

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,

[supower]

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.