Solved

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

Posted on 2010-09-13
8
7,878 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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 500 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

707 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now