To use SSL for connecting to an Oracle Database via SQLPlus on Windows, you'll need to configure your Oracle client and SQLPlus to use SSL. This involves several steps, including configuring the Oracle Net Services to use SSL, importing the required SSL certificate into the Oracle client truststore, and then using SQL*Plus to connect to the database over SSL. Here's a general outline of the steps you need to follow.
Note: Applications connecting to the database will also need to be modified or reconfigured to enable SSL connectivity. Enabling SSL in different drivers is beyond the scope of this document.
1. Obtain SSL Certificate
First, you need to obtain the SSL certificate for the Oracle Database server. This could be a certificate signed by a Certificate Authority (CA) or a self-signed certificate. In the case of Tessell, the certificate can be downloaded from the Tessell Console.
In Tessell → My Services locate the service you are trying to connect to and open the Overview Section.Scroll in the section until you see the function to download the certificate. Download the certificate to a directory on to your local Windows client.
2. Install Oracle Client (if not already installed)
Ensure that you have the Oracle Client installed on your Windows machine. This is necessary to connect to the Oracle Database and includes the utilities for managing SSL configurations.
At a minimum you will need to have the sql*plus client utility and orapki (needed for certificate wallet management). The easiest way to ensure you have all of the tools is to use the Oracle Universal Installer (OUI). You can also use Oracle Wallet Manager which is a graphical utility that performs the same actions as orakpi.
Note: You can find information on the OUI in the Oracle Help Center.
A quick way to ensure you have all of the tools you need when installing with OUI is to select the Administrator profile.
Once installation is complete you will have access to all of the required tools. You may also want to make sure you have noted where the $ORACLE_HOME/network/admin directory is located for the installation.
3. Configure Oracle Net for SSL
You need to configure the Oracle Net Services to use SSL. This involves editing the sqlnet.ora file, usually located in $ORACLE_HOME/network/admin directory. We want to instruct the system where it can find the SSL certificate wallet when establishing a connection
sqlnet.ora
Add or modify the following lines to include SSL configuration. These are not the only settings for SSL. You can also control the ciphers used, etc. Please refer to the Oracle documentation if you require a more advanced version of the configuration.
WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = path_to_your_wallet)))
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_VERSION = 1.2
Note: Ensure the database listener is configured to accept SSL connections, which might require assistance from your database administrator. For purpose of this document we are assuming that the database is already configured for SSL.
If you do not have an existing Wallet, you can complete the steps in section 4 below to enable a new wallet.
4. Create an Oracle Wallet
Oracle uses a Wallet to store trusted certificates. Use the Oracle Wallet Manager or orapki command line utility to create a wallet and import the SSL certificate:
orapki wallet create -wallet path_to_your_wallet -auto_login
orapki wallet add -wallet path_to_your_wallet -trusted_cert -cert your_certificate.crt
Note: your_certificate.crt is the path to the certificate file that you downloaded from Tessell in step 1 above. If you have an existing wallet you do not need to execute the wallet create command.
Important: The -auto_login function enables the driver to access the wallet without requiring a passphrase. If this is not enabled the driver will produce an error message indicating it cannot access the wallet file. You also need to ensure that the operating system permissions allows users or service accounts to access the wallet file.
If you are using Wallet Manager the process is simple:
Open Wallet Manager. The menu bar on the left contains functions to create a new wallet, or open an existing wallet.
When you create a new wallet it will ask for a passcode. Create a passphrase that meets the specified requirements
After you submit the passphrase, the Wallet Manager will ask if you want to create a certificate request. Do not create a request. Instead, you will want to import a certificate
Browse to the directory that contains the certificate file you downloaded from Tessell
You should now have a certificate in the Wallet
Save the Wallet file. Make sure you store it in the directory you configured in sqlnet.ora above.
Enable Auto Login for the Wallet. The setting will be automatically saved in the Wallet once you select it
let add -wallet path_to_your_wallet -trusted_cert -cert your_certificate.crt
5. Connect using SQL*Plus
Finally, use SQL*Plus with the appropriate connect string to connect to your database. Optionally, your TNSNAMES.ORA file should have an entry for the database that specifies the use of the TCPS protocol (TCP with SSL). This can also be supplied directly to sql*plus on the command line.
Example of a TNS entry:
MYDB_SSL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCPS)(HOST = your_db_host)(PORT = your_ssl_port))
(CONNECT_DATA =
(SERVICE_NAME = your_service_name)
(SSL_SERVER_CERT_DN = "CN=mydb.service.com")
)
)
Make sure to replace path_to_your_wallet, your_certificate.crt, your_db_host, your_ssl_port, your_service_name and mydb.service.com with your actual information.
The Tessell Service Overview page provides a preconfigured connection string that you can use to get started on your TNS configuration
Additional Notes
The steps above may vary depending on your Oracle Database version and the specific setup of your Oracle Network environment.
Consult the Oracle documentation specific to your Oracle version for detailed instructions on setting up SSL, as there might be version-specific steps or requirements.
This outline provides a general approach to setting up SSL for SQL*Plus connections on Windows. It is essential to adapt these instructions to your specific environment and Oracle Database version.