Oracle 12c SEC_CASE_SENSITIVE_LOGON and ORA-01017

The init.ora/spfile parameter SEC_CASE_SENSITIVE_LOGON got deprecated since Oracle Database 12.1.0.1
This means, we don’t do any further developments to it, you shouldn’t change it from its default TRUE and if you still do you’ll receive a nice warning during STARTUP of your database:

SQL> alter system set sec_case_sensitive_logon=false scope=spfile;
System altered.

SQL> startup force
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Recently a user asked me if we’d changed the behavior of this parameter in Oracle Database 12c Release 2 as he receives now an ORA-01017: Invalid username or password error when having SEC_CASE_SENSITIVE_LOGON=FALSE with every user except SYSDBAs

In which situations may you receive an ORA-01017?

This is outlined in the Oracle 12.1 documentation already:
•Ensure that the SEC_CASE_SENSITIVE_LOGON parameter is not set to FALSE if the SQLNET.ALLOWED_LOGON_VERSION_SERVER parameter is set to 12 or 12a
This is because the more secure password versions used for this mode only support case-sensitive password checking.
For compatibility reasons, Oracle Database does not prevent the use of FALSE for SEC_CASE_SENSITIVE_LOGON when SQLNET.ALLOWED_LOGON_VERSION_SERVER is set to 12 or 12a
Setting SEC_CASE_SENSITIVE_LOGON to FALSE when SQLNET.ALLOWED_LOGON_VERSION_SERVER is set to 12 or 12a causes all accounts to become inaccessible.

The key is the sqlnet.ora parameter SQLNET.ALLOWED_LOGON_VERSION_SERVER and here’s the difference between Oracle Database 12.1 and Oracle Database 12.2:
•Oracle Database 12.1: SQLNET.ALLOWED_LOGON_VERSION_SERVER defaults to 11 out of the box
•Oracle Database 12.2: SQLNET.ALLOWED_LOGON_VERSION_SERVER defaults to 12 out of the box

Behavior difference Oracle 12.1 vs Oracle 12.2

See this simple example after switching SEC_CASE_SENSITIVE_LOGON=FALSE in both databases (as shown above):
•Oracle Database 12.1.0.2:

SQL> alter user system identified by oracle;
User altered.

SQL> connect system/oracle
Connected.

•Oracle Database 12.2.0.1:

SQL> alter user system identified by oracle;
User altered.

SQL> connect system/oracle
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE

How to workaround the ORA-01017?

First of all you need to edit your sqlnet.ora adding (or lowering) the parameter SQLNET.ALLOWED_LOGON_VERSION_SERVER to a value below 12
But if you try to connect directly after restarting your listener you will receive the same ORA-01017 again
The secret is mentioned in the above documentation link as well, you will have to recreate the user’s password if you need the logon process to work as it did work before Oracle Database 12.2

sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
SQLNET.ALLOWED_LOGON_VERSION_SERVER=11

Check in DBA_USERS

SQL> select username, password_versions 
from DBA_USERS 
where username='SYSTEM';

USERNAME      PASSWORD_VERSIONS
------------- ---------------------
SYSTEM        11G 12C

There’s no “10G” mentioned. This will prevent the connection.

Solution: You will have to specify the password again respective ALTER the user(s):

SQL> alter user system identified by oracle;
User altered.

SQL> select username, password_versions 
from DBA_USERS 
where username='SYSTEM';

USERNAME       PASSWORD_VERSIONS
-------------- ----------------------
SYSTEM         10G 11G 12C

SQL> connect system/oracle
Connected.

How to check if Replication components are installed on your SQL Server

If you need to check as to whether or not SQL Replication components are installed on a specific instance of your SQL Server
Execute the following command on that instance which will give you the answer

EXEC sp_ms_replication_installed

ODBC error: SQLSTATE 37000 native database error 4060

Now, when you go to start the extract/replicat, you may get the following error:
ERROR OGG-00551 Database operation failed: Couldn’t connect to [ dns ]. ODBC error: SQLSTATE 37000 native database error 4060. [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot open database “db_name” requested by the login. The login failed.
The error message is a little bit misleading. It tells you that the process cannot connect to the database which you were able to connect to from the GGSCI command prompt with no issue.

Why is this?
The issue lies in the fact that the manager (MGR) process is running as a service and does not have the correct permissions to access the database from the service.

In searching MOS for this error, I was found Note ID: 1633138.1.
In this note, notice that this issue is known as of Oracle GoldenGate version 12.1.2.x.x. The note also provides you a fix to this issue. In simple terms, since the manager process is running as a service; additional permissions have to be granted to manger.

To grant the SYSADMIN privilege for the manager process follow the below sequence of steps (on windows after all):
1. Manager is installed as service:
Open SQL Server Management studio -> Security ->login>select NT AUTHORITY\SYSTEM ->Right Click -> Properties–>Server Role –>Enable sysadmin role
2. ggsci>stop mgr
3. ggsci>start mgr
4. ggsci>start extract <extract-name>

After granting the sysadmin role, the extract will start.