Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

ORA-01017 invalid username/password; logon denied using TOAD

Posted on 2010-09-13
8
Medium Priority
?
9,901 Views
1 Endorsement
Last Modified: 2012-05-10

Hello Experts,

Need your urgent help please.

When i am trying to connect 11g database which is on HP-UX 11.31 os, as a normal user i can easily connect but when i try to connect with sys as sysdba through Toad which is on client pc I am getting this error.

ORA-01017: invalid username/password; logon denied.

I am able to connect with sys as sysdba from sqlplus promt and oem but not through toad.

Please help me out.

Valuable advices will be highly appreciable.

Thanks in advance
1
Comment
Question by:newbie27
  • 3
  • 3
  • 2
8 Comments
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 33662303

Oracle 11g has case-sensitive passwords (if not disabled) and some older version of TOAD convert the password you enter to UPPERCASE.
:p

0
 
LVL 8

Expert Comment

by:Rindbaek
ID: 33662567

One possible solution is that you dont have access to login remotely as sys

Check if sys has access to login remote as sysdba
SQL> select * from V$PWFILE_USERS;
USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE

SQL> show parameter REMOTE_LOGIN_PASSWORDFILE
NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------------
remote_login_passwordfile            string      EXCLUSIVE

If you dont have a password file:
Create a password file,  as oracle user On the OS:
orapwd FILE=orapw<NAME_OF_SID> ENTRIES=100
set entries higher than the max number of you ever expect to have.
 
If you dont have REMOTE_LOGIN_PASSWORDFILE set to exclusive or shared:
In the database you must set the initialization parameter REMOTE_LOGIN_PASSWORDFILE to either EXCLUSIVE or SHARED. I would recomend to set it to EXCLUSIVE.
Alter system set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE scope=BOTH

If sys dont have sysdba=true in select:
grant sysdba to sys;

see http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/dba007.htm 

Another problem could be the version of toad. Is it compatible with oracle 11? Oracle 11 is the first oracle version that are case sensitive in the passwords.
So check if the toad version supports oracle 11.
Also check the password_version of sys (10 or 11)
SQL> select username, PASSWORD_VERSIONS from dba_users
USERNAME                       PASSWORD
------------------------------ --------
DUMMY                          10G
APEX_PUBLIC_USER               10G 11G

If the password version is 10G it should be ok, then it's still case insensitive. and i would expect that even an old version of toad will work.
 
0
 
LVL 8

Author Comment

by:newbie27
ID: 33670008
@Rindbaek

Thanks for your kind reply and sorry for not mentioning in my query about all the steps have already  gone through which you have mention in your reply.

Want to let you know that sys and one normal user to whom i have also grant the sysdba privs can be seen in the below remote access list.  

SQL> select * from V$PWFILE_USERS;
USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE
USER1                         TRUE  TRUE  FALSE

and also the password file is available with entries parameter value more than 1, below is the status for the password file.

SQL> show parameter REMOTE_LOGIN_PASSWORDFILE
NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------------
remote_login_passwordfile            string      EXCLUSIVE

I am using TOAD 9.0.1v
As a normal user i can connect easily with the same password but as a sysdba cannot.

Please help me out.

Thanks
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 8

Expert Comment

by:Rindbaek
ID: 33670075
ok lets try to pinpoint where to look for the error.
Can you  connect with sqlplus as sysdba from
1) The machine with toad and what client version do you use?
2) From another machine with sqlplus and what client version do you use?
eg sqlplus user1/password@yourdbserver as sysdba
Also have you tried to recreate the password file?
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 33671392
>> As a normal user i can connect easily with the same password but as a sysdba cannot.As a normal user i can connect easily with the same password but as a sysdba cannot.

What is your setting for SEC_CASE_SENSITIVE_LOGON?
If you have set it to FALSE, all passwords are NOT case sensitive EXCEPT for SYSDBA.

Good luck!



0
 
LVL 8

Author Comment

by:newbie27
ID: 33680002
@Rindbaek :

Let you aware with some more details :

Client version : Windows XP SP2
Toad version : TOAD 9.0.1
I have recreated the password file.
When i login with sys using sqlplus as sysdba, i can easily login and the result for sho user is sys
but when i login with user1 who has sysdba priv using sqlplus, i can login but the result for sho user is sys again.

And when i try to login as sydba with the same passwords using TOAD facing the error :
ORA-01017: invalid username/password; logon denied.

@Mike :
Regarding the setting for SEC_CASE_SENSITIVE_LOGON, its FALSE now, even it doesn't work when i try by setting the value as TRUE

Waiting for the valuable advices.
Thanks
0
 
LVL 8

Accepted Solution

by:
Rindbaek earned 2000 total points
ID: 33680047
The show user will show sys whan you are logged in as sysdba. Thats expected behaviour. but you can login through sqlplus at the client as sysdba. so it looks liek a toad issue.  I think toad 9.0.1 was from before oracle 11 and as such will pass the password in uppercase as mike states.
I think you should test from an other client with the lates toad version (10.5.1 i believe it is).
 
0
 
LVL 8

Author Closing Comment

by:newbie27
ID: 33680300
Thanks a lot, 10.5 v solved my problem which i was facing.
Thanks to you all.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question