Troubleshooting ORA-1031 when connecting as SYSDBA using OS Authentication

1. Check whether the OS user is part of the OSDBA group.

On Unix

A. See what are the groups of the user:

[oracle@seclin4 ~]$ id
uid=500(oracle) gid=500(oinstall) groups=500(oinstall),501(dba) context=user_u:system_r:unconfined_t

B. See what is the OSDBA group defined in file $ORACLE_HOME/rdbms/lib/config.[cs]

Example for Linux:

[oracle@seclin4 ~]$ cat $ORACLE_HOME/rdbms/lib/config.c

/* SS_DBA_GRP defines the UNIX group ID for sqldba adminstrative access. */
/* Refer to the Installation and User's Guide for further information. */

/* IMPORTANT: this file needs to be in sync with
rdbms/src/server/osds/config.c, specifically regarding the
number of elements in the ss_dba_grp array.
*/

#define SS_DBA_GRP "dba"
#define SS_OPER_GRP "dba"
#define SS_ASM_GRP ""

char *ss_dba_grp[] = {SS_DBA_GRP, SS_OPER_GRP, SS_ASM_GRP};

Example for AIX: 

[celcaix3]/grdbms/64bit/app/oracle/product/1120/rdbms/lib> cat config.s
# SS_DBA_GRP defines the UNIX group ID for sqldba adminstrative access.
# Refer to the Installation and User's Guide for further information.

.rename H.10.NO_SYMBOL{TC},""
.rename H.11.NO_SYMBOL{TC},""
.rename H.12.NO_SYMBOL{TC},""
.rename H.13.NO_SYMBOL{RO},""
.rename H.14.NO_SYMBOL{RO},""
.rename H.15.NO_SYMBOL{RO},""
.rename H.16.ss_dba_grp{TC},"ss_dba_grp"

.lglobl H.13.NO_SYMBOL{RO}
.lglobl H.14.NO_SYMBOL{RO}
.lglobl H.15.NO_SYMBOL{RO}
.globl ss_dba_grp{RW}

# .text section

# .data section

.toc
T.16.ss_dba_grp:
.tc H.16.ss_dba_grp{TC},ss_dba_grp{RW}
T.10.NO_SYMBOL:
.tc H.10.NO_SYMBOL{TC},H.13.NO_SYMBOL{RO}
T.11.NO_SYMBOL:
.tc H.11.NO_SYMBOL{TC},H.14.NO_SYMBOL{RO}
T.12.NO_SYMBOL:
.tc H.12.NO_SYMBOL{TC},H.15.NO_SYMBOL{RO}

.csect ss_dba_grp{RW}, 3
.llong H.13.NO_SYMBOL{RO}
.llong H.14.NO_SYMBOL{RO}
.llong H.15.NO_SYMBOL{RO}
# End csect ss_dba_grp{RW}

.csect H.13.NO_SYMBOL{RO}, 3
.string "dba"
# End csect H.13.NO_SYMBOL{RO}

.csect H.14.NO_SYMBOL{RO}, 3
.string "dba"
# End csect H.14.NO_SYMBOL{RO}

.csect H.15.NO_SYMBOL{RO}, 3
.string ""
# End csect H.15.NO_SYMBOL{RO}
.llong 0x00000000

# .bss section

Example for Solaris:

        .section        ".text",#alloc,#execinstr
/* 0x0000          7 */         .file   "x.c"

        .section        ".data",#alloc,#write
/* 0x0000          9 */         .global ss_dba_grp
/* 0x0000         10 */         .align  8

                        .global ss_dba_grp
                       ss_dba_grp:
/* 0x0000         17 */         .align  8
/* 0x0000         18 */         .xword  (.L12+0)
/* 0x0004         24 */         .align  8
/* 0x0004         25 */         .xword  (.L13+0)
/* 0x0008         26 */         .type   ss_dba_grp,#object
/* 0x0008         27 */         .size   ss_dba_grp,16

        .section        ".rodata1",#alloc
/* 0x0008         13 */         .align  8


                       .L12:
/* 0x0008         15 */         .ascii  "dba\0"
/* 0x0014         20 */         .align  8


                       .L13:
/* 0x0014         22 */         .ascii  "dba\0"

In the above examples we see that the user is a member of the "dba" group which matches the value of  SS_DBA_GRP in config.c. If the groups seem to be correctly configured but the connection is still failing use the script from Note 67984.1  to see whether Oracle evaluates correctly the group membership.

Intermittent ora-1031 errors for sysdba OS authentication can occur because of the nscd service that caches information from the /etc/passwd and /etc/group as configured in /etc/nscd.conf, if stopping this service makes the problem go away then please refer to the OS vendor.

On Windows

When using OS authentication on Windows the OS user must be a member of one of the following two groups:

• ORA_DBA
• ORA_<%ORACLE_SID%>_DBA

The membership to the second group allows the OS user to use OS authentication while connecting to the instance with the name %ORACLE_SID%> only. 

Check whether the OS user is a member of any of these two local groups:

Get the name of the OS user:

D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN>echo %username%
dbadmin

Obtain the list of the members of the local group: 

D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN>NET LOCALGROUP ORA_DBA
Alias name ORA_DBA
Comment Members can connect to the Oracle database as a DBA without a password
Members

-------------------------------------------------------------------------------
dbadmin
NT AUTHORITY\SYSTEM
The command completed successfully.
D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN>set oracle_sid=d1v10204

D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN>NET LOCALGROUP ORA_%ORACLE_SID%_DBA
Alias name ORA_d1v10204_DBA
Comment Members can connect to instance d1v10204 as a DBA without a password

Members
-------------------------------------------------------------------------------
dbadmin
The command completed successfully.

If the user is a member of these groups but the connection is still failing then compile the C program  from the following page and then run the executable to see whether Oracle evaluates the group membership correctly:

http://msdn.microsoft.com/en-us/library/aa370655(VS.85).aspx 

2. Check the value of the SQLNET.AUTHENTICATION_SERVICES parameter in file $ORACLE_HOME/network/admin/SQLNET.ORA

On Unix

This parameter should not be set if no strong authentication method is used. If such a method is being used then set the parameter to one of the following values:  

SQLNET.AUTHENTICATION_SERVICES = (ALL)

or

SQLNET.AUTHENTICATION_SERVICES = (BEQ,)

Where  can be any combination of the following values: TCPS, KERBEROS5, RADIUS

NOTE: If the definition of SQLNET.AUTHENTICATION_SERVICES is preceded by a leading blank space, an ORA-1031 error may occur

On Windows

This parameter should be set to NTS: 

SQLNET.AUTHENTICATION_SERVICES = (NTS)

If needed you can add other strong authentication methods besides NTS:

SQLNET.AUTHENTICATION_SERVICES = (NTS,TCPS)

Note: If the parameter is set to NONE then the OS authentication will be disabled and the user will have to provide a valid username/password combination to be able to connect to the database.

As above, if the definition of SQLNET.AUTHENTICATION_SERVICES is preceded by a leading blank space an ORA-1031 error may occur

Note: 
On Windows the user who is not able to connect as sysdba using OS authentication might be a domain user. Check the following if you are in this scenario: 

1. It is important that this user is a direct member of the local ORA_DBA group( See Note 1065023.1 for details). 
2. Oracle Service must be started as a user who is able to check the group membership for any domain user who might be connecting as sysdba locally( See Note 1071638.1 for details). 
3. Check whether the clocks of the RDBMS Server and of the Active Directory Server are perfectly synchronized. Even small clock drifts can cause issues to the underlying kerberos authentication mechanism used by default on Windows. In these cases the ORA-1031 would be most of the times intermittent. 
4. Check whether the Oracle Service is started by an user whose name contains non ASCII characters . See Note 1280996.1 for details

ORA-12638 might be seen when the user is managed in Active Directory rather than locally

On Windows, when the OS user is a domain user,  the error that is seen can be ORA-12638. In these situations it is important to obtain a client SQL*Net trace file and analyze the MS Windows errors encountered by function naun5authent: 

A) Add the following lines in client's sqlnet.ora file(The client might be the same as the RDBMS Server): 

trace_level_client = 16
trace_directory_client = c:\temp\newsqlnet

B) Retry the SYSDBA connection and get the SQL*Net trace file. Look for the string "SSPI" inside this file. You will see something similar to this: 

[02-OCT-2011 09:21:02:076] naun5authent: SSPI: 0x8009030c error in InitializeSecurityContext
[02-OCT-2011 09:21:02:076] naun5authent: exit
[02-OCT-2011 09:21:02:076] naunauthent: exit
[02-OCT-2011 09:21:02:076] nau_ccn: get credentials function failed
[02-OCT-2011 09:21:02:076] nau_ccn: failed with error 12638
[02-OCT-2011 09:21:02:076] nacomsd: entry
[02-OCT-2011 09:21:02:076] nacomfsd: entry
[02-OCT-2011 09:21:02:076] nacomfsd: exit
[02-OCT-2011 09:21:02:076] nacomsd: exit
[02-OCT-2011 09:21:02:076] nau_ccn: exit
[02-OCT-2011 09:21:02:076] na_csrd: failed with error 12638
[02-OCT-2011 09:21:02:076] na_csrd: exit
[02-OCT-2011 09:21:02:076] nacomer: error 12638 received from authentication service 

C) Search for the MS error (0x8009030c in the above example) in the following page: 

http://technet.microsoft.com/en-us/library/cc786775%28WS.10%29.aspx

D) Contact the AD administrators and let them perform the actions suggested by Microsoft. For the above SSPI error the solution is the following: 

"Logon Denied. Group Policy impersonates both the computer and the user when it determines the scope of policy, meaning it acts on behalf the user. This could be a machine account needing its password reset or a user account that has some problem. Check Active Directory to make sure the user account is set up correctly. Use a utility, like netdom or nltest, to test the computer account's password."

3. After checking the configuration as per steps 1) and 2) review the problems described in the following notes

Note 69642.1   - UNIX: Checklist for Resolving Connect AS SYSDBA Issues
Note 114384.1 - WIN: Checklist for Resolving CONNECT AS SYSDBA (INTERNAL) Issues 

4. If the problem is not solved after reviewing the above notes open a service request with Oracle Support and provide the following information

For Unix

A) File $ORACLE_HOME/network/admin/sqlnet.ora
B) File $ORACLE_HOME/rdbms/lib/config.c(or config.s)
C) The output of command "id" 
D) A trace file obtained with the following commands: 

Linux: 
strace -o /tmp/strace_sysdba.output -cfT sqlplus / as sysdba
AIX, Solaris: 
truss -fea -o /tmp/truss_sysdba.output sqlplus / as sysdba

HP-UX: 
tusc -afpo /tmp/tusc_sysdba.output -aef sqlplus / as sysdba

For Windows

A) File %ORACLE_HOME%/network/admin/sqlnet.ora
B) The output of the commands  "echo %username% and  "NET LOCALGROUP ORA_DBA"
C) A pair of client/server SQL*Net traces obtained while reproducing the problem as per Note 395525.1 and Note 374116.1.