0

I'm trying to change the SYS password of an Oracle 11g database to be case-insensitive for legacy application reasons. My research suggests all I need to do is set the SEC_CASE_SENSITIVE_LOGON flag, and change the SYS password so it will become case-insensitive.

I connect via SQL*Plus with the current password, and make these changes:

C:\Users\KWalsh>sqlplus sys/PASSWORD@database3 as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 5 11:19:12 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;

System altered.

SQL> alter user sys identified by password;

User altered.

SQL> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Pr
oduction
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Users\KWalsh>

Now I try reconnecting with the initial, ALL-CAPS password. I believe it should allow this with case sensitivity disabled, but this happens:

C:\Users\KWalsh>sqlplus sys/PASSWORD@database3 as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 5 11:23:07 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

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


Enter user-name:

The lowercase password permits access to the database. Any ideas what I'm doing wrong?

  • Seeing as it's sys as SYSDBA, could be a password file case sensitivity problem. See oracleflash.com/37/Oracle-11g-Case-Sensitive-Passwords.html – Philᵀᴹ Mar 5 '14 at 11:52
  • @Phil the database server is running on Windows; I don't think a password file is created automatically, is it? I don't think one is being used in this case. – Kieran Mar 5 '14 at 11:58
  • Are you logged in to the database server when you do this? Are you sure that you're not using operating system authentication (in which case the password will be completely ignored)? If you enter a totally incorrect password, do you still log in successfully? – Justin Cave Mar 6 '14 at 7:01
  • @JustinCave we're not using OS authentication. Incorrect passwords result in "ORA-01017: invalid username/password; logon denied" – Kieran Mar 6 '14 at 11:14
1

Create a password file, set it to insensitive. You should have a password file, even on windows, even if all it does is define who can connect as a sysdba. I just installed a default Standard Edition and a password file was created. THe last time I built a new EE system it was the same way.

Here's the doc for how to set one up if you don't know. Make sure you use the case sensitive switch and turn it off: http://docs.oracle.com/cd/B28359_01/server.111/b28310/dba007.htm#ADMIN10241

  • Thanks, that worked perfectly. In the end the commands I used were DEL C:\oracle\product\11.2.0\dbhome_1\database\PWDDATABASE3.ora then ORAPWD FILE=C:\oracle\product\11.2.0\dbhome_1\database\PWDDATABASE3.ora IGNORECASE=Y – Kieran Mar 7 '14 at 14:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.