Problem connecting to Oracle "as sysdba" from Oracle SQL Developer

by John
(Auckland, New Zealand)

I have an Oracle 10g XE database running on Windows Vista and I want to connect "as sysdba" from Oracle SQL Developer. When I test the connection I get the error ORA-01031: insufficient privileges.





There are a number of possible causes for this, so the best approach is to check them all.

Step 1

Check the current Windows' user (the one you're logged on as) is a member of the ORA_DBA group.

To do this

• right click on My Computer
• select Manage
• then, Local Users and Groups
• then Groups
• then Oracle DBA Group (probably ora_dba)
• and ensure your username is in the list of members or add it, if not

Step 2

Ensure sqlnet.ora in ORACLE_HOME\NETWORK\ADMIN\ contains the following line:

SQLNET.AUTHENTICATION_SERVICES = (NTS)

if not add it, then stop and restart the listener.

If you have the line

SQLNET.AUTHENTICATION_SERVICES = (none)

you must enter a password for SYS user which means you won't be able to connect "/ as sysdba".

Step 3

Open tnsnames.ora (in $ORACLE_HOME\NETWORK\ADMIN\) and ensure the entry for your database(s) has a name for the host rather than an ip address.

XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)

Step 4

In Oracle SQL Developeropen the dialogue box to create a new connection

select the role as SYSDBA

select "OS Authentication" to avoid having to supply a password

change the connection type to "TNS"

select "Connect Identifier"

pick the relevant connection string

finally, test the connection and everything should be fine