Skip to Main Content
  • Questions
  • After install of 12,2 on Windows 10, I can only connect as SYS

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Terry.

Asked: October 11, 2017 - 7:55 pm UTC

Last updated: October 13, 2017 - 12:11 pm UTC

Version: 12.2.0.1

Viewed 1000+ times

You Asked

I have installed Oracle 12c Release 2 twice as a non-container database. The issue I have run into both times is that I keep getting "ORA-01017: invalid username/password; logon denied" every time I try connecting as any user other than SYS. See examples below. This happens even if I set the password right before trying.


SQL> CONNECT / AS SYSDBA
Connected.
SQL>
SQL> CONNECT SYS/coi AS SYSDBA
Connected.
SQL>
SQL> CONNECT SYSTEM/mgr
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL>
SQL> CONNECT / AS SYSDBA
Connected.
SQL>
SQL> ALTER USER SYSTEM IDENTIFIED BY mgr;

User altered.

SQL>
SQL> CONNECT SYSTEM/mgr
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL>
SQL> CONNECT / AS SYSDBA
Connected.
SQL>
SQL> ALTER USER HR IDENTIFIED BY HR ACCOUNT UNLOCK;

User altered.

SQL>
SQL> CONNECT HR/HR
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL>
SQL> CONNECT / AS SYSDBA
Connected.
SQL> CONNECT HR/HR@ORG
ERROR:
ORA-01017: invalid username/password; logon denied

and Connor said...

How did you create the database ? And did you do any customising with the spfile ?

The only cause I can think of for this comes when people use a non-standard or older pfile, and hence run into the issue described by Mike here

https://mikedietrichde.com/2017/04/24/having-some-fun-with-sec_case_sensitive_logon-and-ora-1017/

Can you reproduce this with stock standard use of dbca with a pre-delivered template and all the defaults taken ?

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

The real culprit!

Terry Stough, October 13, 2017 - 10:47 am UTC

I can actually give the solution and I think this is a bug that needs to be addressed. I had set SEC_CASE_SENSITIVE_LOGON to false in a script that ran at the very end of my installation protocol.

I checked connectivity up until I ran that script and it worked fine. After running it, I could never again connect as SYSTEM, for example, in Enterprise Manager Database Express, SQL*Plus, SQLcl standalone 17.3 or SQL Developer 17.3.

Apparently in 12cR2, if this deprecated parameter is set, no passwords are accepted.

Connor McDonald
October 13, 2017 - 12:11 pm UTC

We do actually document this in the Upgrade Guide

https://docs.oracle.com/database/122/UPGRD/case-insensitive-passwords-ora-1017-invalid-username-password.htm#UPGRD-GUID-FDA9C77A-12F4-4410-9448-9BCC13960C27

I have to ask - why did you set it ? Nothing in the 12.2 db creation manuals suggests that you should do this.

The problem script

Terry Stough, October 13, 2017 - 12:21 pm UTC

Sorry, I had not had my first coffee when I responded. The problem script was one written at 11g and that line did not get removed from the script. I probably did not read the Upgrade Guide because it was a fresh install.

I changed the response rating to Very Useful.

Thanks for your help.

More to Explore

APEX

Keep your APEX skills fresh by attending their regular Office Hours sessions.