How to secure connections to your Oracle database over TLS or by SSH tunnels

If you want to establish a connection to your Oracle database over the public Internet, it is recommended to do so by encrypted connections. The Oracle database tools allow users to connect via Transport Layer Security (TLS) or by using SSH tunnels. In this blog post I would like to show how you can connect over SSH tunnels, how to set up your database for allowing secure connections to your database over TLS, as well as how to use client certificates for user authentication. Additionally I will explain any kind of configuration needed when using tools like SQLcl, SQL Developer or the Visual Studio Code Extension of SQL Developer.

Connections over TLS

If you want to allow TLS encrypted connections to your database, you can create and use an Oracle Wallet to save your certificates. To create the Oracle Wallet, you can use the orapki utility which is part of each database installation. You can find the utility in the bin directory of the Oracle Home – $ORACLE_HOME/bin/orapki.

It is possible to create a server wallet and client wallet, whereby the client wallet is optional. This is the case if

• a client certificate is not needed for user authentication on the database (eg. SSL_CLIENT_AUTHENTICATION is disabled), and
• the server certificate was issues by a trusted Certificate Authority (CA) that is stored in the client system’s certificate store.

If the server certificate is not stored on the client’s certificate store, or if you want to authenticate the user by the client certificate (mTLS), you will need to create a client wallet. In this blog post, I will show the configurations needed for this kind of user authentication.

Create Client Wallet: First, we will create the Oracle Wallet and certificate on client side and let the Oracle Wallet on the database server sign the client certificate later on. Alternatively, you may set one or multiple Subject Alternative Names (SAN) comma separated to your client certificate signing request. Please adapt the option argument values as needed.

# create wallet
orapki wallet create -wallet {path/to/store/client/wallet} -pwd {password} -auto_login_local

# create and export client certificate signing request
orapki wallet add -wallet {path/to/your/client/wallet} -pwd {password} -dn "CN={yourClientCommonName}" -keysize 2048 -validity 1825 -addext_san DNS:{yourSubjectAlternativeName}
orapki wallet export -wallet {path/to/your/client/wallet} -pwd {password} -dn "CN={yourClientCommonName}" -request {path/to/save/your}/client_signing_request.csr

Create Server Wallet: Create the Oracle Wallet on server side (database). Afterwards, you can import the Client certificate signing request client_signing_request.csr to let your Server CA issue a new certificate for the client.

# create wallet
orapki wallet create -wallet {path/to/save/server/wallet} -pwd {password} -auto_login_local

# create self-signed cert
orapki wallet add -wallet {path/to/your/server/wallet} -pwd {password} -dn "CN={yourCACommonName}" -keysize 2048 -self_signed -validity 3650

# display all wallet certificates (it should show your server CA certificate)
orapki wallet display -wallet {path/to/your/server/wallet} -complete

# create cert by client certificate signing request
orapki cert create -wallet {path/to/your/server/wallet} -request {path/to/your}/client_signing_request.csr -cert {path/to/save}/client_certificate.crt -validity 1825 -pwd {password}

# export server certificate
orapki wallet export -wallet {path/to/your/server/wallet} -pwd {password} -dn "CN={yourServerCommonName}" -cert {path/to/save/your}/server_certificate.crt

Import certificates into Wallet: Import certificate client_certificate.crt as user certificate and server_certificate.crt as trusted certificate to your Client Oracle Wallet.

# add trusted CA server certificate
orapki wallet add -wallet {path/to/your/client/wallet} -pwd {password} -trusted_cert -cert {path/to/your}/server_certificate.crt

# import CA signed client certificate
orapki wallet add -wallet {path/to/your/client/wallet} -pwd {password} -user_cert -cert {path/to/your}/client_certificate.crt

# display all wallet certificates (it should show your imported certificates)
orapki wallet display -wallet {path/to/your/client/wallet} -complete

Configure SQLnet.ora: Next we need to configure the SQLnet.ora files on the client and server side. Here we can define the location of our Oracle Wallet and define Parameter settings for TLS. On the client configuration, we define the following settings to ensure only strong ciphers are used.

WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = {path/to/your/client/wallet})
     )
   )

SQLNET.AUTHENTICATION_SERVICES = (TCPS)
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_SERVER_DN_MATCH = ON
SSL_VERSION = 1.2
SSL_CIPHER_SUITES = (TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384, TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256, TLS_ECDHE_ECDSA_WITH_AES_256_GCM_SHA384, TLS_ECDHE_ECDSA_WITH_AES_128_GCM_SHA256, TLS_DHE_RSA_WITH_AES_256_GCM_SHA384, TLS_DHE_RSA_WITH_AES_128_GCM_SHA256)

On the database server, similiar configurations can be set. In this case SSL_CLIENT_AUTHENTICATION will be set to TRUE to activate user authentication.

WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = {path/to/your/server/wallet})
     )
   )

SQLNET.AUTHENTICATION_SERVICES = (TCPS)
SSL_CLIENT_AUTHENTICATION = TRUE
SSL_VERSION = 1.2
SSL_CIPHER_SUITES = (TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384, TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256, TLS_ECDHE_ECDSA_WITH_AES_256_GCM_SHA384, TLS_ECDHE_ECDSA_WITH_AES_128_GCM_SHA256, TLS_DHE_RSA_WITH_AES_256_GCM_SHA384, TLS_DHE_RSA_WITH_AES_128_GCM_SHA256)

Configure listener.ora: On the database server we need to add the Oracle Wallet and the Protocol Information to make the database listen on default Port 2484 for TCPS. You may use any other Port if you like.

WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = {path/to/your/server/wallet})
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCPS)(HOST = {yourHostname})(PORT = 2484))
    )
  )

Configure TNSnames.ora: Finally, you can add the TCPS connection to your tnsnames.ora file on the client system. The parameter WALLET_LOCATION (MY_WALLET_DIRECTORY is deprecated since Oracle 23ai) allows you to specify the Oracle Wallet location where your certificates are stored. In the example below, I am using YOURPDB as the Network Alias.

YOURPDB=
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCPS)
      (HOST={yourHostname})
      (PORT=2484)
    )
    (CONNECT_DATA=
      (SERVICE_NAME={ServiceName})
    )
    (SECURITY=
      (SSL_SERVER_CERT_DN="CN={yourServerCommonName}")
      (SSL_SERVER_DN_MATCH=TRUE)
      (WALLET_LOCATION={path/to/your/client/wallet})
    )
  )

That’s it! If you are using SQL Developer, you can set the TNSnames directory via Tools > Preferences > Database > Advanced > TNSnames Directory. Afterwards, you can select {YOURPDB} as the Network Alias in the connection settings. For SQLcl, you can set the environment variable TNS_ADMIN to link to the directory where your tnsnames.ora file is saved.

Optional – Activate TLS as an Authentication Service: In Oracle database it is possible to use TLS as an Authentication Service for user authentication. While remote OS authentication is deprecated since Oracle 21c and has been removed in 23ai, the authentication based on TLS certificates is still available. You can use it for a database schema by using the IDENTIFIED EXTERNALLY clause specifying the Distinguished Name (DN) of the certificate when creating or altering an user, for example:

-- Create or alter user
CREATE USER MYUSER IDENTIFIED EXTERNALLY AS 'CN={yourClientCommonName}';
ALTER USER MYUSER IDENTIFIED EXTERNALLY AS 'CN={yourClientCommonName}';

-- Minimal grants
GRANT CONNECT, CREATE SESSION TO MYUSER;

To be able to connect to a schema in SQL Developer, you need to activate TCPS as an authentication service in the config file of SQL Developer. Add the following line (or include value TCPS as available authentication service, separated by a comma) to your config in …/sqldeveloper/bin/sqldeveloper.conf:

....
AddVMOption -Doracle.net.authentication_services=(TCPS)

For the Visual Studio Code Extension of SQL Developer, you can add this option in the “Advanced” tab inside the connection settings. There you can set a new property “oracle.net.authentication_services” as Name and “TCPS” as its Value.

When using SQLcl, you need to set this configuration by the environment variable JAVA_TOOL_OPTIONS.

SET JAVA_TOOL_OPTIONS=-Doracle.net.authentication_services=(TCPS)
SET TNS_ADMIN={path/to/your/tnsnames.ora}
sql {user}@{YOURPDB}
SQL> Connected.
....

If SQL Developer or SQLcl asks you for a schema password, you can leave it empty.

SSH tunnels

If you want to be able to use SSH tunnels to securely connect to the database, you need to have access to the host system the database is running on. As Public Key Authentication is needed, you need to create an SSH Public Key and save it in the authorized_keys file on the host system.

SQL Developer: When using SQL Developer, you first need to add the SSH panel to the GUI (menu View > SSH). You can add a new SSH host and specify hostname and username for the SSH session, as well as hostname and port for the database connection.

To use the SSH tunnel for a connection to the database, you need to set SSH as the Connection Type and select your previously created SSH tunnel name as Port Forward.

SQLcl: You need to start SQLcl without logging in. Afterwards you can execute the sshtunnel command and log in via EZ Connect Naming Method. In my example, the remote database is running on localhost Port 1521.

SET TNS_ADMIN={path/to/your/tnsnames.ora}
sql /nolog
SQL> sshtunnel oracle@example.tld:22 -i ~/.ssh/id_rsa -L 1521:localhost:1521
Using port: 22
SSH Tunnel connected
SQL> conn {user}@{YOURPDB}
SQL> Connected.
....

Note: Please be aware that you need to specify the host connection details in your SSH tunnel settings. Therefore, you cannot use the tnsnames.ora file to save any SSH tunnel credentials.