firstworks software and systems: Oracle TLS/SSL Encryption with CA-Signed Certificates

Introduction

This tutorial walks through enabling TLS/SSL encryption between an application and Oracle database, including basic encryption, certificate validation, distinguished name validation, and mutual authentication.

Self-signed certificates, generated by Oracle-provided tools, are used throughout. Assumptions

The following assumptions are made:

An Oracle database is running on a linux server named db.firstworks.com.
The database was installed by the oracle user.
The database contains an instance named ora1 which is accessible using scott/tiger credentials.
An application which accesses the database is installed on a linux server named app.firstworks.com.
Oracle Instant Client is the only Oracle software installed on app.firstworks.com.
It is possible to scp from db.firstworks.com to root@app.firstworks.com.
It is possible to scp from app.firstworks.com to oracle@db.firstworks.com.

Basic Encryption

First, we will configure basic TLS/SSL encryption of the communications between the database and application server.

Database Server Configuration

orapki wallet create -wallet /u01/app/oracle/wallet -auto_login -pwd Passw0rd

The orapki options above do the following things:

-wallet specifies the path for the wallet.

A wallet is just a directory with some files under it.
If you need to delete a wallet, just delete the directory.

-auto_login indicates that the wallet will be used for logins.

There is also an alternative -auto_login_local option that locks the wallet down to the local machine. If the wallet is copied or moved to a different machine then attempts to use it will fail.
These options can also be omitted, but it's not clear what a wallet created without one of them can be used for.

-pwd specifies a password for the wallet.

This password must be supplied to modify the wallet, but is not required to use the wallet. It may be used by any user with permissions to read its files.
If the -pwd option is omitted then the user will be prompted for the password on the command line.
Passwords must contain 8 characters, and must contain at least 1 number.
Obviously a more secure password than Passw0rd should be used.

Next, create a self-signed certificate:

orapki wallet add -wallet /u01/app/oracle/wallet -dn CN=db.firstworks.com \
-keysize 2048 -self_signed -validity 3650 -pwd Passw0rd

This command actually does several things:

Creates a 2048-bit private key, which is stored in the wallet (and cannot be exported using Oracle-provided tools).
Uses the private key to create a certificate request certificate request for distinguished name CN=db.firstworks.com, which is also stored in the wallet.
Uses the certificate request to create a self-signed root certificate which is valid for 10 years (3650 days).
Stores the certificate as a Trusted Certificate.
Also stores the certificate as a User Certificate.

You can display contents of the wallet as follows:

orapki wallet display -wallet /u01/app/oracle/wallet

You should see CN=db.firstworks.com as both a User Certificate and Trusted Certificate.

Next, configure the database instance to use the wallet.

Edit $ORACLE_HOME/network/admin/sqlnet.ora and append these lines:

WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /u01/app/oracle/wallet)
    )
  )

SQLNET.AUTHENTICATION_SERVICES = ALL
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA, SSL_RSA_WITH_3DES_EDE_CBC_SHA)

These lines:

Provide the path to the wallet.
Enable "all" authentication services (including TLS/SSL), as opposed to only allowing database user/password authentication.
Disables authentication of the peer's certificate. For now we just want to encrypt communications.
Enables a set of cipher suites. The default is "none" so a set of ciphers has to be explicitly provided.

Edit $ORACLE_HOME/network/admin/listener.ora and add these lines near the top:

WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /u01/app/oracle/wallet)
    )
  )

SSL_CLIENT_AUTHENTICATION = FALSE

These lines do the same things that they do in sqlnet.ora. It's not clear why they have to be present in both files.

Also, edit $ORACLE_HOME/network/admin/listener.ora and update the LISTENER definition, adding an entry for the TCPS protocol on port 2484:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = db.firstworks.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCPS)(HOST = db.firstworks.com)(PORT = 2484))
    )
  )

Now, bounce the listener:

lsnrctl stop
lsnrctl start

That's all on the database server.

Application Server Configuration

Oracle clients don't create ephemeral keys, so on the application server, a wallet must also be created and populated with a User Certificate for the application.

Fortunately, this is relatively straightforward.

Create a wallet in the local directory.
Create a self-signed certificate for the distinguished name CN=app.firstworks.com.
Tar up the local wallet.
Copy it to the application server.
Clean up.

orapki wallet create -wallet ./wallet -auto_login -pwd Passw0rd
orapki wallet add -wallet ./wallet -dn CN=app.firstworks.com \
-keysize 2048 -self_signed -validity 3650 -pwd Passw0rd
tar cf wallet.tar wallet
scp wallet.tar root@app.firstworks.com:
rm -r wallet wallet.tar

Create a directory to serve as the ORACLE_HOME, with network/admin subdirectories.
Extract the wallet into the ORACLE_HOME.
Fix permissions.
Clean up.

mkdir -p /var/oraclehome
mkdir -p /var/oraclehome/network/admin
tar xf ~/wallet.tar
chmod 755 wallet
chmod 644 wallet/*
rm ~/wallet.tar

Create /var/oraclehome/network/admin/sqlnet.ora with the following contents:

WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = /u01/app/oracle/wallet)
     )
   )

SQLNET.AUTHENTICATION_SERVICES = ALL
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA, SSL_RSA_WITH_3DES_EDE_CBC_SHA)

Create /var/oraclehome/network/admin/tnsnames.ora with the following contents:

ora1=
(DESCRIPTION =
        (ADDRESS =
                (PROTOCOL = TCPS)
                (HOST = db.firstworks.com)
                (PORT = 2484)
        )
        (CONNECT_DATA =
                (SERVER = DEDICATED)
                (SERVICE_NAME = ora1)
        )
)

Note that PROTOCOL = TPCS and PORT = 2484 instead of TCP/1521.

That's all on the application server.

Testing With SQLPlus

Connect to the database from the application server using sqlplus as follows:

ORACLE_HOME=/var/oraclehome sqlplus scott/tiger@ora1

You can verify that the connection is TLS/SSL-secured by running:

select sys_context('USERENV','network_protocol') from dual;

SYS_CONTEXT('USERENV','NETWORK_PROTOCOL')
--------------------------------------------------------------------------------
tcps

If the connection fails, then it may fail for one of these common, but also very cryptic errors:

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

The listener isn't ready.
It can take a while. Try again in a minute or two.

ORA-28864: SSL connection closed gracefully

The wallet on the database server was created without the -auto_login option.
Remove the wallet, re-create the wallet with the -auto_login option, and bounce the listener.

ORA-29106: Cannot import PKSC #12 wallet

The application server's wallet was created with the -auto_login_local option.
Remove the wallet, re-create the wallet with the -auto_login option instead of -auto_login_local, and move the new wallet to the application server.

ORA-29024: Certificate validation failure

You accidentally omitted or mispelled SSL_CLIENT_AUTHENTICATION = FALSE in sqlnet.ora, on the application server.
Update sqlnet.ora on the application server with SSL_CLIENT_AUTHENTICATION = FALSE

ORA-28860: Fatal SSL error

You accidentally omitted or mispelled SSL_CLIENT_AUTHENTICATION = FALSE in sqlnet.ora and/or listener.ora, on the database server.
Update sqlnet.ora and/or listener.ora on the database server with SSL_CLIENT_AUTHENTICATION = FALSE and bounce the listener.

Updating Your Application

To get your application to use the new configuration:

You must set the environment variable ORACLE_HOME to /var/oraclehome, either system-wide, in the app's startup script, or in the app's config file.

You must update your application to use the SID "ora1" defined in the tnsnames.ora files, rather than the full database connection string. This is typically done in the app's config file, or sometimes in app code itself. For some apps, you have to set the environment variable ORACLE_SID, either system-wide, in the app's startup script, or in the app's config file.

Adding Database Certificate Validation

So far, we have encrypted communications between the application and the database, but we can make the application more secure by validating the database server's certificate.

The basic steps are:

Extract the database server's certificate.
Add it to the application server's wallet.
Set SSL_CLIENT_AUTHENTICATION = TRUE on the application server.

Conceptually, this isn't very complex, but lack of orapki on the application server makes the process take a lot of unintuitive steps.

orapki wallet export -wallet /u01/app/oracle/wallet -dn CN=db.firstworks.com -cert db.crt -pwd Passw0rd

Tar up the wallet.
Copy it to the database server.
Clean up.

cd /var/oraclehome
tar cf wallet.tar wallet
scp wallet.tar oracle@db.firstworks.com:
rm wallet.tar

Extract the wallet locally.
Add the database server's certificate as a Trusted Certificate to the local wallet.
Tar up the local wallet.
Copy it to the application server.
Clean up.

tar xf wallet.tar
rm wallet.tar
orapki wallet add -wallet ./wallet -trusted_cert -cert db.crt -pwd Passw0rd
tar cf wallet.tar wallet
scp wallet.tar root@app.firstworks.com:
rm -r wallet wallet.tar
rm db.crt

Remove the old wallet.
Extract the updated wallet.
Fix permissions.
Clean up.

cd /var/oraclehome
rm -r wallet
tar xf ~/wallet.tar
chmod 755 wallet
chmod 644 wallet/*
rm ~/wallet.tar

Reconfigure the application server to validate the database's certificate against the set of Trusted Certificates in the wallet by enabling SSL_CLIENT_AUTHENTICATION. Edit /var/oraclehome/network/admin/sqlnet.ora and set:

SSL_CLIENT_AUTHENTICATION = TRUE

The parameter name SSL_CLIENT_AUTHENTICATION is a bit of a misnomer, since we're using it to authenticate the database server's certificate. It should probably be called SSL_PEER_AUTHENTICATION.

Test the configuration using sqlplus:

ORACLE_HOME=/var/oraclehome sqlplus scott/tiger@ora1

If the connection fails, then it will likely fail with:

ORA-29024: Certificate validation failure

SSL_CLIENT_AUTHENTICATION = TRUE may be misspelled.
Some wallet-update or wallet-transfer step failed or was skipped.
In either case, just try again.

Adding Database Certificate Distinguished Name Validation

So far, we are encrypting communications between the application and the database and validating the database server's certificate, but we can also make the application more secure by validating the distinguished name in the database server's certificate.

Edit /var/oraclehome/network/admin/sqlnet.ora, and add the line:

SSL_SERVER_DN_MATCH = TRUE

Edit /var/oraclehome/network/admin/tnsnames.ora, and add a SECURITY section to the ora1 entry as follows:

ora1=
(DESCRIPTION =
        (ADDRESS =
                (PROTOCOL = TCPS)
                (HOST = db.firstworks.com)
                (PORT = 2484)
        )
        (CONNECT_DATA =
                (SERVER = DEDICATED)
                (SERVICE_NAME = ora1)
        )
 (SECURITY =
  (SSL_SERVER_CERT_DN = "CN=db.firstworks.com")
 )
)

Then you can test the configuration using sqlplus:

ORACLE_HOME=/var/oraclehome sqlplus scott/tiger@ora1

If the connection fails, then it will likely fail with:

ORA-29003: SSL transport detected mismatched server certificate.

The distinguished name in the database server's cert didn't match the SSL_SERVER_CERT_DN entry in the tnsnames.ora file.
Verify the spelling of the distinguished name in the tnsnames.ora and certificate, and update the entry in the tnsnames.ora accordingly. Alternatively, you could delete both wallets, start completely over and make sure to spell the name correctly in the certificate.

Adding Mutual Authentication

Just as we configured the application server to validate the database's certificate, it is also possible to configure the database server to validate the application server's certificate.

The basic steps are:

Extract the application server's certificate.
Add it to the database server's wallet.
Set SSL_CLIENT_AUTHENTICATION = TRUE on the database server.
Bounce the listener.

Conceptually, this isn't very complex, but lack of orapki on the application server makes the process take several unintuitive steps.

Tar up the wallet.
Copy it to the database server.
Clean up.

cd /var/oraclehome
tar cf wallet.tar wallet
scp wallet.tar oracle@db.firstworks.com:
rm wallet.tar

Extract the wallet locally.
Export the application server's certificate from the local wallet.
Add the application server's certificate as a Trusted Certificate to the database server's wallet.
Clean up.

tar xf wallet.tar
orapki wallet export -wallet ./wallet -dn CN=app.firstworks.com -cert app.crt -pwd Passw0rd
orapki wallet add -wallet /u01/app/oracle/wallet -trusted_cert -cert app.crt -pwd Passw0rd
rm -r wallet wallet.tar app.crt

Edit $ORACLE_HOME/network/admin/sqlnet.ora, and update the SSL_CLIENT_AUTHENTICATION parameter to:

SSL_CLIENT_AUTHENTICATION = TRUE

Edit $ORACLE_HOME/network/admin/listener.ora, and update the SSL_CLIENT_AUTHENTICATION parameter to:

SSL_CLIENT_AUTHENTICATION = TRUE

This line does the same thing that it does in sqlnet.ora. It's not clear why it has to be present in both files.

Bounce the listener:

lsnrctl stop
lsnrctl start

Then you can test the configuration from the application server, using sqlplus:

ORACLE_HOME=/var/oraclehome sqlplus scott/tiger@ora1

If the connection fails, then it will likely fail with:

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

The listener isn't ready.
It can take a while. Try again in a minute or two.

ORA-28860: Fatal SSL error

SSL_CLIENT_AUTHENTICATION = TRUE may be omitted or misspelled in sqlnet.ora and/or listener.ora.
Some wallet-update or wallet-transfer step failed or was skipped.
In either case, just try again and bounce the listener.

There is no obvious way to validate the distinguished name in the application server's certificate. I suspect that this just isn't supported.