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.


SQL Developer - new connection

---------------------------


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

  • SQL Developer - new connection

    Having established how to connect to Oracle "as sydba" from SQL Developer, it is unlikely that you will need to do so very often. When you install Oracle XE, SQL*Plus is also installed and you can use this to start and stop the database.

    You might also be interested in our Oracle tutorials, our PL/SQL tutorials (including how to wrote a "Hello World" program in 30 seconds) and our SQL tutorials.

    See our Oracle training page for information about formal training courses both on-line and in person.

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

    Average Rating starstarstarstarstar

    Click here to add your own comments

    Apr 19, 2014
    Good
    by: Lenin

    Thank you for the post, really good step by step analysis..

    Jan 19, 2013
    Thanks
    by: Jyoti

    Thanks a Lot
    it worked

    Dec 14, 2011
    It works. Thanks
    by: Muthu

    It works. Thanks.

    Click here to add your own comments

    Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.