I'm fairly new to the OracleDB. I'm looking into connecting client applications to the Oracle DB on a server without providing username and password. Is my understanding is correct that this can be achieved using the orapki wallet?
I'm using my laptop to test this by acting as both client and server.
I'm usingorapki to create wallets for client and server side as shown below:
Create Server Wallet :
orapki wallet create -wallet "C:/app/wallet" -pwd Welcome1 -auto_login
orapki wallet add -wallet "C:/app/wallet" -pwd Welcome1 -dn "CN=MyHostName" -keysize 1024 -self_signed -validity 3650 -sign_alg sha256
orapki wallet export -wallet "C:/app/wallet" -pwd Welcome1 -dn "CN=MyHostName" -cert C:/app/wallet/MyHostName-certificate.crt
Create Client Wallet: [I choose CN name to match username for oracleDB login name, i.e., abcd]
orapki wallet create -wallet "C:/app/client_wallet" -pwd Welcome1 -auto_login
orapki wallet add -wallet "C:/app/client_wallet" -pwd Welcome1 -dn "CN=abcd" -keysize 1024 -self_signed -validity 3650 -sign_alg sha256
orapki wallet export -wallet "C:/app/client_wallet" -pwd Welcome1 -dn "CN=abcd" -cert C:/app/client_wallet/abcd-certificate.crt
Load certificate from one side to the wallet on the other side.
Load the server certificate into the client wallet.
orapki wallet add -wallet "C:/app/client_wallet" -pwd Welcome1 -trusted_cert -cert C:/app/wallet/MyHostName-certificate.crt
Load the Client certificate into the server wallet.
orapki wallet add -wallet "C:/app/wallet" -pwd Welcome1 -trusted_cert -cert C:/app/client_wallet/abcd-certificate.crt
Displaying wallets gives below results:
Server Wallet:
orapki wallet display -wallet "C:/app/wallet" -pwd Welcome1
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.
Requested Certificates:
User Certificates:
Subject: CN=MyHostName
Trusted Certificates:
Subject: CN=abcd
Subject: CN=MyHostName
Client Wallet:
orapki wallet display -wallet "C:/app/client_wallet" -pwd Welcome1
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.
Requested Certificates:
User Certificates:
Subject: CN=abcd
Trusted Certificates:
Subject: CN=MyHostName
Subject: CN=abcd
My understanding from above is that the server and client now trusted each other and hence good for establishing connections.
Server settings files as shown below:
tnsnames.ora
TCP_ACTIVE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = MyHostName)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
TCPS_ACTIVE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCPS)(HOST = MyHostName)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
listener.ora
SSL_CLIENT_AUTHENTICATION = TRUE
WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = C:\app\wallet)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = C:\app\MyUser\product\12.1.0\dbhome_2)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:C:\app\MyUser\product\12.1.0\dbhome_2\bin\oraclr12.dll")
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = MyHostName)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCPS)(HOST = MyHostName)(PORT = 1522))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES= (BEQ, TCPS, NTS)
SQLNET.WALLET_OVERRIDE = FALSE
#SSL_VERSION = 0
TRACE_LEVEL_CLIENT = SUPPORT
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
SSL_CLIENT_AUTHENTICATION = TRUE
WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = C:\app\wallet)
)
)
#SSL_CIPHER_SUITES= (SSL_RSA_WITH_AES_256_CBC_SHA, SSL_RSA_WITH_3DES_EDE_CBC_SHA)
I have a simple c++ test client using OCCI with setting files as shown below
tnsnames.ora
TCP_ACTIVE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = MyHostName)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
TCPS_ACTIVE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCPS)(HOST = MyHostName)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
sqlnet.ora
TRACE_LEVEL_CLIENT=support
WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = C:\app\client_wallet)
)
)
SSL_CLIENT_AUTHENTICATION =TRUE
SSL_SERVER_DN_MATCH=OFF
TRACE_LEVEL_CLIENT = 16
TRACE_FILE_CLIENT = client_trace
TRACE_TIMESTAMP_CLIENT = ON
TRACE_DIRECTORY_CLIENT = C:\Client
DIAG_ADR_ENABLED=ON
My server Listener Service status is as shown below:
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHostName)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 64-bit Windows: Version 12.1.0.2.0 - Production
Start Date 24-OCT-2019 19:46:17
Uptime 0 days 21 hr. 31 min. 25 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\app\MyUser\product\12.1.0\dbhome_2\network\admin\listener.ora
Listener Log File C:\app\MyUser\diag\tnslsnr\MyHostName\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=MyHostName)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=MyHostName)(PORT=1522)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=MyHostName)(PORT=5500))(Security=(my_wallet_directory=C:\app\wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
When I try to connect to the server using connection string "TCP_ACTIVE" and supply username and password, I'm able to connect normally
Environment *env = Environment::createEnvironment();
Connection *conn = env->createConnection(m_username.c_str(), m_password.c_str(), m_dbConnectionString.c_str());
The connection in "conn" seems to form and able to use it to run simple query successfully.
But when I'm trying to connect to the server using TCPS_ACTIVE connection string, I'm able to connect only when I supply the username and the password. Below is the error message I got when I don't supply the user name and password:
ORA-01017: invalid username/password; logon denied
I am curious if it is possible to connect to the server using the wallet and without using a username and password? If so, how should I set it up for test?
Thank you
P.S. : I've look into similar question but I can't understand how the setting was done.
It seems my user needs to be identified externally as "CN=abcd" for it to work. Go to SQL Developer with admin login and run :
alter user abcd identified externally as 'CN=abcd';
Above code able to connect without using the password.