DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: ORA-01031 Insufficient privilege when connecting from client

  1. #1
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620

    ORA-01031 Insufficient privilege when connecting from client

    Hi Guys,

    Oracle 81730 on Win2000 platform.

    ORA-01031 error is somehow always haunting me. Today, I found out that from client, if I say connect sys/@xyz as sysdba, it gives ORA-01031 Insufficient privilege.

    But from server, the same thing works.

    remote_login_passwordfile = exclusive

    select * from v$pwfile_users

    USERNAME SYSDBA SYSOPER
    ------------------------------
    INTERNAL TRUE TRUE
    SYS TRUE TRUE

    In SQLNET.ORA on server,
    #
    SQLNET.AUTHENTICATION_SERVICES= (NTS)
    NAMES.DIRECTORY_PATH=(TNSNAMES, ONAMES, HOSTNAME)

    Pl. help....

    Thanks in Adv.
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  2. #2
    Join Date
    Dec 2002
    Location
    USA
    Posts
    53
    Could you try
    comment out SQLNET.AUTHENTICATION_SERVICES= (NTS)
    In SQLNET.ORA on server,
    #SQLNET.AUTHENTICATION_SERVICES= (NTS)
    NAMES.DIRECTORY_PATH=(TNSNAMES, ONAMES, HOSTNAME)

  3. #3
    Join Date
    Sep 2002
    Posts
    30
    Do you mean "connect sys/@xyz as sysdba"? This command is invalid for me in either server or client. But "connect /@xyz as sysdba" or "connect sys/passwd@xyz as sysdba" works. Pls advice.

  4. #4
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by ly
    Do you mean "connect sys/@xyz as sysdba"? This command is invalid for me in either server or client. But "connect /@xyz as sysdba" or "connect sys/passwd@xyz as sysdba" works. Pls advice.
    LY :

    He says hez getting ORA-01031 Insufficient privilege.

    Had he jus given conn Sys/@connect string as sysdba, then he wud have got

    Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
    where ::= [/][@] | /

    Probalabaly he forgot to type "PSWD"

    Its suerly the problem in SQL * NET. NTS authentiation should be commented.....

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  5. #5
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Hi Guys,

    That was the typing error. After I commented SQLNET.AUTHENTICATION_SERVICES= (NTS) in sqlnet.ora, now it gives ORA-

    ORA-1017: Invalid Username/password error; logon denied error

    Pl. help me resolve this problem.

    Thanks,
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  6. #6
    Join Date
    Dec 2002
    Location
    USA
    Posts
    53
    Now you put invalid sysdba password. To solve the problem

    1) local connect in as sysdba ( / as sysdba, do not need password)
    2) shutdown the database
    3) create new password file by
    $ orapwd file=$ORACLE_HOME/dbs/orapwSID password=new_password entries=6
    4) restart database
    5) alter user sys identified by new_password; (sometimes the password in passwd file does not match passwd inside database)
    6) remotely connect as sysdba
    sys/new_password@SID as sysdba

  7. #7
    Join Date
    Sep 2002
    Posts
    30
    Hi abhaysk,

    I definitely understand what his question is. But I need to know what he really want to do. And I don't think NTS authentiation should be commented.

    samdba,
    If you want to use "connect /@xyz as sysdba" to conn, you may refer to http://216.239.53.100/search?q=cache...N_SERVICES%3D+(NTS)&hl=en&ie=UTF-8 .

    Otherwise, you can try supower's method.

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by ly
    Hi abhaysk,

    I definitely understand what his question is. But I need to know what he really want to do. And I don't think NTS authentiation should be commented.
    LY,

    If u give NTS Authentication, then u need to add the member's NTS account in the ORA_DBA group on server side.

    If u comment NTS authentication....then the pswd of SYS can be used to connect as SYSDBA.

    If u dont comment and try to connect as SYS with it pswd and the member NTS account not included in ORA_DBA group, then no matter if u give correct pswd it will not login into oracle database as SYS.


    I belive this clears why we should not give NTS authentcation.....

    Regards
    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  9. #9
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Hi Guys,

    Thanks for useful contribution to my thread.

    Now, here is the situation.

    in SQLNET.ORA, SQLNET.AUTHENTICATION_SERVICES= (NTS).

    I recreated password file with foll. command.

    orapwd file=PWDTEST.ORA password=syspwd entries=30.

    After this, I am now able to connect to sys user as sysdba but now I am not able to connect to internal user as sysdba, which I was able to do before recreating the password file.

    Supower's link is really good for understanding. How can I check whether the user is a member of the ORA_DBA local group though?

    Could someone pl. explain?

    Thanks,
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  10. #10
    Join Date
    Dec 2002
    Location
    USA
    Posts
    53
    Comment out SQLNET.AUTHENTICATION_SERVICES= (NTS)
    in SQLNET.ORA. Now you should be able to connect sysdba, internal, and remote sysdba (if remote_login_passwordfile = exclusive).

    General speaking (my experience):
    set SQLNET.AUTHENTICATION_SERVICES= (NTS) in Wins
    so you do not need sys passwd to start db.

    comment out SQLNET.AUTHENTICATION_SERVICES= (NTS)
    in Unix, linux so you do not need sys passwd to start db.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width