ORA-01031 insufficient privileges - While logging in AS SYSDBA

OS Authentication is a setup whereby we can log into the oracle database as SYSDBA or SYSOPER without supplying any password because OS user (currently logged in) is already authenticated to log into the database as a privileged user. This is handy specially if you have forgotten the SYS password (another way to recover SYS password is
to recreate the password file).

As explained above, in Oracle, OS authentication can be enabled so that DBAs don't need to provide password while logging in as "AS SYSDBA" and to do so, we need to add the OS user in OSDBA group (group name is ORA_DBA on Windows, and dba in Unix based systems) and specifically for windows based systems, we also need to add a parameter "sqlnet.authentication_services= (nts)" in the sqlnet.ora file found under ORACLE_HOME\network\admin folder.

ORA-01031: insufficient privileges

A few days back I faced an issue while working at a customer site on a Windows based server, where I was trying to login as "as SYSDBA" without supplying a password (using OS Authentication), and I faced error message "ORA-01031: insufficient privileges". I realized that all prerequisites for OS authentication are already met as my OS user was member of ORA_DBA group, and parameter "sqlnet.authentication_services= (nts)" was already present in sqlnet.ora file.

I was really baffled and opened a case with Oracle support who mentioned MOS document 730067.1 which explains how to enable OS authentication on different platforms, but, I already had fulfilled all prerequisites which did not help.

Solution

Thanks to one of my colleagues who helped me in that by mentioning a similar issue he faced and which was related to TNS_ADMIN setting in registry.

TNS_ADMIN is a parameter in windows registry (under oracle database software registry key) which points to the location of TNS files (tnsnames.ora, sqlnet.ora, listener.ora etc.). In my case, there were 2 installations on that server (Oracle application server 10g and Oracle database server 11g) and TNS_ADMIN in the registry (under Oracle database software key) was actually pointing to TNS files of application server home, and in application server home (as you can see in bellow image)

and SQLNET.AUTHENTICATION_SERVICES=(NTS) was disabled/commented in application server home SQLNET.ORA file as you can see bellow

#####################################################SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= ( TNSNAMES)
####################################################

So I had two options: either un-comment this parameter here in application server home, or set my TNS_ADMIN value to point to database home TNS files location so that it can read sqlnet.ora file in oracle database home where this parameter is already set.

Moral of the story: In case of any network/connectivity issue, don't forget to check TNS_ADMIN setting and make sure it is pointing to correct TNS files locations where you have made your required settings.

On UNIX based systems, SQLNET.AUTHENTICATION_SERVICES is not required to be set for OS authentication.

On UNIX based systems, TNS_ADMIN is used as environment variable to point to the network files from a specific ORACLE_HOME which we want to use. This can either be set in .bash_profile/.profile or also can be set at command prompt.