Tag Archives: ORA-01031

Authenticate with REMOTE LOGIN PASSWORDFILE Parameter in Oracle

Authenticate method with REMOTE LOGIN PASSWORDFILE Parameter in Oracle

Remote login passwordfile is specify that need to verify from the password file or OS authentication for login into the Oracle Server.
Default value of parameter is exclusive. For modify the value need to reboot the Oracle Database Server.

Values:
EXCLUSIVE: Password file can be used by only one database and having sys or non sys users.
SHARED: In this one or more database can used the same Password file and having sys or non sys users.
NONE: Oracle use OS authentication. it not consider password file.

Example of using Password file:

1. Check the parameter detail of remote login passwordfile.

SQL> show parameter remote_login
NAME TYPE VALUE
------------------------- ------ ----------
remote_login_passwordfile string EXCLUSIVE

2. Try to login with SYS users

C:\Users\e3019447>sqlplus sys as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Mon Oct 8 10:29:42 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL>

3. Change the value of parameter to NONE.

SQL> alter system set remote_login_passwordfile=NONE scope=both;
alter system set remote_login_passwordfile=NONE scope=both
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

SQL> alter system set remote_login_passwordfile=NONE scope=spfile;
System altered.

4. Restart the database for parameter changes effects:

shutdown
startup

Note: Check also the parameter NTS or NONE in SQLNET.ora if you are on windows server.
NTS means window authentication
NONE means password file authentication
Otherwise got ERROR:
ORA-01031: insufficient privileges
Solution: On Windows if you change remote login passwordfile parameter to NONE then for login you have sqlnet.ora parameter set as NTS value.

5. Start the database and check connectivity

C:\Windows\system32>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Mon Oct 8 10:36:39 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL>

Error: “ORA-01031: insufficient privileges”
Invalid username and password from Server or Client Side connectivity issue
One of our customer says he is able to connect from Server side but when he tried to connect from client side he is getting error of “ORA-01031: insufficient privileges”
Solution:
1. If you are connecting on Server SQLPLUS then it use SQLNET value as NTS. If you provide sys user name and password on same server
then it connected but if you tried to connect from client side then it give you error.
2. Remote_login_passwordfile parameter to have ‘exclusive’
3. Password file present for the instance where you try to connect it.

Advertisements

AUTHENTICATION SERVICES Parameter in SQLNET file of Oracle

AUTHENTICATION SERVICES Parameter in SQLNET file of Oracle

Authentication Service parameter is used to list the authentication method of connectivity with oracle database.

SQLNET.AUTHENTICATION_SERVICES value all the server attempts to authenticate using each of the following methods.

Note: NTS means OS authenticated and NONE value means password file authenticated.

Authentication Method available in Oracle:
NONE for no authentication method. When parameter is set to none, a valid user name and password can be used to access the database.
ALL for all authentication methods. Default value is all.
BEQ for native operating system authentication for operating systems other than Microsoft Windows
KERBEROS5 for Kerberos authentication
NTS for Microsoft Windows native operating system authentication. Used to connect with OS Authentication.
RADIUS for Remote Authentication Dial-In User Service (RADIUS) authentication
TCPS for SSL authentication

DEFAULT VALUE

SQLNET.AUTHENTICATION_SERVICES = ALL

Example of windows Server

1. Value for SQLNET.AUTHENTICATION_SERVICES is NTS.

SQLNET.AUTHENTICATION_SERVICES = (NTS)

2. Try to connect with SQLPLUS with OS User then its connected.

C:\Users\e3019447>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Tue Jun 12 14:26:38 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL>

3. Change the value of parameter to NONE.

SQLNET.AUTHENTICATION_SERVICES = (NONE)

4. It will give the permission error while tried to connect again with OS user.

C:\Users\e3019447>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Tue Jun 12 14:26:58 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
ERROR:
ORA-01031: insufficient privileges