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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.