I am trying to connect to my Oracle DB with Windows authentication, and it just doesn't seem to work - Whatever I do I keep getting "ORA-01017: invalid username/password; logon denied".
I installed Oracle DB version 21c, and upon that created a new windows user as the Oracle Home User. My global DB name is orcl.ad11.siemens.net, and the PDB is orclpdb.
I've created a new listener with the following configuration in listener.ora:
LISTENER_2 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
(ADDRESS = (PROTOCOL = IPC)(KEY = PNPKEY))))
SID_LIST_LISTENER_2 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORCLPDB.AD11.SIEMENS.NET)
(ORACLE_HOME = C:\Oracle\OraDB21c\WINDOWS.X64_213000_db_home)
(SID_NAME = ORCLPDB)))
And added an alias in tnsnames.ora:
LISTENER_2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
(CONNECT_DATA =
(SERVICE_NAME = ORCLPDB.AD11.SIEMENS.NET)))
When I execute "lsnrctl status LISTENER_2", I get:
STATUS of the LISTENER
------------------------
Alias LISTENER_2
Version TNSLSNR for 64-bit Windows: Version 21.0.0.0.0 - Production
Start Date 05-DEC-2024 15:18:31
Uptime 0 days 0 hr. 1 min. 0 sec
Trace Level admin
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\Oracle\OraDB21c\WINDOWS.X64_213000_db_home\network\admin\listener.ora
Listener Log File C:\Oracle\OraDB21c\diag\tnslsnr\<my-hostname>\listener_2\alert\log.xml
Listener Trace File C:\Oracle\OraDB21c\diag\tnslsnr\<my-hostname>\listener_2\trace\ora_23584_28284.trc
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1522)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\PNPKEYipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=<my-hostname>.ad11.siemens.net)(PORT=5500))(Security=(my_wallet_directory=C:\ORACLE\ORADB21C\admin\orcl\xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "75b0d2bd17c4485c998c967244b4515d.ad11.siemens.net" has 1 instance(s).
Instance "orcl", status READY, has 2 handler(s) for this service...
Service "ORCLPDB.AD11.SIEMENS.NET" has 2 instance(s).
Instance "ORCLPDB", status UNKNOWN, has 1 handler(s) for this service...
Instance "orcl", status READY, has 2 handler(s) for this service...
Service "bc74b627f47a416ab26784e660b5c7b2.ad11.siemens.net" has 1 instance(s).
Instance "orcl", status READY, has 2 handler(s) for this service...
Service "orcl.ad11.siemens.net" has 1 instance(s).
Instance "orcl", status READY, has 2 handler(s) for this service...
Service "orclXDB.ad11.siemens.net" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
I noticed the status of ORCLPDB is UNKNOWN but I have no idea why - guess it might be something to consider as the source of the problem.
Additionally, I added the following line to sqlnet.ora:
SQLNET.AUTHENTICATION_SERVICES = (NTS)
And I also granted my current windows user with the required privileges, and it's verified by the fact that:
ALTER SESSION SET CONTAINER = ORCLPDB;
SELECT USERNAME, AUTHENTICATION_TYPE FROM DBA_USERS WHERE AUTHENTICATION_TYPE = 'EXTERNAL';
Returns my current windows user.
I use the following command to connect:
sqlplus /@LISTENER_2
And tried many other ways as well, but keep getting that ORA-01017.
Does anyone have a clue what is the problem here and how can I fix it?