OTN Discussion Forums : Unable to connect to Oracle using OS ...

dragosv

Posts: 71
Registered: 08/09/06

Unable to connect to Oracle using OS authentication (sqlplus / as sysdba)
Posted: May 23, 2007 1:48 AM

Hello,

When I try to connect locally ,using OS authentication ( sqlplus / as sysdba ), on a Oracle database, I get "ORA-01031: insufficient privileges" error.

OS: Windows XP
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod.
connecting as sysdba using sys/pasword works.

I have setup :
-> Log on as a batch job
-> Log on as a service
I am also member of ORA_DBA group.

I mention that connecting "/ as sysdba" worked many months after I've installed the Oracle database. But at some moment in time connecting "/ as sysdba" was no longer possible.

C:\>sqlplus / as sysdba
SQL*Plus: Release 10.1.0.3.0 - Production on Wed May 23 11:08:52 2007
Copyright (c) 1982, 2004, Oracle. All rights reserved.
ERROR:
ORA-01031: insufficient privileges.

Thank you for any suggestion.

P. Forstmann

Posts: 2,415
Registered: 01/26/07

Re: Unable to connect to Oracle using OS authentication (sqlplus / as sysdba)
Posted: May 23, 2007 2:09 AM

in response to: dragosv

Reply

Check items 3 and 6 from http://download-uk.oracle.com/docs/cd/B19306_01/win.102/b14304/external.htm#i1014020

dragosv

Posts: 71
Registered: 08/09/06

Re: Unable to connect to Oracle using OS authentication (sqlplus / as sysdba)
Posted: May 23, 2007 3:57 AM

in response to: P. Forstmann

Reply

Thank you, Pierre, but unfortunatelly it still doesn't work

sqlnet.ora already had these lines :
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

I've also added the
OSAUTH_PREFIX_DOMAIN with value TRUE here : HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1
(it wasn't at all)

moosy

Posts: 247
Registered: 07/26/06

Re: Unable to connect to Oracle using OS authentication (sqlplus / as sysdba)
Posted: May 23, 2007 4:32 AM

in response to: dragosv

Reply

Hi

what is the value of remote_login_passwordfile

Regards
Den

dragosv

Posts: 71
Registered: 08/09/06

Re: Unable to connect to Oracle using OS authentication (sqlplus / as sysdba)
Posted: May 23, 2007 5:18 AM

in response to: moosy

Reply

SQL> show parameter remote_login_passwordfile

NAME TYPE VALUE

-----------

remote_login_passwordfile string EXCLUSIVE

(I'm trying to connect locally, on the same machine where Oracle is installed)

moosy

Posts: 247
Registered: 07/26/06

Re: Unable to connect to Oracle using OS authentication (sqlplus / as sysdba)
Posted: May 23, 2007 5:20 AM

in response to: dragosv

Reply

is your ORACLE_SID ok ?

regards
Den

moosy

Posts: 247
Registered: 07/26/06

Re: Unable to connect to Oracle using OS authentication (sqlplus / as sysdba)
Posted: May 23, 2007 5:22 AM

in response to: moosy

Reply

sorry !
You can't connect on your client with / only on the server.

OR create a password file and connect to database with a granted sysdba user.

Regards
Den

dragosv

Posts: 71
Registered: 08/09/06

Re: Unable to connect to Oracle using OS authentication (sqlplus / as sysdba)
Posted: May 23, 2007 5:50 AM

in response to: moosy

Reply

Oracle database is installed on my machine.
ORACLE_SID is orcl.
Did some testing :

1) sqlplus sys/<passw> as sysdba : OK

2) Enter user-name: sys/<passw>@orcl as sysdba : FAILS !
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

sqlplus / as sysdba : FAILS !
ERROR:
ORA-01031: insufficient privileges

dragosv

Posts: 71
Registered: 08/09/06

Re: Unable to connect to Oracle using OS authentication (sqlplus / as sysdba)
Posted: May 23, 2007 6:02 AM

in response to: dragosv

Reply

I've tried also :
set ORACLE_HOME=D:\oracle_db\product\10.2.0\db_1

but then, for both :
connect sys/<passwd>@orcl as sysdba
connect / as sysdba

I've got these 2 errors :
1)error message from sql*plus : "The procedure entry point snlinAddrLocalhost could not be located in the dynamic link library oranl10.dll"
2)
ERROR:
ORA-12557: TNS:protocol adapter not loadable.

Message was edited by:
dragosv

moosy

Posts: 247
Registered: 07/26/06

Re: Unable to connect to Oracle using OS authentication (sqlplus / as sysdba)
Posted: May 23, 2007 6:28 AM

in response to: dragosv

Reply

is your OS user member of ORA_DBA group ?

Regards
Den

dragosv

Posts: 71
Registered: 08/09/06

Re: Unable to connect to Oracle using OS authentication (sqlplus / as sysdba)
Posted: May 23, 2007 6:49 AM

in response to: dragosv

Reply

issue solved !
with regards to Petre Vasu (petrica.vasu@bluespace.com)
I've modified the order of the ORACLE_HOMEs I have, using the Universal Installer (> Installed Products / Environment Variables). The ORACLE_HOME of the database should be the first one.

sheron

Posts: 2
Registered: 05/31/08

Re: Unable to connect to Oracle using OS authentication (sqlplus / as sysdba)
Posted: May 31, 2008 1:24 AM

in response to: dragosv

Reply

There can be other situations like we need to set the PATH env variable as well. Suppose if you have 2 or more oracle homes , and different versions of sqlplus , you need to set PATH for the mail database ORACLE_HOME\bin.

I had the same issue. That is even after setting the ORACLE_HOME,it gave the same error when connecting via SQLPLUS . So I set the PATH to my main ORALCE_HOME\bin path. Then it worked...

thanks ,
Sheron.

$ami Malik

Posts: 733
Registered: 07/02/07

Re: Unable to connect to Oracle using OS authentication (sqlplus / as sysdba)
Posted: Jun 1, 2008 2:01 AM

in response to: dragosv

Reply

You should add OS user to the DBA group on the windows server only then you will be able to connect to the database without providing any sys password i.e by '/ as sysdba'.

Sami Malik

Khaleel S

Posts: 204
Registered: 02/26/06

Re: Unable to connect to Oracle using OS authentication (sqlplus / as sysdba)
Posted: Oct 20, 2009 10:27 PM

in response to: $ami Malik

Reply

Hi,

I have the same issues from the beginning..

I have two Oracle homes ( One for earlier installed SOA10g, and another for current installing SOA11g).

I have the right sqlora.net file. I have removed the old path and reset to point the new oracle home. I have this home entry as the first entry in my Universal installer too.

I AM ABLE TO CONNECT WITH sys USER AS SYSDBA on THE SAME SYSTEM ( I MEAN WHERE i INSTALLED THE DATABASE) USING THE command prompt.

But I am not able to connect as the SYSDBA with sys ACCOUNT through the SQLDeveloper tool. or RCU tool? Any help please. Again I am able to connect as a normal user through the SQL developer using the SYSTEM account.

-- Khaleel