6 Replies Latest reply: Dec 7, 2013 11:39 AM by rp0428 RSS

    4.0EA3: SQLDeveloper can't connect to TestDB because ORA-01031

    David_Pasternak

      Hello @ all,

       

      I have a test DB server unter RHEL6 with Oracle 11gR2 (11.2.0.3.7) to which i try to connect with SQLDeveloper 4.0EA3 from Windows 7 with user SYS. So i configured all things as a new connection but everytime i try a test connection I get a "ORA-01031: insufficient privileges". But I try to connect as sys with sysdba Role of course. Not enough privileges with sys user? I'm a little bit confused.

       

      Thanks for all help!

       

      Regards,

      David

        • 2. Re: 4.0EA3: SQLDeveloper can't connect to TestDB because ORA-01031
          Jim Smith

          Not really a SQL Developer issue.

          You can't connect remotely as sysdba unless you have a password file.

          • 3. Re: 4.0EA3: SQLDeveloper can't connect to TestDB because ORA-01031
            rp0428
            Not really a SQL Developer issue.

            I can't tell based only on what OP posted. But it isn't as simple as it might first appear. The SQL Dev team will need to respond as to the extent of sql developer's support for the authentication methods that Oracle supports on various platforms (see below).

            You can't connect remotely as sysdba unless you have a password file.

            Sure you can - See the DBA guide for the specifics of the different authentication methods. The 'Selecting an Authentication Method for Database Administrators' section in particular but the entire page at this link has additional info:

            http://docs.oracle.com/cd/B28359_01/server.111/b28310/dba006.htm#i1006628

            Database Administrators can authenticate through the database data dictionary, (using an account password) like other users. Keep in mind that beginning with Oracle Database 11g Release 1, database passwords are case sensitive. (You can disable case sensitivity and return to pre–Release 11g behavior by setting the SEC_CASE_SENSITIVE_LOGON initialization parameter to FALSE.)

            In addition to normal data dictionary authentication, the following methods are available for authenticating database administrators with the SYSDBA or SYSOPER privilege:

            •   Operating system (OS) authentication
            •   A password file
            •   Strong authentication with a network-based authentication service, such as Oracle Internet Directory

            These methods are required to authenticate a database administrator when the database is not started or otherwise unavailable. (They can also be used when the database is available.)

            Also it is not commonly known but on Windows platforms you can even use OS authentication remotely!

            Connecting Using Operating System Authentication

            A user can be authenticated, enabled as an administrative user, and connected to a local database by typing one of the following SQL*Plus commands:

            CONNECT / AS SYSDBA  CONNECT / AS SYSOPER  

            For the Windows platform only, remote operating system authentication over a secure connection is supported. You must specify the net service name for the remote database:

            CONNECT /@net_service_name AS SYSDBA  CONNECT /@net_service_name AS SYSOPER  

            Both the client computer and database host computer must be on a Windows domain.

            As mentioned earlier only the sql dev team will need to address which of those various methods, and on which platforms, authentication is supported.

            • 5. Re: 4.0EA3: SQLDeveloper can't connect to TestDB because ORA-01031
              Jim Smith

              <tedious lecture snipped>

               

              By far the likeliest cause of 1031 trying to connect as sysba remotely is the lack of a password file.  It is usually best to investigate the likely causes before pursuing esoteric solutions.

              • 6. Re: 4.0EA3: SQLDeveloper can't connect to TestDB because ORA-01031
                rp0428

                My reply was directly responsive to OPs issue. In part it was directed at this false statement that you made:

                You can't connect remotely as sysdba unless you have a password file.

                As I documented that statement is just plain WRONG. It is important to correct misinformation that otherwise well-meaning people may post.

                 

                There was no 'lecture'; just an explanation of the facts supported by excerpts from the Oracle documentation so that others may research the context in which those excerpts exist.

                By far the likeliest cause of 1031 trying to connect as sysba remotely is the lack of a password file.

                Assuming, arguendo, that your statement is true, it is tangential to anything that I said in my reply.

                 

                First I was correcting your erroreous statement that you can't connect remotely at all without a password file.

                 

                Second I was providing extended info on what methods are available in general. As I said above I can't speak to which of those methods sql developer is intended to support. That is for Oracle to decide. 

                  It is usually best to investigate the likely causes before pursuing esoteric solutions.

                Good advice for OP, and others, to follow. Although I'm not sure what 'esoteric' solutions you refer to since none have been mentioned so far in this thread.