Problem connecting to Oracle "as sysdba" from Oracle SQL Developer
(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.
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
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".
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.
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
In Oracle SQL Developer
open 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